如何在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 namedTest1.docx
. You can check the exact syntax by typingcopy /?
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屋!