T-SQL代码:
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:/Temp.xls -c -q -S"servername" -U"sa" -P""'
参数:S 是SQL服务器名;U是用户;P是密码
说明:还可以导出文本文件等多种格式
declare @str varchar(600)
select @str=
'bcp "select convert(nvarchar(2),99 )+ CAST(YEAR(trxdatetime) AS NVARCHAR)+CAST(month(trxdatetime) AS NVARCHAR)+CAST(day(trxdatetime) AS NVARCHAR)+CONVERT(varchar(2),trxdatetime,8)+substring(CONVERT(varchar(5),trxdatetime,14),4,4) + convert(nvarchar(2),1 ) + rtrim(ltrim(employeeno)) from sdt..EmployeeTransaction where empgroup=convert(nvarchar(7),2010301) and prodgroup=convert(nvarchar(7),2010301) and logdate=convert(varchar(10),getdate()-1,23) order by employeeno,trxdatetime" queryout "E:/OldFactoryEmployeeAttendance/' +
CAST(YEAR(getdate()) AS NVARCHAR)+CAST(month(getdate()) AS NVARCHAR)+CAST(day(getdate()-1) AS NVARCHAR)
+'.txt" -c -q -S"svr26" -U"SA" -P"123"'
--select @str
EXEC master..xp_cmdshell @str
--Copy 文件复到另一个目录
--remark: copy file to other server
declare @copyFile varchar(300)
set @copyFile='copy E:/OldFactoryEmployeeAttendance/'+ CAST(YEAR(getdate()) AS NVARCHAR)+CAST(month(getdate()) AS NVARCHAR)+CAST(day(getdate()-1) AS NVARCHAR) +
'.txt //gproreport/test/'+ CAST(YEAR(getdate()) AS NVARCHAR)+CAST(month(getdate()) AS NVARCHAR)+CAST(day(getdate()-1) AS NVARCHAR) + '.txt'
--select @copyFile
exec master..xp_cmdshell @copyFile
--注:系统定时运行可把这段sql Code加到Job