Rename Files using sql/t-sql

Is it posssible to rename files using SQL/T-SQL? One method could be the xp_cmdshell command but I want to rename files on the netowrk and xp_cmdshell commands do not support UNC paths.
Any ideas?
ThanksOriginally posted by vmlal
Is it posssible to rename files using SQL/T-SQL? One method could be the xp_cmdshell command but I want to rename files on the netowrk and xp_cmdshell commands do not support UNC paths.

Any ideas?

Thanks

This works fine:

xp_cmdshell 'copy \\server1\backup\backup.sql \\server1\backup\backup2.sql'

xp_cmdshell 'copy \\server1\backup\backup.sql \\server2\backup\backup2.sql'|||you can create a bat file on your local machine in which you can enter all your commands. You can then execute this with the xp_cmdshell.

rename file using cmdexec in SQL Server Agent job.

Hi:
I am using the cmdexec step in the SQL Server agent to rename a file. Below
is the command I use to rename the file in the step.
ren c:\test1.bak test2.bak
However when I run the job the job fails with the following error.
The process could not be created for step 1 of job
0xDD5759D5B7103041B7F0BD736B442D1D (reason: The system cannot find the file
specified). The step failed.
What is wrong with the command?. the command works perfectly from dos prompt
but refuses to work from the job. I also tried
ren "c:\test1.bak test2.bak" and this also fails. I dont want to use
xp_cmdshell and hence want to use the cmdexec command. Can anyone please let
me know what I am doing wrong and what is\are the correct commands when
executed from a SQL Server Agent job step?.
Thanks
MWhen you run it from the command prompt is it on your local machine or the
server? xp_cmdshell is always executed on the server not the client.
Andrew J. Kelly SQL MVP
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:eJriWN7SGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Hi:
> I am using the cmdexec step in the SQL Server agent to rename a file.
> Below is the command I use to rename the file in the step.
> ren c:\test1.bak test2.bak
> However when I run the job the job fails with the following error.
> The process could not be created for step 1 of job
> 0xDD5759D5B7103041B7F0BD736B442D1D (reason: The system cannot find the
> file specified). The step failed.
> What is wrong with the command?. the command works perfectly from dos
> prompt but refuses to work from the job. I also tried
> ren "c:\test1.bak test2.bak" and this also fails. I dont want to use
> xp_cmdshell and hence want to use the cmdexec command. Can anyone please
> let me know what I am doing wrong and what is\are the correct commands
> when executed from a SQL Server Agent job step?.
> Thanks
> M
>|||It is on my local machine which is running SQL Server 2000.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uTMlOI8SGHA.4264@.TK2MSFTNGP11.phx.gbl...
> When you run it from the command prompt is it on your local machine or the
> server? xp_cmdshell is always executed on the server not the client.
> --
> Andrew J. Kelly SQL MVP
>
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> news:eJriWN7SGHA.5656@.TK2MSFTNGP11.phx.gbl...
>|||Andrew:
I figured it out. Thanks for making me think. Here is the correct command.
ren "c:\test20.bak" test1.bak
Thats why I adore SQL MVPs. They make SQL guys think logically.
Best Regards
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:eUC6IY8SGHA.4952@.TK2MSFTNGP09.phx.gbl...
> It is on my local machine which is running SQL Server 2000.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uTMlOI8SGHA.4264@.TK2MSFTNGP11.phx.gbl...
>|||The double quotes usually are not required unless you have spaces in the
path or file name. In either case I am glad you fixed it.
Andrew J. Kelly SQL MVP
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:eJTtLc8SGHA.736@.TK2MSFTNGP12.phx.gbl...
> Andrew:
> I figured it out. Thanks for making me think. Here is the correct command.
> ren "c:\test20.bak" test1.bak
> Thats why I adore SQL MVPs. They make SQL guys think logically.
> Best Regards
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> news:eUC6IY8SGHA.4952@.TK2MSFTNGP09.phx.gbl...
>

Removing SQL Parameters from Command Object after use.

Hi all,

is it good practice to remove SQL Parameters from the Command Object's parameters collection after the CommandObject has executed its SQL containing the SQL Parameters?

The reason I ask is because I have encountered some issues (errors) stating that the SQL Parameter cant be used because it is still contained in another Parameter collection - ( I created the Parameters, and then use these same parameter (through looping) to execute new commands).

Thanks

Tryst, if you are in a loop reusing the same command object, clear your parameters either before you begin adding them again, or after you execute the command using:

cmd.Parameters.Clear

That should be all you need to reuse your parameter objects again.

|||OK - thanks.