problem with OUTPUT + returned recordset

I have written a stored procedure which contains a Select that returns a
recordset,
and returns a pair of OUTPUT values. The Recordset returned is correct, but
I cannot access the OUTPUT args until I close the recordset. I've seen
reference to this in SQL 7, but not for SQL 2000. I need the open recordset
and the return values at the same time. I've tried changing the Recordset
properties from adUseServer to adUseClient etc with no success.
Thanks for any help,
Jack
The following is both sp & vb code to execute.
CREATE PROCEDURE Select_LatestTimeSlice
(@.dataID [int],
@.TS_ID [int] OUTPUT,
@.RowCnt [int] OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.TSID int
DECLARE @.numcnt int
-- get TSID(s) for this dataID value; and recordcount
Select @.TSID=TS_ID, @.numcnt = COUNT(TS_ID) FROM TimeSlices
WHERE DATAID= @.dataID GROUP BY TS_ID
-- get recordset containing all rows where DATAID = this dataID
SELECT * FROM TimeSlices WHERE DATEID= @.dataID
SET @.RowCnt = @.numcnt
SET @.TS_ID = @.TSID
END
GO
=========================
Dim rsdata As ADODB.Recordset
Set rsdata = New ADODB.Recordset
rsdata.CursorLocation = ad_UseServer 'ad_UseClient
rsdata.CursorType = ad_OpenStatic 'ad_OpenDynamic
rsdata.LockType = adLockReadOnly 'adLockOptimistic
'
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = "Select_LatestTimeSlice"
.CommandType = adCmdStoredProc
'
.Parameters("@.dataID") = varDataID
'
' Pull the Trigger......
Set rsdata = .Execute() ' , , adExecuteNoRecords
'----
' when enabled these lines return NULL and I cannot get values
later
'Debug.Print Format(.Parameters("@.TS_ID"))
'Debug.Print Format(.Parameters("@.RowCnt"))
End With
'
With rsdata
' recordset data is correct
Debug.Print Format(.Fields("DATAID")) & " " &
Format(.Fields("TS_ID"))
rsdata.Close
Debug.Print Format(cmd.Parameters("@.TS_ID"))
Debug.Print Format(cmd.Parameters("@.RowCnt"))
End WithThat is the way sql server works. it sends output parameters and return valu
e
in the last packet it returns to the client. See "Parameters Markers" in BOL
.
You have to process or cancel all result sets returned by the stored
procedure before you have access to the return code and output parameter
values.
Instead using the execute method of the command, use the command as the
source of the recordset open method.
Example:
use northwind
go
create procedure dbo.usp_p1
@.sd datetime,
@.ed datetime,
@.rowcnt int output
as
set nocount on
declare @.error int
select
orderid,
customerid,
orderdate
from
dbo.orders
where
orderdate >= convert(varchar(8), coalesce(@.sd, getdate()), 112)
and orderdate < dateadd(day, 1, convert(varchar(8), coalesce(@.ed,
getdate()), 112))
select @.error = @.@.error, @.rowcnt = @.@.rowcount
return @.error
go
-- vb6
Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRs As ADODB.Recordset
Set objConn = New ADODB.Connection
Set objCmd = New ADODB.Command
Set objRs = New ADODB.Recordset
With objConn
.ConnectionString =
"provider=sqloledb;server=weg-256;database=northwind;integrated security=SSP
I"
.Errors.Clear
.Open
End With
With objCmd
.CommandText = "dbo.usp_p1"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.return_value", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@.sd", adVarChar, adParamInput,
8, "19970701")
.Parameters.Append .CreateParameter("@.ed", adVarChar, adParamInput,
8, "19970731")
.Parameters.Append .CreateParameter("@.rowcnt", adInteger,
adParamOutput)
.ActiveConnection = objConn
End With
With objRs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With
objRs.Open objCmd
MsgBox objRs.Fields(0) & " - " & objRs.Fields(1) & " - " & objRs.Fields(2)
MsgBox objCmd.Parameters("@.rowcnt").Value
objRs.Close
objConn.Close
Set objConn = Nothing
Set objCmd = Nothing
Set objRs = Nothing
End Sub
AMB
"hushtech" wrote:

