用bcp命令写入文件

一种更为简单的方式写入excel/ csv 文件的方式,这种方式的好处在于不必提前创建文件 ，显得更为professonal 一点吧：

1. enable xp_cmdshell，否则报错 SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
2. 创建文件夹D:\Temp\
3. 调用bcp 命令

use <servername>
go

exec sp_configure 'xp_cmdshell',1 --如果不enable也会报错
reconfigure


EXEC xp_cmdshell 'MKDIR "D:\Temp\"' --否则会报错，BCP error -Unable to open BCP host data-file,看似意思是无权限，实际是没有创建相应的路径，也会报错

DECLARE @stmt_c VARCHAR(8000);
SET @stmt_c=
    'BCP '+
    '"SELECT  top 1 * FROM '+QUOTENAME(DB_NAME())+'.dbo.tablename" '+
    'QUERYOUT "D:\Temp\filename.csv" '+
    '-c -t, -T -S ';
	 print @stmt_c
EXEC master.sys.xp_cmdshell @stmt_c;

也可以直接调用bcp.exe 创建文件, 参考于：https://stackoverflow.com/questions/18619061/unable-to-open-bcp-host-data-file :

-- BCP - Export query, pipe delimited format, trusted security, character format
DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @exe_path4 VARCHAR(200) = 
    ' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & ';
SET @bcp_cmd4 =  @exe_path4 + 
    ' BCP.EXE "SELECT FirstName, LastName FROM AdventureWorks2008R2.Sales.vSalesPerson" queryout ' +
    ' "C:\TEST\PEOPLE.TXT" -T -c -q -t0x7c -r\n';
PRINT @bcp_cmd4;
EXEC master..xp_cmdshell @bcp_cmd4;
GO