Problems with SQL output as excel file

ivigor

The answer given by Microsoft is:
EXEC master..xp_cmdshell 'bcp library name.dbo.table name out c:\Temp.xls -c -q -S "servername" -U "sa" -P ""'

There are two questions now:
First, because the extended stored procedure of the system is used, a permission issue is involved, how to configure a common account so that it can call the stored procedure of the system.
Second. The excel exported in this way is pure data content, how to add the field name

恶魔右指

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
Exported statement
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF

hhuaixiaozi

Create a stored procedure and use execute as to specify that the stored procedure uses the sysadmin user internally.
Then grant ordinary users execute permission on this stored procedure.

hhuaixiaozi

The second one, because the original bcp export is a text file, just the xls used for the file name, and the bcp method also determines that the field name cannot be added directly, so you can only change the method, or change bcp to the export query, and then Use union all to associate field names in the query.