Getting stored procedure result to excel

[mkarja]

Hi,

I have a stored procedure in SQL Server 2000 that returns result
as text. I want to run that stored procedure from excel, but I'm
a bit lost on how to do that. I can get a normal result set from
sql server to excel.
The results should go into a text box.

Any help is much appreciated.

----
mkarja

[mkarja]

The stored procedure that I'm using is found on the following web site.
http://www.sql-server-performance.co...on_sp_code.asp

I would like to use excel to compare two databases or two tables from
different databases. The excel sheet has a two drop down boxes that you

can choose the databases and two drop boxes where you can choose the
corresponding tables to compare.
The drop boxes work. The database and table names are retrieved from
the SQL Server 2000 database.
I don't know how to get the stored procedure to return the result and
put it in the textbox that's on the excel sheet.

----
mkarja

[mkarja]

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

[Robin Hammond]

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" <mkarja@gmail.com> wrote in message
news:1109848716.737418.109160@g14g2000cwa.googlegroups.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
>

[Fredrik Wahlgren]

"mkarja" <mkarja@gmail.com> wrote in message
news:b8a00e37.0503010135.96c144d@posting.google.com...
> Hi,
>
> I have a stored procedure in SQL Server 2000 that returns result
> as text. I want to run that stored procedure from excel, but I'm
> a bit lost on how to do that. I can get a normal result set from
> sql server to excel.
> The results should go into a text box.
>
> Any help is much appreciated.
>
> ----
> mkarja
>

I guess you could use something like this:

call procedure_name[([parameter][,[parameter]]...)]}
where procedure_name specifies the name of a procedure and parameter
specifies a procedure parameter.



Does your stored procedure take any arguments?

/Fredrik

[Robin Hammond]

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" <rjNOrhSPAM@PLEASEnetvigator.com> wrote in message
news:OyaOryAIFHA.2564@tk2msftngp13.phx.gbl...
> 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" <mkarja@gmail.com> wrote in message
> news:1109848716.737418.109160@g14g2000cwa.googlegroups.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
>>
>
>

[mkarja]

Thank you very much for going thru so much trouble for helping me
robin. I've implemented your code to my vba code in excel but I
get one error when trying to run it.

> 'execute the SP returning the result into recordsets
> .Open cmdSP

The error comes from that .Open cmdSP line and the error is
Run-time error '-2147217900 (80040e14)'

I've tried some different things with it but can't seem to be able
to make it work. If you still have enough enthusiasm to wrestle
with this I'd be thankful. I will continue to try to fix it myself
but any help wouldn't hurt either.

The reason I'm using this procedure is that it's the only thing
I've found to compare two tables for differences. I know it's a
bit of a mess of an procedure but until I can find some better
way I'll be using that. If I can't get this thing to work it's
not the end of the world but still it would be helpful.
I'll check out the tool you recommended, but it's not my
decision whether we can use it or not. If it costs money we
propably won't be able to use it.

Thanks again for the help.

----
mkarja

[mkarja]

Damn, that SQL Delta is just what I would've wanted.
It's a shame that it's not a free software.
Some time at the end of last year I tried to search a software
like that, but I didn't find that one then.
Oh well, you can't win everytime.

----
mkarja

[Robin Hammond]

That looks like a "Could not find stored procedure" error.

First, please test this using my short demo proc rather than your long one.
Second, have you got the name of the proc absolutely right? If it's set up
under a different user account to the one you are running under, have you
fully qualified the proc name.

e.g.
mkarja.spTemp rather than just spTemp.

Third, make sure you have exec permission on the proc for the account you
are running under?

Robin Hammond
www.enhanceddatasystems.com

"mkarja" <mkarja@gmail.com> wrote in message
news:1109944465.296732.270750@l41g2000cwc.googlegroups.com...
> Damn, that SQL Delta is just what I would've wanted.
> It's a shame that it's not a free software.
> Some time at the end of last year I tried to search a software
> like that, but I didn't find that one then.
> Oh well, you can't win everytime.
>
> ----
> mkarja
>

[mkarja]

I tested using your demo proc and it worked, so I propably have
something not right in my own code.
Thanks for all your help, I think I can manage from now on.