| |

VerySource

 Forgot password?
 Register
Search
View: 817|Reply: 3

Problems with SQL output as excel file

[Copy link]

1

Threads

1

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 China

Post time: 2020-3-3 11:30:01
| Show all posts |Read mode
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
Reply

Use magic Report

0

Threads

93

Posts

46.00

Credits

Newbie

Rank: 1

Credits
46.00

 China

Post time: 2020-6-25 11:00:02
| Show all posts
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
Exported statement
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
Reply

Use magic Report

0

Threads

9

Posts

7.00

Credits

Newbie

Rank: 1

Credits
7.00

 China

Post time: 2020-6-29 13:00:01
| Show all posts
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.
Reply

Use magic Report

0

Threads

9

Posts

7.00

Credits

Newbie

Rank: 1

Credits
7.00

 China

Post time: 2020-6-29 14:45:01
| Show all posts
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.
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list