declare @fn nvarchar(max) = CONVERT(char(8),GETDATE(),112)+CONVERT(char(15),GETDATE(),114)
set @fn ='D:\文件\' +rtrim(replace(@fn,':',''))+'.xls'
SET @fn = 'EXEC master..xp_cmdshell'+'''bcp "select * from TOPLED2015..COPTDLog1" queryout ' + @fn + ' -c -q -T'''
EXEC(@fn)
/*

BCP用法:
bcp {dbtable | query} {in | out | queryout | format} 數(shù)據(jù)文件
dbtable | query :表或結(jié)果集
in : 導(dǎo)入數(shù)據(jù)
out : 導(dǎo)出數(shù)據(jù)
queryout : 有條件導(dǎo)出數(shù)據(jù) 
  [-m 最大錯誤數(shù)]                         [-f 格式化文件]       [-e 錯誤文件]
  [-F 首行]                                      [-L 末行]                 [-b 批大小]
  [-n 本機(jī)類型]                              [-c 字符類型]          [-w 寬字符類型]
  [-N 將非文本保持為本機(jī)類型] [-V 文件格式版本] [-q 帶引號的標(biāo)識符]
  [-C 代碼頁說明符]                     [-t 字段終止符]       [-r 行終止符]
  [-i 輸入文件]                               [-o 輸出文件]         [-a 數(shù)據(jù)包大小]
  [-S 服務(wù)器名稱]                         [-U 用戶名]             [-P 密碼]
  [-T 可信連接]                             [-v 版本]                   [-R 允許使用區(qū)域設(shè)置]
  [-k 保留空值]                             [-E 保留標(biāo)識值]
  [-h"加載提示"]                           [-x 生成xml 格式化文件]
    */
    --帶條件查詢導(dǎo)出，
EXEC master..xp_cmdshell 'bcp "select * from TEST..Demo_A" queryout D:\Test\1.xls -c -q -T'
EXEC master..xp_cmdshell 'bcp "select * from TEST..Demo_A" queryout D:\Test\1.xls -c -q -S"." -U"sa" -P"123123"'
--所有表數(shù)據(jù)導(dǎo)出
EXEC master..xp_cmdshell 'bcp TEST.dbo.Demo_A out D:\Test\1.xls -c -q -T'
--導(dǎo)入數(shù)據(jù)，表結(jié)構(gòu)一致
EXEC master..xp_cmdshell 'bcp TEST..Demo_A in D:\Test\1.xls -T -c'

--解決一些報(bào)錯處理
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO