Hi All,
please help me to resolve this issue
I am getting following error message while creating procedure
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
--Version
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
if i remove the XP_cmdshell line then proc is creating .. comment also not working
CREATE PROCEDURE [dbo].[ArchiveFileCleanUp] @Dir varchar(512),@Ext varchar(4),@DaysOld int
AS
BEGIN TRY
set @DaysOld = REPLACE(@daysold,'-','')
declare @cmd varchar(8000),@extlen int,@sqlstr nvarchar(4000),@filename varchar(100)
create table #tmp (dir varchar(512))
create table #delfiles (filename varchar(512))
set @cmd = 'dir ' + @Dir + '*' + @ext
set @extlen = LEN(@ext)
--get contents of directory
insert into #tmp
exec xp_cmdshell @cmd
--get files to delete
set @sqlstr = ' insert into #delfiles
select substring(dir,40, len(dir)-25)
from #tmp
where RIGHT(rtrim(dir),' + cast(@extlen as varchar) + ') = ''' + @Ext +'''
and LEFT(dir,20) < dateadd(day,-' +cast(@DaysOld as varchar)+ ','''+ convert(varchar(10),GETDATE(),121)+''')'
-- print @sqlstr
exec sp_executesql @sqlstr
declare _curs cursor for select * from #Delfiles
open _curs
fetch next from _Curs into @Filename
while @@FETCH_STATUS=0
begin
--delete command
set @cmd = 'del ' + @Dir + @filename
--print @cmd
--execute delete
exec xp_cmdshell @cmd
fetch next from _curs into @filename
end
close _curs
deallocate _curs
--cleanup
drop table #tmp
drop table #delfiles
END TRY
BEGIN CATCH
--Error Handling
DECLARE @ErrorNumber int, @ErrorMessage varchar(1000), @ErrorProc varchar(100),@ErrorLine int,@err varchar(8000)
DECLARE @LocalServerName varchar(100)
Set @LocalServerName =@@SERVERNAME
set @ErrorNumber = ERROR_NUMBER()
set @ErrorMessage = replace(ERROR_MESSAGE(),'''','')
set @ErrorProc = coalesce(ERROR_PROCEDURE(),'N/A')
set @ErrorLine = ERROR_LINE()
set @err = 'Delete of archived files Failed:(' + @filename + ' Error #: ' + CAST(@errorNumber as varchar(10)) + ' Message: ' + @ErrorMessage + ' Proc: ' + @ErrorProc + ' Line #: ' + cast(@ErrorLine as varchar(5))
return @errornumber
END CATCH