如何在sqlserver2008中使用xp_cmdshell替换文件？ [英] How to Replace a file using xp_cmdshell in sqlserver2008?

问题描述

请在下面找到我的代码，

DECLARE @DestLink ='D：\NewFolder \'

DECLARE @SrcLink ='C：\ Test \ Test1.docx'

声明@strDateTimeToTicks BIGINT = 0

SET @ strDateTimeToTicks =（选择dbo.DateTimeToTicks（getdate（）））



DECLARE @extension VARCHAR（50）







SET @extension =（SELECT RIGHT（@SrcLink，CHARINDEX（'。'，REVERSE（@SrcLink）））AS [FileExtension]）



SET @ newPath ='prefix'+（选择CAST（@strDateTimeToTicks As varchar））+（选择CAST（@randomNumber As varchar））+ @ extension

< br $>


声明@newFlieName nvarchar（max）=''

SET @ newFlieName = @ DestLink - + @ newPath









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



DECLARE @result int = 0

EXEC master..xp_cmdshell @SqlCopy



我的问题是它无法替换为目标文件名

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

推荐答案

删除 + 字符。您的声明正在尝试将这两个文件复制到一个新文件中，该文件名为 Test1.docx 。您可以在命令窗口中键入 copy /？来检查确切的语法。

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.

快速查看您的评论，@ newPath正在被注释掉。

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

SET @newFlieName=@DestLink--+@newPath

命令的输出类似于：

The output of the command would be something like:

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

这会将位置 C：\New \ 中的文件 Test1.docx 复制到目的地 D：\ Neww \\ 使用相同的文件名 Test1.docx 。



请阅读： http://ss64.com/nt/copy.html [ ^ ]，我发现这是命令行参考的一个很好的来源。



另外你应该知道安全问题 使用xp_cmdshell时。

http：// blogs.msdn.com/b/sqlsecurity/archive/2008/01/10/xp-cmdshell.aspx [ ^ ]

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[^]

这篇关于如何在sqlserver2008中使用xp_cmdshell替换文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！