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" wrote in message
...
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

"mkarja" wrote in message
oups.com...
Is there a way to do this or do I have to try to export the results to
a text
file first and read it from that file to the textbox in excel.
Please, anybody ?

----
mkarja