--1. 开启 xp_cmdshell . 注:风险较高, 用完要关闭
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE;
GO
--2.1 sql 账户导出
EXEC master..xp_cmdshell 'bcp "select 1 as r" queryout "d:\test.xls" -c -S".\sqlserver2014" -U"userName" -P"password" '
--2.2 windows账户导出
EXEC master..xp_cmdshell 'bcp "select 1 as r" queryout "d:\test2.xls" -c -S".\sqlserver2014" -T '
--2.3 根据存储过程导出
EXEC master..xp_cmdshell 'bcp "EXEC [AdventureWorks2014].dbo.Proc_Test" queryout "d:\test2.xls" -c -S".\sqlserver2014" -T '