关键词：sql server开启高级配置，使用Bat,cmdshell

1、sql server中使用xp_cmdshell

--允许配置高级选项

EXEC sp_configure 'show advanced options',1

GO

RECONFIGURE

GO

2、

--开启xp_cmdshell服务

EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE

GO

3、

--使用 xp_cmdshell

master..xp_cmdshell 'copy e:\databasebackup\test.bakm:'

4、

-- 允许配置高级选项

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

-- 禁用xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 0

GO

RECONFIGURE

GO

CREATE PROCEDURE [dbo].[Delete_File]

@day NVARCHAR(3),

@url NVARCHAR(100)

AS

BEGIN

 DECLARE @sql NVARCHAR(MAX),@result INT

 EXEC xp_fileexist 'C:\DEL.bat', @result OUTPUT --检查文件DEL.bat存在

 IF @result=1

  EXEC xp_cmdshell 'del C:\DEL.bat'

 IF object_id('test','U')IS NOT NULL   --检查test表是否存在

  DROP TABLE test  

 SET @sql='@echo off

 setlocal enabledelayedexpansion

 Rem 取'+@day+'天之前的日期，取回放入变量riqi

 echo wscript.echo dateadd("d",-'+@day+',date) >%tmp%\tmp.vbs

 for /f "tokens=1,2,3* delims=-" %%i in (''cscript /nologo %tmp%\tmp.vbs'') do set y=%%i&set m=%%j&set d=%%k

 if %m% LSS 10 set m=0%m%

 if %d% LSS 10 set d=0%d%

 set riqi=%y%%m%%d%

 set "mulu='+@url+'"

 for /f "eol= skip=4 tokens=1,4" %%a in (''dir /a-d /tw "%mulu%"^|find /v ^"字节^"'') do (

 set delrq=%%a

 set delrq=!delrq:-=!

 if !delrq! lss %riqi% (

 del /q "%mulu%\%%~nxb"

 )

 )

 pause'

 select @sql a INTO test --将批处理脚本放入test表

 EXEC xp_cmdshell 'bcp "select top 1 a from test" queryout C:\DEL.bat -c -T -S127.0.0.1,2433' --将批处理文件导出到C盘根目录下

 DROP TABLE test --删除test表

 EXEC xp_cmdshell 'C:\DEL.bat'--调用批处理文件

END