How to Replace a file using xp_cmdshell in sqlserver2008?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi,

Please find my code below,
DECLARE @DestLink ='D:\NewFolder\'
DECLARE @SrcLink ='C:\Test\Test1.docx'
Declare @strDateTimeToTicks BIGINT=0
SET @strDateTimeToTicks=(select dbo.DateTimeToTicks(getdate()))

DECLARE @extension VARCHAR(50)

SET @extension =(SELECT RIGHT(@SrcLink, CHARINDEX('.', REVERSE(@SrcLink))) AS [FileExtension])

SET @newPath='prefix'+ (Select CAST(@strDateTimeToTicks As varchar))+(Select CAST(@randomNumber As varchar))+@extension

Declare @newFlieName nvarchar(max)=''
SET @newFlieName=@DestLink--+@newPath

SET @SqlCopy = ' COPY /Y "' + @SrcLink + '" /B "' + @newFlieName

DECLARE @result int=0
EXEC master..xp_cmdshell @SqlCopy

My Question is it could not replace to destination filename

Posted 12-Oct-14 23:33pm

24983

Updated 14-Oct-14 6:33am

v4

Add a Solution

Comments

jaket-cp 13-Oct-14 12:31pm

Not sure what you are asking.
Your code looks good, apart from missing double quote at end of @SqlCopy and to declare @SqlCopy.
Do you want to delete C:\New\Test1.docx after copying it to D:\New\Prefix123456.docx ?

24983 14-Oct-14 5:00am

As per my my code, Now copied Test1.docx to another folder. It could not replace with Prefix123456.docx. I need to Replace ...Please help me.

jaket-cp 14-Oct-14 5:19am

Not sure what you mean by replace?
Does D:\New\Prefix123456.docx exist already?

jaket-cp 14-Oct-14 8:15am

Please update your question, there should be an improve question link or something.
Also when replying click on the Reply so people can be notified of the reply

jaket-cp 15-Oct-14 8:31am

Have you managed to solve your issue?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2014-10-13T06:35:00

Solution 1

Remove the + character. Your statement is trying to copy those two files to a new one, which will be named Test1.docx. You can check the exact syntax by typing copy /? in a command window.

Posted 13-Oct-14 6:35am

Richard MacCutchan

Comments

Richard MacCutchan 14-Oct-14 6:02am

I already told you what to do to correct it; please make an effort.

jaket-cp · Answer 2 · 2014-10-14T02:24:00

Having a quick look at your comment, @newPath is being commented out.

SQL

SET @newFlieName=@DestLink--+@newPath

The output of the command would be something like:

COPY /Y  "C:\New\Test1.docx"  /B "D:\New\"

This will copy the file Test1.docx in location C:\New\ to destination D:\New\ with the same file name Test1.docx.

Please read: http://ss64.com/nt/copy.html[^], I find this a good source for command line references.

Also you should be aware of the security issues when using xp_cmdshell.
http://blogs.msdn.com/b/sqlsecurity/archive/2008/01/10/xp-cmdshell.aspx[^]