sp return values failed to show up in variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sp return values failed to show up in variables

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-23 : 16:28:06

This drives me nuts. I have come down this path many times, but I need to borrow somebody's wisdom.

I have a SP, and it returns one row of data if I run it in QA like this: exec mySP. No input parameter, and the output is a recordset from the select statement.

Here is my calling code. It runs, but just did not get anything back. The cmd.Execute has nothing returned. I have double checked the connection string, make sure it is pointing to the same server as my QA.

Public Function getSP() As ADODB.Recordset
Dim cmd As New ADODB.Command 'ado2.8 msado15.dll
Dim cn As ADODB.Connection

On Error GoTo Err_getSP

Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.open conQWrightDev
'Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "mySP"
.CommandTimeout = 60
End With
Set getSP = cmd.Execute

Thanks!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-03-23 : 16:49:55

Just a thought - have you tried outputting the recordset as a local variable first?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-23 : 16:55:37

Hommer,

-- If u run ur stored proc in Query Analyzer, will that return records?
-- How do u get the output ? as a parameter or by returning a Select statement ?
-- Did u check with some hard coded strings rather than "mySP"
-- U can check the Profiler in SQL Server - when u execute that statement and check whether its executing in the way u want

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-23 : 17:04:40

Timmy,

If you mean dim a rs within the function to receive the return from sp, then pass it to the getSP, yes, I have tried that.

Srinika,
-- If u run ur stored proc in Query Analyzer, will that return records?
In QA, it did return record.
-- How do u get the output ? as a parameter or by returning a Select statement ?
By return the select statement
-- Did u check with some hard coded strings rather than "mySP"
Sorry I don't understand.
-- U can check the Profiler in SQL Server - when u execute that statement and check whether its executing in the way u want
Ok, will try.

Thanks!!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-23 : 17:19:16

Srinika,

The profiler indicates RPC:completed and textDate is the exact one I see in QA: exec mySPName

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-23 : 17:37:29

quote:
Originally posted by Hommer

-- Did u check with some hard coded strings rather than "mySP"
Sorry I don't understand.

Instead of "mySP"
write something like
"Select * from MyTbl"

