将变量传递到xp_cmdshell [英] Passing variable into xp_cmdshell
问题描述
我在SQL Server中有一个存储过程,用于检查今天的备份文件(文件名中带有日期的文件).检查后,它将robocopy
这些文件移至另一个文件夹.
I have a stored procedure in SQL Server that checks for today's backup files (files that has a date in its filename). After checks, it will move on to robocopy
these files to another folder.
挑战:在此文件夹中,可能有昨天或其他日期的文件.但是,仅需要今天的bak文件即可进行传输.
The challenge: In this folder, there could be files from yesterday's or other dates. But only today's bak files are required for transfer.
--@day allows me to capture the day of a month
declare @day char(2)
set @day = RIGHT('00' + CONVERT(NVARCHAR(2),DATEPART(DAY,GETDATE())),2)
--print @day
--In "MyFolder", it might containts files like
--Project_backupfile_01_2006_02_28_001.bak
--OR Project_backupfile_01_2006_02_27_001.bak
--Currently I need to hard code 28 to represent 28th. How to pass in @day?
EXEC master..xp_cmdshell 'dir d:\myfolder\Project*28*.bak/b'
--Similarly, I would like to pass in @day variable so that the --Project_backupfile*02_*@day.bak
-- Copy the backup fules from ftp to a local drive
EXEC master..xp_cmdshell 'robocopy "d:\source" "E:\MSSQL\Restore\" Project_backupfile*_02_28*.bak /NFL /NDL /COPY:DAT /R:2 /W:1 /XO /E /Z /MT:10'
推荐答案
使用变量来形成命令,然后再将其传递给xp_cmdshell
Use a variable to form the command before passing it to xp_cmdshell
declare @cmd varchar(100)
select @cmd = 'dir d:\myfolder\Project*' + datename(day, getdate()) + '*.bak/b'
-- print @cmd
exec master..xp_cmdshell @cmd
注意:datename(day, getdate())
将为您提供字符串形式的月份.
Note: datename(day, getdate())
will give you the day of the month as a string.
stuff(convert(varchar(5), getdate(), 101), 3, 1, '_')
将给您02_28
这篇关于将变量传递到xp_cmdshell的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!