TSQL - query export to xls / xslx / csv

Question

I have a complex dynamic query in TSQL that I want to export to Excel. [The result table contains fields with text longer than 255 characters, if that matters]

I know that I can export the result using the Management Studio menu, but I want to do this automatically using code. Do you know how?

Thanks in advance.

Answer 1

You can look at sp_send_dbmail . This allows you to send an email from your query after it is launched containing an attached CSV result set. Obviously, the viability of this method will depend on how large your result set is.

An example from a linked document:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks2008R2 Administrator',
@recipients = '[e-mail]',
@query = 'SELECT COUNT(*) FROM AdventureWorks2008R2.Production.WorkOrder
              WHERE DueDate > ''2006-04-30''
              AND  DATEDIFF(dd, ''2006-04-30'', DueDate) < 2' ,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;

Answer 2

- bcp, , - , , -t, ( CSV), .

, TSQL, OPENROWSET Pinal Dave.

Update: Re:: 2008 64Bit OPENROWSET - , MSDN . ?

, SSIS SQL CLR .NET SQL. bcp TSQL xp_cmdshell - , " " SQL Server. .

Answer 3

: SQL Server Excel Workbench

Answer 4

, -.

- , .

In the end, I went with sending a request to PowerShell from SSMS Read my post here How to create a document on the server by running an existing stored procedure or sql statement of this procedure on SQL Server R2008

Single quotes, however, were a problem, and at first I did not trim my query and write it on one line, so this actually has line breaks in sql studio.