Also, r u OK with ASP.Net (Means, u did the similar stuff before ?)
How r u using ur rec set ?
[I'm asking this to c to which way u need the help]

Check for getSP.EOF
If getSP.EOF = false, Check for getSP(0)
(May be by assigning to a label)

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-23 : 17:51:37

My rs EOF and BOF : <Operation is not allowed when the object is closed.>
and the cursor is adOpenForwardOnly adLockReadOnly
My value assignment code errors out said, Item cannot be found in the collection corresponding to the reguested name or ordinal.

And this call is not from asp or asp.net, but vb6.

Thanks!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-23 : 20:18:11

VB6 ==> Easier to debug

U can put brk points and check the values of BOF just after it is "Supposed to" execute the SP

>>> My rs EOF and BOF : <Operation is not allowed when the object is closed.> ==>
Either u r checking after closing the RecordSet
Or ur Connection is not active, thus not connecting

>>> My value assignment code errors out said, Item cannot be found in the collection corresponding to the reguested name or ordinal. ==>
Same answer for the above

Also do as timmy suggested and my test as well
>> Define the rs locally, rest normal way, without closing it check EOF

What is ur conQWrightDev ? Global Variable assigned with connection String ?
Did u test with my Hard Coding technique ...?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-24 : 10:52:00

I understand, agree, and have tried everything being suggested here.
Like I have said, I have been down to this path many times, and I thought this should be the easiest part of the project.

The global connection string is fine. I used it to get a “hard code” select * from Mytbl back.

The permission is not the issue. Another piece of front end calls the same SP with success.

I don’t have any line of code that will close my connection or recordset before I call the command object’s execute.

Here is another version of the code block with local rs.

Public Function ReturnSP(strSQL As String) As ADODB.Recordset
Dim cmd As ADODB.Command
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.ConnectionString = conQWrightDev_cnStr
cn.open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = strSQL
' cmd.Execute 'make no difference with or with this line
Set rs = New ADODB.Recordset
rs.ActiveConnection = conQWrightDev_cnStr
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
' rs.open 'is not ready here.

Set rs = cmd.Execute
Set ReturnSP = rs

End Function
Thanks!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-24 : 12:28:20

Hommer,

I'm trying to help u, so bare with me

U may need to answer the Questions "precisely" (Answer Qs 1 by 1)

1. What is ur conQWrightDev ? Global Variable assigned with connection String ?
2. Did u check the value of conQWrightDev, just before it is assigned to
cn.ConnectionString ?
3. Did u test with my Hard Coding technique ...?
ie. instead of passing the Stored Proc name, Pass an SQL Query

Can u check the code as following (Exactly as I show below);

Set rs = cmd.Execute   'ur existing code

msgbox rs.eof
if rs.eof = false then
   msgbox rs(0)
end if
Set ReturnSP = rs      'ur existing code

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 12:41:58

Hommer - Just a thought, but could it be that the sp returns more than 1 recordset, and your VB code is only viewing the first one (which happens to be always empty)? If so you can use 'SET NOCOUNT ON' in SQL or even GetNextRecordset (or something!) in VB. As I say - just a thought

PS - please don't let this post get in the way of the answers you need to provide to Srinika. If it's not my suggestion, please answer him 'precisely' - as he asks - and he'll be able to help you

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-24 : 14:23:56

Srinika,
Here are the answers:
1. Yes.
2. Yes.
3. Yes.
Inserted your code and run, error on
MsgBox rs.eof
Run-time error 3704 Operation is not allowed when the object is closed.

quote:
Originally posted by Srinika

Hommer,

I'm trying to help u, so bare with me

U may need to answer the Questions "precisely" (Answer Qs 1 by 1)

1. What is ur conQWrightDev ? Global Variable assigned with connection String ?
2. Did u check the value of conQWrightDev, just before it is assigned to
cn.ConnectionString ?
3. Did u test with my Hard Coding technique ...?
ie. instead of passing the Stored Proc name, Pass an SQL Query

Can u check the code as following (Exactly as I show below);

Set rs = cmd.Execute 'ur existing code

msgbox rs.eof
if rs.eof = false then
msgbox rs(0)
end if
Set ReturnSP = rs 'ur existing code

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-24 : 14:43:44

[code]

' Comment out everything else in ur Function and place this code.
' Give me the results of each msgbox after running it
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.ConnectionString = conQWrightDev_cnStr
cn.Open

MsgBox IIf(cn.State = 1, "Connected", "Not Connected") '

' put any table name (having records) in the DB where u connect by
' the Connect String in "Variable" conQWrightDev_cnStr, in place of "urTbl" below

rs.Open "Select * from urTbl", cn
MsgBox rs.EOF
If rs.EOF = False Then
MsgBox rs(0)
End If[/code]

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-27 : 08:52:03

Srinika, Here are the results.

MsgBox 1: Connected.
Msgbox 2: False.
Msgbox 2: some real data ( first column of first record from my table)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-27 : 09:16:29

Then Change 1 by 1
1st :
rs.Open "Select * from urTbl", cn -->

rs.Open "mySP", cn
Do While not rs.EOF
MsgBox rs(0)
rs.movenext
Loop
-- U should be OK with the above, but to understand more, do the following:
rs.Open "mySP", cn -- > Set rs = cn.Execute ("mySP")
And u can read all the fields of ur results set (u may know this) as
rs(0), rs(1) ... or
rs("FieldName1"), rs("FieldName2"), ...

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-27 : 09:33:10

rs.Open "mySP", cn
Do While Not rs.EOF -->caused Operation is not allowed when the object is closed. runtime error 3704.

Using next instead of rs.open
Set rs = cn.Execute("mySP") also errored out the same way.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-27 : 10:03:11

Does the SP is in the connected DB ?
Also is it having a Select Query in the end ?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-27 : 10:38:41

Yes and yes.
But when I created a SPTest with a select,
Set rs = cn.Execute("spTest")
did get back the data!

Again, the real SP in QA returns the right data.
The sp has one insert, two update blocks, then last one is the select as follow:

SELECT
'http://www.xyz.com/xml/dtd/jobStatus_1.01.dtd' as DTD,
'http://www.xyz.com:2000/xml/jobStatus.php' as URL,
'ComName' as VendorName,
'ComPwd' as VendorPassword,
ItemPONumber,
LineItemNumber,
Status
FROM Mytbl
WHERE StatusCode < 9999 and Processed = 0 and ShelfItem = 'N'

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-27 : 11:04:50

So the problem would be in mySP ?

Check by commenting out (one at a time)
Insert Part
Update part

and find which one causes the problem.

Also if the Inserts / Updates doesn't cause any problem, remove each field from the list in the select query and c whether something is causing the error.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-03-27 : 11:33:36

Finally!
In the SP, last Update has 0 row(s) affected as the return msg.
When I added "set nocount on" in front of it, and the rest is history!

Thank you Srinika and others for help! You guys are great!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-28 : 03:20:56

You obviously paid attention to my post from 4 days ago then!

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources