I have a batch query that I execute from SQL Server Management Studio (v18.5) that has multiple dynamic pieces and generates multiple query results that I would like to export to Excel automatically from the SSMS console, if possible.
In the batch query, I first select a unique set values from 1 column in a table, and I iterate through that list to build a dynamic pivot query using the value from the list. Each pivot query will have a different set of results for each value.
For example: unique list that I loop through:
Type ----- Fan Compressor Belt Motor Filter
The pivot query results for Fan will have a unique set of columns that are different from the pivot query results for Compressor.
Fan pivot columns
FanID, Speed, Weight, Blade Size, RPM
Compressor pivot columns
CompressorID, HP, Voltage, Amps, Height,
Each time I loop through the Type list, I would like to export the pivoted query results to an Excel file. Ideally, I would like to export to 1 workbook with each Type’s pivoted query results having its own worksheet. Since each pivoted query result will have a difference set of columns, I cannot compile all of the query results into 1 table and then export to Excel (or csv). Trying to avoid having multiple files, one for each type.
I have added the following to my batch query:
INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0; Database=C:temptesxt.xlsx;'',''SELECT * FROM [Sheet1$]'')
But I’m getting the following error:
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
I ran these two commands prior to attempting the code above:
EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE
I was hoping there was a down and dirty export option that would eliminate the need to write an application.
I tried the BCP approach, but I was unable to get it to work. I changed the server name to mine and the database name to mine; no luck. The @sql variable is my dynamic pivot query. Tried different bcp command parameters with no luck.
Getting a little closer; I replaced @sql from the bcp command above with a simple select query and the command executed, creating a new .csv file for each "Type" in the type list; there must be something in the complex dynamic pivot @sql string that bcp doesn’t like. Regardless, I know the underlying plumbing of having bcp export query results to a csv file is working.