Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
"mkarja" wrote in message m... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
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" wrote in message oups.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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting stored procedure result to excel
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can Oracle DB Stored procedure be called in MS Excel? | Excel Discussion (Misc queries) | |||
execute stored procedure from excel | Excel Worksheet Functions | |||
calling a stored procedure on MS SQL Server within MS Excel 2000 | Excel Programming | |||
Run a stored procedure in Excel 2K | Excel Programming | |||
Passing parameter from Excel to stored procedure? | Excel Programming |