怎么用SQL 把这句话
set /a StartS=%time:~6,2%
原样输出到1.bat文件中呢??
set @delete_fullBak =N'echo set /a StartS='+'%'+'time:~6,2'+'%>>'+ @bat_path
exec master..xp_cmdshell @delete_fullBak
输出后显示 set /a StartS=45 郁闷了 啊
我要原样显示
set /a StartS=%time:~6,2%
原样输出到1.bat文件中呢??
set @delete_fullBak =N'echo set /a StartS='+'%'+'time:~6,2'+'%>>'+ @bat_path
exec master..xp_cmdshell @delete_fullBak
输出后显示 set /a StartS=45 郁闷了 啊
我要原样显示
感觉你自己多调试几种方法,应该很容易解决
declare @bat_path varchar(256), @cmd varchar(4000);set @bat_path = 'c:\t.bat'
set @cmd = 'echo set /a StartS='+'^%'+'time:~6,2'+'^%>>'+ @bat_pathexec master..xp_cmdshell @cmd
declare @delete_fullBak nvarchar(100), @bat_path nvarchar(100)
set @bat_path=N'E:\1.bat'
set @delete_fullBak =N'echo set /a StartS='+'^%'+'time:~6,2'+'^%>>'+ @bat_path
exec master..xp_cmdshell @delete_fullBak
set /a StartM=%time:~3,2%
set /a StartH=%time:~0,2%哭泣 能加换行吗?
是不是只能一句一句输出了?
LZ就是要在 bat中 显示這串字串: set /a StartS=%time:~6,2%
SQL 利用xp_cmdshell,调 dos命令下的 echo 直接写资料。
但是因为加了 %%,会认为是变量,所以在%前加^
echo new line 1 >> t.txt
echo new line 2 >> t.txt
这样本身就有换行,是一行行的加入文件。
要不能换行那我 只能每行执行一次 exec master..xp_cmdshell @delete_fullBak
??????
exec master..xp_cmdshell @delete_fullBak
了?
set /a EndS=%time:~6,2%
set /a EndM=%time:~3,2%
set /a EndH=%time:~0,2%
echo 结束时间:%time%
set /a diffS_=%EndS%-%StartS%
set /a diffM_=%EndM%-%StartM%
set /a diffH_=%EndH%-%StartH%用SQL拼出生成一个类似于这样的批
如果是命令换,可以用 & 串set @delete_fullBak =N'echo set /a StartS='+'^%'+'time:~6,2'+'^% >> '+@bat_path+' & echo set /a StartS='+'^%'+'time:~1,2'+'^% >> '+ @bat_path如果只是参数变,可以用 % 来传参
GO
/****** 对象: StoredProcedure [dbo].[gen_diff_copyscript] 脚本日期: 02/25/2010 13:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOalter PROCEDURE [dbo].[gen_diff_copyscript]
@bak_fullpath varchar(400) , --备份文件存储路径,如 'T:\P_backup_'
@copy_fullpath varchar(400), --拷贝目的路径,如'\\192.168.1.5\D$\datd_backup_'
@bat_path varchar(400), -- 批处理文件存放路径,如'T:\Pfull.bat'
@restore_job varchar(400), -- 调用还原作业的名称
@amount int = 30, -- 备份文件个数
@flagflie_bak varchar(200) = '\\dbg\T$\Tesp\Tlk\flbackup_flag.txt', -- 备份标志文件
@restore_job_log varchar(400) = 'T:\Pie_joblog.log' -- 日志文件
AS
--执行想要的xp_cmdshell语句
DECLARE @sql varchar(8000)
DECLARE @int int
DECLARE @file varchar(200)
DECLARE @cmd varchar(200)
DECLARE @battemp varchar(500)
DECLARE @delete_fullBak varchar(2000)set @battemp = 'echo @echo off>'+@bat_path
exec master..xp_cmdshell @battemp --重写批处理文件
set @delete_fullBak =N'echo set /a StartS='+'^%'+'time:~6,2'+'^%>>'+ @bat_path
+N'&& echo set /a StartM=^%time:~3,2^%>>'+ @bat_path
+N'&& echo set /a StartH=^%time:~0,2^%>>'+ @bat_path
+N'&& echo echo 开始时间:^%time^% >>'+ @bat_path
exec master..xp_cmdshell @delete_fullBakset @delete_fullBak ='echo del '+ @flagflie_bak +'>>' + @bat_path --删除标志文件
print @delete_fullBak
exec master..xp_cmdshell @delete_fullBak
set @delete_fullBak ='echo del '+ @copy_fullpath +'*.bak>>'+ @bat_path
exec master..xp_cmdshell @delete_fullBak --删除本机备份路径下文件SET @int = 1 --设置备份文件起始名称
SET @file = RIGHT('00'+cast(@int AS varchar),2) --备份文件的序列号
WHILE (@int<=@amount) --根据备份文件数,可以修改此值大小,本例是有30个备份文件
BEGIN
set @sql='copy '+ @bak_fullpath +
+cast(rtrim(convert(varchar,getdate(),112))AS varchar)+'_'+ @file +'.bak '+@copy_fullpath+@file+'.bak'
PRINT (@sql)
SET @cmd = 'echo ' + @sql + ' >> '+@bat_path --拼接SQL,生成批处理脚本
PRINT (@cmd)
EXEC xp_cmdshell @cmd --脚本输出到.bat
SET @int=@int+1
SET @file = RIGHT('00'+cast(@int AS varchar),2)
end
set @delete_fullBak =N'echo set /a EndS=^%time:~6,2^%>>'+@bat_path
+N' && echo set /a EndM=^%time:~3,2^%>>'+ @bat_path
+N'&& echo set /a EndH=^%time:~0,2^%>>'+ @bat_path
+N'&& echo echo 结束时间:^%time^% >>'+ @bat_path
+N'&& echo set /a diffS_=^%EndS^%-^%StartS^%>>'+ @bat_path
+N'&& echo set /a diffM_=^%EndM^%-^%StartM^%>>'+ @bat_path
+N'&& echo set /a diffH_=^%EndH^%-^%StartH^%>>'+ @bat_path
+N'&& echo if ^%diffH_^%==1 set /a diffM_=^%diffM_^%+60 >>'+ @bat_path
+N'&& echo if %diffH_%==2 set /a diffM_=%diffM_%+120>>'+@bat_path
+N'&& echo if %diffH_%==3 set /a diffM_=%diffM_%+180>>'+@bat_path
+N'&& echo echo 拷贝文件运行时间:%diffM_%分钟%diffS_%秒 复制于: ^%time^% 结束^>^>'+@restore_job_log+'>>'+@bat_pathexec master..xp_cmdshell @delete_fullBak--SET @bat ='echo ' + 'sqlcmd -S wangxf\wfei -Q "print ''在'' + convert(varchar,getdate(),100)+''启动作业''; use msdb exec sp_start_job @job_name= ''还原''" -W ^>d:\fullbak.txt '+ '>> d:\fullbak.bat'
SET @battemp ='echo ' + 'sqlcmd -S dbvs-bak -Q "print ''在'' + convert(varchar,getdate(),100)+''启动作业''; use msdb exec sp_start_job @job_name= N'''+@restore_job+'''" -W ^>^>'+@restore_job_log+ '>> '+@bat_path
print @battemp
EXEC xp_cmdshell @battemp --追加在脚本最后一句,目的主要是启动“还原作业”
'd:\full.bat','还原_TianTalk.Subplan_1',1,'D:\dablag.txt','D:\log.log'测试通过
谢谢老猫和沟沟 一会再开帖散分