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 If you only do this occasionally for testing, can you use the Management Studio functionality for exporting to Excel? Set the query output to go to Excel, then run the sproc in a query window.
以上就是How to export the results of a stored procedure directly to a MS Excel file?的详细内容，更多请关注web前端其它相关文章！
