Copying files with xp_cmdshell- is there a better way than this cursor

[bmacbmac]

Hi. As part of an SQL script I need to copy files from one folder to another while renaming each file.

I populate a table with the copy script and new name for each file:

create table #temp (copypath varchar(5000), newname varchar(25))
insert into #temp values ('copy \\Server1\BENTMP000300\09446\D0944643.* \\Server2\benbatch1\', 'D0000001.*')
insert into #temp values ('copy \\Server1\BENTMP000300\09446\D0944644.* \\Server2\benbatch1\', 'D0000002.*')
insert into #temp values ('copy \\Server1\BENTMP000300\09446\D0944645.* \\Server2\benbatch1\', 'D0000003.*')
insert into #temp values ('copy \\Server1\BENTMP000300\09446\D0944646.* \\Server2\benbatch1\', 'D0000004.*')
insert into #temp values ('copy \\Server1\BENTMP000300\09446\D0944647.* \\Server2\benbatch1\', 'D0000005.*')

Then I have a cursor run the xp_cmdshell for each of those rows:

DECLARE COPY CURSOR FOR
SELECT copypath, newname
from #temp

DECLARE @CopyPath varchar(1000)
DECLARE @Newname varchar(15)
DECLARE @RESULT int
DECLARE @CFILE varchar(255)

OPEN COPY
FETCH NEXT FROM COPY INTO @Copypath, @NewName
   WHILE (@@FETCH_STATUS = 0)
      BEGIN
        
        SET @CFILE =  @Copypath + @Newname
	select @cfile

               EXEC Master..xp_cmdshell @CFILE

      FETCH NEXT FROM COPY INTO @copypath, @NewName
      END

DEALLOCATE COPY

Sometimes the SQL script will hang and will remain kind of frozen even if I let it run overnight... it should be a 5-10 minute copy.

It got me thinking that there is probably a better way to go than a cursor. Wanted to see if anyone else had an idea for me?

This is in SQL 2000 so I'm sure it's kind of limited.

Thanks!

Brian

 

[mrdenny]

That's about the best way. Try adding the switch /Y to allow overwriting of files. Odds are there's a duplicate file name.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site