How to export the results of a stored procedure directly to a MS Excel file?

Question

I found a way to extract the contents of a MS Sql table directly (faster) to excel. But I do not know how to do this with a stored procedure that requires parameters. Is it possible to extract, directly, to an Excel File the results of a stored procedure? I know how to do it indirectly (using a data table) but it is too slow. Thank you very much.

PS: This is the method I was using to do some tests. It works with a table, but what I need is to extract the result of a stored procedure:

    Private Sub SqlToExcelTest2(ByVal excelFilePath As String, _
                            ByVal nonExistingSheetName As String, _
                            ByVal sqlServer As String, _
                            ByVal sqlDatabase As String, _
                            ByVal sqlUserName As String, _
                            ByVal sqlPassword As String, _
                            ByVal sqlTable As String)

    Const excelConnStrTemplate As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=2"";"

    Dim connStr As String = String.Format(excelConnStrTemplate, _
                                          excelFilePath)

    Const adoQueryTemplate As String = "SELECT * INTO {0} FROM [odbc;Driver={{SQL Server}};" & _
    "Server={1};Database={2};UID={3};PWD={4}].[{5}] "

    Dim query As String = String.Format(adoQueryTemplate, _
    nonExistingSheetName, _
    sqlServer, _
    sqlDatabase, _
    sqlUserName, _
    sqlPassword, _
    sqlTable)

    Using oleConn As New OleDb.OleDbConnection(connStr), oleCmd As New OleDb.OleDbCommand(query, oleConn)
        oleConn.Open()
        oleCmd.ExecuteNonQuery()
        oleConn.Close()
    End Using

End Sub

Answer 1

Is Python an option for you?

A contrived stored procedure that takes a parameter and returns a result set:

create procedure usp_Temp @howmany tinyint
as
set nocount on

create table #tmp (ID int identity, Letter char(1), String varchar(50))

declare @i tinyint
set @i = 0

while @i < @howmany
begin
  insert into #tmp (Letter, String) values('X', 'The quick brown fox...')
  set @i = @i + 1
end

select * from #tmp
set nocount off

In Python, you could do something like this:

import dbi
import odbc
import csv

conn_str = 'Driver={SQL Server};Server=MyServer;Database=MyDb;Uid=MyLogin;Pwd=MyPwd'
conn = odbc.odbc(conn_str)
curs = conn.cursor()

curs.execute('exec usp_temp @howmany = 15')
results = curs.fetchall()

curs.close()
conn.close()

writer = csv.writer(open('tmp.csv', 'wb'), delimiter = ',',
    quoting = csv.QUOTE_MINIMAL)

for result in results: writer.writerow(result)

Excel should be able to open up that CSV file without a problem.

Answer 2

I do not have Excel right now to check, but you might try:

1. Start recording a new macro
2. on a new worksheet select menu Data->Import, and something like "data source"
3. choose your table/view (I am not sure if stored procedure is also supported)
4. setup all the credentials etc.
5. follow the rest of the steps...
6. stop recording macro

and take a look at the generated VBA code.

If you always run the same query (or few different ones), then creating few of such data sources with the auto-refresh on startup could be all you need.