> I have written a stored procedure which contains a Select that returns a
> recordset,
> and returns a pair of OUTPUT values. The Recordset returned is correct, b
ut
> I cannot access the OUTPUT args until I close the recordset. I've seen
> reference to this in SQL 7, but not for SQL 2000. I need the open records
et
> and the return values at the same time. I've tried changing the Recordset
> properties from adUseServer to adUseClient etc with no success.
> Thanks for any help,
> Jack
> The following is both sp & vb code to execute.
> CREATE PROCEDURE Select_LatestTimeSlice
> (@.dataID [int],
> @.TS_ID [int] OUTPUT,
> @.RowCnt [int] OUTPUT)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.TSID int
> DECLARE @.numcnt int
> -- get TSID(s) for this dataID value; and recordcount
> Select @.TSID=TS_ID, @.numcnt = COUNT(TS_ID) FROM TimeSlices
> WHERE DATAID= @.dataID GROUP BY TS_ID
> -- get recordset containing all rows where DATAID = this dataID
> SELECT * FROM TimeSlices WHERE DATEID= @.dataID
> SET @.RowCnt = @.numcnt
> SET @.TS_ID = @.TSID
> END
> GO
> =========================
> Dim rsdata As ADODB.Recordset
> Set rsdata = New ADODB.Recordset
> rsdata.CursorLocation = ad_UseServer 'ad_UseClient
> rsdata.CursorType = ad_OpenStatic 'ad_OpenDynamic
> rsdata.LockType = adLockReadOnly 'adLockOptimistic
> '
> Set cmd = New ADODB.Command
> With cmd
> .ActiveConnection = cn
> .CommandText = "Select_LatestTimeSlice"
> .CommandType = adCmdStoredProc
> '
> .Parameters("@.dataID") = varDataID
> '
> ' Pull the Trigger......
> Set rsdata = .Execute() ' , , adExecuteNoRecords
> '----
> ' when enabled these lines return NULL and I cannot get values
> later
> 'Debug.Print Format(.Parameters("@.TS_ID"))
> 'Debug.Print Format(.Parameters("@.RowCnt"))
> End With
> '
> With rsdata
> ' recordset data is correct
> Debug.Print Format(.Fields("DATAID")) & " " &
> Format(.Fields("TS_ID"))
> rsdata.Close
> Debug.Print Format(cmd.Parameters("@.TS_ID"))
> Debug.Print Format(cmd.Parameters("@.RowCnt"))
> End With
>|||You could "forget" about using Output parameters and return the values as a
Recordset.
Select TS_ID, COUNT(TS_ID) as rowCnt FROM TimeSlices
WHERE DATAID= @.dataID GROUP BY TS_ID
...and then use the nextRecordset method in your page.
Or keep the method you are using but use getRows to "transfer" your
recordset into an array.
Then close the recordset and access the Output parameters.
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:377AB4EE-033A-4A6A-A87C-5DD8AD355556@.microsoft.com...
>I have written a stored procedure which contains a Select that returns a
> recordset,
> and returns a pair of OUTPUT values. The Recordset returned is correct,
> but
> I cannot access the OUTPUT args until I close the recordset. I've seen
> reference to this in SQL 7, but not for SQL 2000. I need the open
> recordset
> and the return values at the same time. I've tried changing the Recordset
> properties from adUseServer to adUseClient etc with no success.
> Thanks for any help,
> Jack
> The following is both sp & vb code to execute.
> CREATE PROCEDURE Select_LatestTimeSlice
> (@.dataID [int],
> @.TS_ID [int] OUTPUT,
> @.RowCnt [int] OUTPUT)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.TSID int
> DECLARE @.numcnt int
> -- get TSID(s) for this dataID value; and recordcount
> Select @.TSID=TS_ID, @.numcnt = COUNT(TS_ID) FROM TimeSlices
> WHERE DATAID= @.dataID GROUP BY TS_ID
> -- get recordset containing all rows where DATAID = this dataID
> SELECT * FROM TimeSlices WHERE DATEID= @.dataID
> SET @.RowCnt = @.numcnt
> SET @.TS_ID = @.TSID
> END
> GO
> =========================
> Dim rsdata As ADODB.Recordset
> Set rsdata = New ADODB.Recordset
> rsdata.CursorLocation = ad_UseServer 'ad_UseClient
> rsdata.CursorType = ad_OpenStatic 'ad_OpenDynamic
> rsdata.LockType = adLockReadOnly 'adLockOptimistic
> '
> Set cmd = New ADODB.Command
> With cmd
> .ActiveConnection = cn
> .CommandText = "Select_LatestTimeSlice"
> .CommandType = adCmdStoredProc
> '
> .Parameters("@.dataID") = varDataID
> '
> ' Pull the Trigger......
> Set rsdata = .Execute() ' , , adExecuteNoRecords
> '----
> ' when enabled these lines return NULL and I cannot get values
> later
> 'Debug.Print Format(.Parameters("@.TS_ID"))
> 'Debug.Print Format(.Parameters("@.RowCnt"))
> End With
> '
> With rsdata
> ' recordset data is correct
> Debug.Print Format(.Fields("DATAID")) & " " &
> Format(.Fields("TS_ID"))
> rsdata.Close
> Debug.Print Format(cmd.Parameters("@.TS_ID"))
> Debug.Print Format(cmd.Parameters("@.RowCnt"))
> End With
>|||Alejandro,
Thanks for the solution to my problem. I've implemented it successfully.
You referred me to "Parameters Markers" in BOL. I'm not familiar with what
BOL is and how to find it. Please give me a pointer if you can.
Thanks again for the help. I'm really happy with how quickly responses are
given on this forum - and how accurate and helpful they are.
-- jack
"Alejandro Mesa" wrote:
> That is the way sql server works. it sends output parameters and return va
lue
> in the last packet it returns to the client. See "Parameters Markers" in B
OL.
> You have to process or cancel all result sets returned by the stored
> procedure before you have access to the return code and output parameter
> values.
> Instead using the execute method of the command, use the command as the
> source of the recordset open method.
> Example:
> use northwind
> go
> create procedure dbo.usp_p1
> @.sd datetime,
> @.ed datetime,
> @.rowcnt int output
> as
> set nocount on
> declare @.error int
> select
> orderid,
> customerid,
> orderdate
> from
> dbo.orders
> where
> orderdate >= convert(varchar(8), coalesce(@.sd, getdate()), 112)
> and orderdate < dateadd(day, 1, convert(varchar(8), coalesce(@.ed,
> getdate()), 112))
> select @.error = @.@.error, @.rowcnt = @.@.rowcount
> return @.error
> go
> -- vb6
> Private Sub Command1_Click()
> Dim objConn As ADODB.Connection
> Dim objCmd As ADODB.Command
> Dim objRs As ADODB.Recordset
> Set objConn = New ADODB.Connection
> Set objCmd = New ADODB.Command
> Set objRs = New ADODB.Recordset
> With objConn
> .ConnectionString =
> "provider=sqloledb;server=weg-256;database=northwind;integrated security=S
SPI"
> .Errors.Clear
> .Open
> End With
> With objCmd
> .CommandText = "dbo.usp_p1"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@.return_value", adInteger,
> adParamReturnValue)
> .Parameters.Append .CreateParameter("@.sd", adVarChar, adParamInput
,
> 8, "19970701")
> .Parameters.Append .CreateParameter("@.ed", adVarChar, adParamInput
,
> 8, "19970731")
> .Parameters.Append .CreateParameter("@.rowcnt", adInteger,
> adParamOutput)
> .ActiveConnection = objConn
> End With
> With objRs
> .CursorLocation = adUseClient
> .CursorType = adOpenStatic
> .LockType = adLockOptimistic
> End With
> objRs.Open objCmd
> MsgBox objRs.Fields(0) & " - " & objRs.Fields(1) & " - " & objRs.Field
s(2)
> MsgBox objCmd.Parameters("@.rowcnt").Value
> objRs.Close
> objConn.Close
> Set objConn = Nothing
> Set objCmd = Nothing
> Set objRs = Nothing
> End Sub
>
> AMB
> "hushtech" wrote:
>