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.

Correct Script Download 

/************* 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

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