Help with running xp_cmdshell in cursor - incorrect syntax error

[bmacbmac]

Hello,

I have a group of files in a folder. I want to rename each file in the folder to something different.

This is a process that will run each day and the file names will always be different. I want my user to run a SQL script to accomplish this without any necessary input from the user.

I am assuming a cursor is the best way to go with the master..xp_cmdshell? If you have a better option, please let me know what you think.

In the meantime, I have written this:

set nocount on
go

declare RenameFiles scroll cursor
     for select distinct filename, newfilename from loadimages            
     where filename is not null

declare @filename varchar(200)
declare @newfilename varchar(200)

open RenameFiles
   fetch next from RenameFiles into @filename, @newfilename

       while (@@fetch_status =0)
	   begin
		exec master..xp_cmdshell 'rename "\\ServerName\ShareName\FolderName\unzipped\' + @filename + '" ' + @newfilename
	    		
    fetch next from RenameFiles into @filename, @newfilename
			
	
	    end

deallocate RenameFiles

However I am getting an error:
Line 13: Incorrect syntax near '+'

Any idea what I am missing? If I change the 'exec master..xp_cmdsell' to 'print' it prints out the rename scripts just fine.

Is there an issue running the xp_cmdshell in a cursor?

 

[gmmastros]

The problem is that you cannot concatenate a string on the same line as the exec. Instead, declare another variable, build your command, and then exec the variable. Weird, I know, but it should work.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Yep that makes sense. So I created an @fullstring variable:

set nocount on
go

declare RenameFiles scroll cursor
     for select distinct filename, newfilename from loadimages            
     where filename is not null

declare @filename varchar(200)
declare @newfilename varchar(200)
declare @Fullstring varchar(500)

open RenameFiles
   fetch next from RenameFiles into @filename, @newfilename
select @fullstring = 'rename "\\ServerName\ShareName\FolderName\unzipped\' + @filename + '" ' + @newfilename
       while (@@fetch_status =0)
       begin
        exec master..xp_cmdshell @fullstring
                
    fetch next from RenameFiles into @filename, @newfilename
            
    
        end

deallocate RenameFiles

And it works great for the first file, but fails for the rest. As I look into it further, it looks like it is just repeating the same rename script over and over instead of moving on to the next file in line.

Do I have it in the wrong place of the script? I imagine I need to adjust the placement of my 'Fetch' command, but everything I try does the same thing.

 

[JoeAtWork]

If it were me, I would just write this in a script or as a program in VB6, or .NET, etc. Among other reasons, it is a lot easier to debug that way.

 

[bmacbmac]

I know just a little enough of cursors to be dangerous..... I know even less (zero) vb and even less (-1000) .net.

I think a cursor is all that falls in my skill set. I think I'm 95% there if I can get my script rearranged a little but.