Calling XP_CMDSHELL Within Cursor Is Not Completing task (Copying Files Using SQL Server)


                       I have configured copy job for latest backups using xp_cmdshell in cursor. Initially job is working fine but after a week I found job is successful but copy is happening for only 16 databases where as rest of database backups are not copying. If I try to generate dynamic sql output is correct but copying is only limited for 16 databases. After looking couple of sites I found this is one of basic problem but reason is not available. So I try to create work around here by replacing Cursor with While loop.


/************* Original SQL code with issue************************************/
/* Declare variables*/
Declare @DBNAME varchar (200)
Declare @Cmd varchar (6000)
Declare @SourceFile varchar (6000)
Declare @DestFile varchar (6000)
/*Declare Cursore */
Declare  DB_Backup_Cursor Cursor
FOR Select NAME from sys.databases where name not in ('tempdb') and state_desc ='ONLINE'
/*Open cursor*/
Open DB_Backup_Cursor
FETCH NEXT FROM DB_Backup_Cursor INTO @DBNAME
/*Starting cursor Loop*/
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @SourceFile = 'C:\path\' + @DBNAME + '\*.*' 
  SET @DestFile = '\\sharelocation\' + @DBNAME + '\'
  SET @Cmd = 'COPY ' + @SourceFile + ' ' + @DestFile;
      SELECT @cmd /*Publish command to debug*/
  EXEC master.dbo.xp_cmdshell @Cmd; /*Actual execution*/
  FETCH NEXT
  FROM DB_Backup_Cursor INTO @DBNAME
END
/*Closing and Deallocating Cursor*/
CLOSE DB_Backup_Cursor
DEALLOCATE DB_Backup_Cursor

/*************************************************/

Now below is modified code or which fix this issue. Here I am using temp table and while loop to avoid cursor.

/************Modified Fixed Code*************************************/
/* Declare variables*/
Declare @DBNAME varchar (200)
Declare @Cmd varchar (6000)
Declare @SourceFile varchar (6000)
Declare @DestFile varchar (6000)
Declare @i int
/*Create temp table and load it with data */
Create table #temp (id int identity (1,1), name varchar (8000))
insert into #temp
SELECT name from sys.databases where name not in ('tempdb') and state_desc ='ONLINE' order by name
/*Finde no of interation for while loop*/
select @i =count(*) from #temp
/*Starting While Loop*/
while @i >0
BEGIN
Select @DBNAME = name from #temp where id = @i
  SET @SourceFile = 'C:\Path\' + @DBNAME + '\*.*' 
  SET @DestFile = '\\Share folder\' + @DBNAME + '\'
  SET @Cmd = 'COPY ' + @SourceFile + ' ' + @DestFile;
select @cmd
EXEC master.dbo.xp_cmdshell @Cmd;
/*Decrementing Variable*/
set @i =@i -1
END
Drop table #temp
GO
/*************************************************/




No comments:
Write comments

Please do not enter spam links

Services

More Services