Export SQL query to excel with multiple worksheets and custom headers

Question

What I need to do is, a client wants to have a report in an excel doc with multiple worksheets with custom headers. I have tried SSRS 2008 Report Builder 2.0 but naming worksheets is not available with SSRS 2008 Report Builder 2.0. I have tried bcp in SQL Server Management Studio, but am not able to export it into multiple worksheets. I have put the queries into temp tables, is there a way to export those queries into the same excel doc but different worksheets with a different header for each worksheet.

Like this enter image description here

Notice how each worksheet has a different name and a different header.

Is this possible to do with SQL or is there a workaround for SSRS 2008 Report Builder 2.0?

Greg the Incredulous · Accepted Answer · 2015-02-24 00:05:37Z

3

You could use SQL Server Integration Services 2008R2 (SSIS) to do this. SSIS has an Excel Data Flow Destination that accepts a worksheet name as a parameter. You could construct your SSIS package to populate the various worksheets of a spreadsheet this way.

answered Feb 24, 2015 at 0:05

Greg the Incredulous

1,8765 gold badges34 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jjohnson Over a year ago

I have never used SSIS could you walk me through it to get started?

Francesco Mantovani · Accepted Answer · 2018-06-27 09:45:16Z

I know, I know... you too you faced the error:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

You can do it in SSMS through T-SQL, follow this example:

First you need to allow SSMS to bypass the error:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

Then you can save the result in a precise Excel Tab this way:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM [Sheet1$]') 
SELECT * FROM dbo.DimScenario

The file .XLSX must already be there with the tab having the precise name [Sheet1$]

Collectives™ on Stack Overflow

Export SQL query to excel with multiple worksheets and custom headers

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related