September 24, 2014 at 4:48 pm
I've been tasked with coming up with a method to export queries to Excel from SQL Server 2008 and 20012. Ideally, I'd like to pass in a SQL statement or stored procedure/parameters and send that to an Excel file.
I've taken the approach of the following:
insert into openrowset('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES; Database=\\URL\ExistingFileName.xlsx;',
'SELECT * FROM [Sheet2$]')
select name, database_id from master.sys.databases
order by name
That seems to work well enough, but then I run into a wall when it gets more complicated, and I can't seem to find a way to execute a stored procedure. Would I be best off putting the results in a #temp table and then exporting those results?
SSIS and SSRS are not great candidates for this because they feel its a higher maintenance solution.
September 24, 2014 at 4:51 pm
Funny, I would have thought the SSIS solution would be perfect. It even has the Excel destination that you could configure... Execute a stored procedure? Nothing doing. AND you could schedule the package to execute on a schedule by creating a job. Done right, this would be a zero maintenance solution. Do it once, make sure it works, and then forget all about it.
September 24, 2014 at 4:54 pm
In many ways, that would be true. But they didn't wan't to write a new package, or add to an additional one, every time they have new SQL. Doesn't really allow for ad hoc queries so much either.
Andre
September 24, 2014 at 5:52 pm
Are the queries already in SQL Server (as views or stored procedures), or are they completely ad hoc and being written by the people using Excel? One way might be to create views of the data you want the users to be able to see and then link to them in maybe Access and then export from there using ADO... (Sorry, I'm not very good at Excel, so my "manipulate data on the fly" tool would probably be Access...)
September 24, 2014 at 6:46 pm
I have done this at a program office with the Air Force where the business analyst wanted data out of the database in order to manipulate it in Excel for some monthly user reports.
I created a stored procedure for the data they wanted to pull. I then showed here how to create a data connection from Excel and execute the procedure from that connection where it would populate the worksheet in Excel.
When you first create the Excel connection you just pick a table or view because that is initially all it will let you select. After that I have attached the a word document that includes screenshots of where you can then alter it to include a procedure call.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 25, 2014 at 9:18 am
I remember doing this as well. Created a bunch of views in a separate schema that the users had access to. The views had friendly column names and made it all much more ready for civilians to use.
On my end, the Excel sheets at times will eventually be sent out as an attachment to email, so there are subsequent steps.
Actually, on a somewhat related note: is there a way to execute VBA macros on an Excel sheet? That way, after the data is placed there, in theory, I could run a macro to maybe create a pivot table or graph. I could definitely see that as a Phase 2 type thing for my user group.
Thanks
Andre
September 25, 2014 at 9:21 am
Actually, on a somewhat related note: is there a way to execute VBA macros on an Excel sheet? That way, after the data is placed there, in theory, I could run a macro to maybe create a pivot table or graph. I could definitely see that as a Phase 2 type thing for my user group.
That is beyond the limit of what I will do in Excel 🙂
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 25, 2014 at 10:07 am
Follow what Shawn Melton said about how to set up a data connection in Excel to a SQL stored procedure.
The data will then display as a table in your Excel workbook and whenever the Excel connection is refreshed (either manually or through VBA code) the stored procedure will execute and repopulate the table will the results.
Then you can just make a pivot table that is linked to your SQL connected table by highlighting the entire range of the table (including headers) --> Insert Pivot Table...Excel will default to grabbing the name of the table...this is GOOD because it will keep the Pivot Table linked to the table so as the table grows or shrinks, the Pivot can reflect that.
You are now free to build the pivot table off the data however you see fit and it will be linked to the table in your spreadsheet which is linked to the SQL stored procedure.
All can updated simply with VBA...something like the below code would work. It will update all data connections and Pivot Tables in a particular workbook.
Public Sub RefreshAll()
ActiveWorkbook.RefreshAll
End Sub
**Just be weary that the data connection in Excel will store your login credentials as plain text if you opt to save the password in the data connection (this prevents Excel from prompting users for SQL Server login and password when refreshing the data connection to execute the stored procedure) so best practice is to set up the connection with a SQL login with limited permissions.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply