mkarja,
I knew I would regret this!
Here's an answer for you. Your stored proc returns a lot of rubbish due to
extensive use of print commands to describe the output in SQL and is not
really usable as far as I can see unless you want to do some serious editing
of the proc. I continue to advocate using a pro tool like SQL Delta.
That said, here's the answer to how to get results of a stored proc. You
have to get the parameters, update the parameters, and then loop through the
recordsets to get all the responses. I've just dumped the output to new
worksheets to save me some time having spent enough on this already. As I've
illustrated, you need a stored proc designed for the purpose if you want to
return something meaningful to Excel.
Sub Test1()
Dim vParams As Variant
Dim vValues As Variant
'using your stored proc
'you get it to work but get pretty much garbage back due to the
'way the sp has been written
vParams = Array("db1", "db2", "TabList", "NumbToShow", _
"OnlyStructure", "NoTimestamp", "VerboseLevel")
vValues = Array("DB1", "DB2", Null, 10, 0, 1, 0)
ReturnRSFromSP "sp_CompareDB", vParams, vValues, "MASTER"
End Sub
'back in SQL DO THE FOLLOWING
'CREATE PROC spTemp(@Table1 nvarchar(50), @Table2 nvarchar(50))
'as
'-- example of a dynamic SQL sp returning multiple recordsets
'SET NOCOUNT ON
'EXEC('SELECT * FROM ' + @Table1)
'EXEC('SELECT * FROM ' + @Table2)
'SET NOCOUNT OFF
'GO
Sub Test2()
Dim vParams As Variant
Dim vValues As Variant
Dim rsReturn As ADODB.Recordset
vParams = Array("Table1", "Table2")
vValues = Array("TableName1", "TableName2")
'change DBNAME to whatever DB you created the above proc in
ReturnRSFromSP "spTemp", vParams, vValues, "DBNAME"
End Sub
Public Sub ReturnRSFromSP(strSP As String, _
vParams As Variant, _
vValues As Variant, _
strCatalog As String)
Dim cnSP As ADODB.Connection
Dim cmdSP As ADODB.Command
Dim lCounter As Long
Dim strItem As String
Dim lIndex As Long
Dim rsReturn As ADODB.Recordset
Set cnSP = New ADODB.Connection
cnSP.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=" & strCatalog & _
";Data Source=" & FILLTHISIN 'add your data source here
cnSP.Open
'create the command object
Set cmdSP = New ADODB.Command
cmdSP.ActiveConnection = cnSP
cmdSP.CommandText = strSP
cmdSP.CommandType = adCmdStoredProc
cmdSP.Parameters.Refresh
lCounter = 0
For lCounter = 1 To cmdSP.Parameters.Count - 1
strItem = cmdSP.Parameters(lCounter).Name
For lIndex = 0 To UBound(vParams)
If "@" & vParams(lIndex) = strItem Then
cmdSP.Parameters(lCounter).Value = vValues(lIndex)
Exit For
End If
Next
Next
'create the recordset object
Set rsReturn = New ADODB.Recordset
With rsReturn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
'execute the SP returning the result into recordsets
.Open cmdSP
End With
Do Until rsReturn Is Nothing
If rsReturn.State = adStateOpen Then
DumpRecordset rsReturn
End If
Set rsReturn = rsReturn.NextRecordset
Loop
Set cmdSP = Nothing
If cnSP.State = adStateOpen Then
cnSP.Close
End If
Set cnSP = Nothing
Set rsReturn = Nothing
End Sub
Sub DumpRecordset(rsName As ADODB.Recordset, Optional lstartpos As Long)
Dim W As Workbook
Dim nField As Integer
Dim lRowPos As Long
If rsName.State = adStateClosed Then Exit Sub
Set W = ActiveWorkbook
Workbooks.Add
With rsName
For nField = 1 To .Fields.Count
Cells(1, nField).Value = .Fields(nField - 1).Name
Next nField
If .RecordCount = 0 Then Exit Sub
.MoveFirst
.Move lstartpos
End With
Cells(2, 1).CopyFromRecordset rsName
End Sub
HTH,
Robin Hammond
www.enhanceddatasystems.com
Robin Hammond said:
mkarja,
Unusual in this group that there is not a ready made answer!
I am gettting results from a sp to Excel via a web tier, so it is
possible, but a quick look at your stored proc suggests it will return
multiple recordsets, which is not something I have designed into my sp's.
I don't have a lot of time tomorrow but I'll try and have a look at it. No
success promised. It's a complex stored proc.
As a question, why do you want to do this in Excel? I use SQL Delta from a
remote machine against a SQL db. I don't think it cost too much and it has
proved to be extremely useful.
Robin Hammond
www.enhanceddatasystems.com
