Stored Procedure - ID to renamed file

[talon121]

I'd like to retrieve the rows from the db, and change the ID to a renamed file based on fields in the table, then execute a rename on the file...

right now files are stored in the db as ID.ext ... any way to use a stored procedure and change to field1.ext ??

I figure that a stored procedure would be the way to go but unless theres an easier way?

 

[MarkSweetland]

You lost me on this one. What file are you trying to rename, a column in the table, or the contents in a column?


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[talon121]

Oh Im sorry, Im fairly new with SP's Done them in the past, but trying to back into it..

Trying to take a file from a table ie.

ID
field1
field2

ex:
1,titleofmybook1,summaryofbook1,booklocationfilePath1
2,titleofmybook2,summaryofbook2,booklocationfilePath2
3,titleofmybook3,summaryofbook3,booklocationfilePath3

And in a file system we have files in a location (specified in booklocationfilepath) and I need to rename the physical file in the path, with a new filename ie. field1-field2.ext ... And each file in physical filepath already has the ID.ext

 

[jrbarnett]

The only way I have found to do this, short of writing a CLR procedure, is to build up a command line in a variable, then have xp_cmdshell execute it.

It will be something like:

declare @cmdline varchar (255)

set @cmdline = 'REN ' + booklocationfilePath1 + ' ' + field1+field2
-- print @cmdline -- check the command is correct
-- exec xp_cmdshell (@cmdline) -- uncomment to run

You need to put this into a loop for each entry in your table you want to rename. Presumably you also want to add an update routine to change the book location file path to the new one.

Problems?
1) you may not have xp_cmdshell enabled (quite common for security reasons);
2) your SQL Server service account will need permission to write to the folder with the files located
3) there is no check afterwards that the rename was successful.

If you can think of a better way of doing this though, then I'm up to hearing about it.

John