Move to next RS in stored proc w/ many SELECTs

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)

Move to next RS in stored proc w/ many SELECTs

Author

Topic

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-07-16 : 13:50:25

Hello. I have a stored procedure that has multiple SELECT statements in it. I have tested it in Query Analyzer and it looks good. I'm calling the data through ASP and I'm missing the correct syntax to move to the next recordset. After I'm done with the first SELECT in the stored procedure, I use the following code to go to the next SELECT.

Set RS1 = RS1.NextRecordset()

This is the code I normally use when I build my SQL strings in ASP, but I'm getting the following error with my stored proc.

"Current provider does not support returning multiple recordsets from a single execution."

I'm guessing I'm missing something when I first set up the object. Here's the code I use to call the stored procedure.

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = THISONE
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_MultipleSelectProc"

'Establish parameters
objCmd.Parameters.Append = objCmd.CreateParameter("@StartDate",adDate,adParamInput, ,vStartDate)
objCmd.Parameters.Append = objCmd.CreateParameter("@EndDate",adDate,adParamInput, ,vEndDate)

'Build recordset
Set RS1 = Server.CreateObject("ADODB.Recordset")
RS1.CursorLocation=adUseClient
RS1.Open objCmd,,adOpenForwardOnly,adLockReadOnly
Set RS1.activeConnection = nothing
Set objCmd = Nothing

I would appreciate it if anyone could point out what I'm doing wrong or missing. Thanks for the help.

Huligan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-16 : 15:13:02

what provider are you using? what does your connect string look like (hide the password if it's in there, of course!)

- Jeff

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-07-16 : 15:53:57

Here's my recordset wrapped inside the connection.

-----

'Build connection
Set THISONE = server.CreateObject ("ADODB.Connection")
THISONE.Open Application("THISONE_ConnectionString")

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = THISONE
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_MutlipleSelects"

'Establish parameters
objCmd.Parameters.Append = objCmd.CreateParameter("@StartDate",adDate,adParamInput, ,vStartDate)
objCmd.Parameters.Append = objCmd.CreateParameter("@EndDate",adDate,adParamInput, ,vEndDate)

'Build recordset
Set RS1 = Server.CreateObject("ADODB.Recordset")
RS1.CursorLocation=adUseClient
RS1.Open objCmd,,adOpenForwardOnly,adLockReadOnly
Set RS1.activeConnection = nothing
Set objCmd = Nothing

'Close connection
THISONE.Close()
Set THISONE = Nothing

------

Here is the connection string that THISONE refers to. I'm using a global.asa in Visual InterDev.

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==Visual InterDev Generated - startspan==
'--Project Data Connection
Application("THISONE_ConnectionString") = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=SQLDatabase;Data Source=MIKES-LAPTOP;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MIKES-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;User Id=UserID;"
Application("THISONE_ConnectionTimeout") = 15
Application("THISONE_CommandTimeout") = 30
Application("THISONE_CursorLocation") = 3
Application("THISONE_RuntimeUserName") = "UserID"
Application("THISONE_RuntimePassword") = "Password"
'-- Project Data Environment
'Set DE = Server.CreateObject("DERuntime.DERuntime")
'Application("DE") = DE.Load(Server.MapPath("Global.ASA"), "_private/DataEnvironment/DataEnvironment.asa")
'==Visual InterDev Generated - endspan==
End Sub
</SCRIPT>

-----

Thanks for the help.

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 03:31:31

what's with the multiple "User Id"? Two in the THISONE_ConnectionString and another (with password) in THISONE_RuntimeUserName

Other than that I can't see anything particularly amiss, but you may get some connection ideas at:
http://www.able-consulting.com/ADO_Conn.htm

Kristen

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-07-17 : 09:06:04

I'm guessing here, but I think my connection is fine. Do you think I'm just missing some syntax that states my stored proc will have 2 recordsets? Does anyone have some ASP code that calls a stored procedure with multiple SELECTs that I can look at? Thanks.

Huligan

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 12:41:27

[code]
<%
Option Explicit

' Arcaysis result testing tool
' Filename : resultview.asp
' Author: ASW/KBM
' Created: 23-Mar-2004
' Prompts the user for a SQL argument and returns
' all results including those from multiple recordsets.
' Also displays ADO errors in a friendly format.

' **** Alter this for your target database. ****

Const DATABASE_CONNECTION = "Provider=SQLOLEDB;Data Source=MyServername;User Id=MyUserID;Password=MyPassword;Initial Catalog=MyDatabaseName;"

Dim mode 'Tracks the state of this page

Const FORM=0
Const DISPLAY=1
Const ERROR=2

Dim rs 'ADO recordset object
Dim strSQL 'SQL container
Dim conn 'ADO connection object
Dim count 'Used for identifying which recordset we are using

Function ExecuteSQL(sql)

On Error Resume Next
'Opens database connection
conn.Open DATABASE_CONNECTION

If conn.Errors.Count > 0 Then
mode=ERROR
On Error Goto 0
Exit Function
End If

Set rs = conn.Execute(sql)

If conn.Errors.Count > 0 Then
Response.Write "Error while opening recordset:<br>"
mode=ERROR
On Error Goto 0
Exit Function
End If

'Change the way this page will be displayed
mode=DISPLAY

executeSQL = rs

End Function

'Errors are formatted for display
Function ProcessErrors()
Dim ErrorItem

ProcessErrors="ADO Error collection:<br>"
For Each ErrorItem in conn.Errors
ProcessErrors = ProcessErrors & "Description:" & ErrorItem.Description &"<br>"
ProcessErrors = ProcessErrors & "Error Id" & ErrorItem.NativeError &"<br>"
Next
End Function

Function DisplayRecordset(outrs, rsnumber)
Dim fld

Response.Write "<h2>Recordset " & rsnumber & "</h2>"
Response.Write "<table class='resultstable'>"

'Header row
Response.Write "<tr>"
For Each fld In outrs.Fields
Response.Write "<th>" & fld.name & "<br><small>[" & GetADOType(fld.type) & "(" & fld.precision & ")]<small></th>"
Next
Response.Write "</tr>"

'Each row
Do Until outrs.EOF
'Each column
Response.Write "<tr>"
For Each fld In outrs.Fields
If IsNull(fld.value) Then
Response.Write "<td class='nullcell'>NULL</td>"
Else
Response.Write "<td>" & fld.value & "</td>"
End If
Next
Response.Write "</tr>"

rs.MoveNext
Loop

Response.Write "</table>"
End Function

Function GetADOType(adoType)
Select Case adoType
Case 3
GetADOType="Int"
Case 135
GetADOType="DateTime"
Case 200
GetADOType="VarChar"
Case Else
GetADOType=adoType

End Select
End Function
' **************** Find mode and action **********************
strSQL=Request.Form("testsql")

If strSQL <> "" Then
'instantiate ADO object
Set conn = CreateObject("ADODB.Connection")

'Get the results from the database
ExecuteSQL strSQL
Else
mode=FORM
End If
%>
<html>
<head>
<title>arcaysis Result View Tool</title>
<style>
BODY {
font-family: arial;
font-size: 10pt;
}

H1 {
font-size: 14pt;
}

H2 {
font-size: 13pt;
}
.resultstable {
border: solid black 1px;
}

TH {
background-color: navy;
color: white;
vertical-align: top;
}
TD {
border: solid black 1px;
vertical-align: top;
}
TD.nullcell {
background-color: cornsilk;
color: darkred;
border: solid black 1px;
vertical-align: top;
}
</style>
</head>
<body>

<%
If mode=ERROR Then

Response.Write ProcessErrors

ElseIf mode=DISPLAY Then
Response.Write "<h1>Results for [" & strSQL & "]</h1>"
count=1

Do While Not (rs Is Nothing)

DisplayRecordset rs, count

count=count+1

'Get the next recordset if available
Set rs = rs.NextRecordSet
Loop

ElseIf mode=FORM Then
%>

<h1>Result Viewer</h1>
<form name="frm" action="resultview.asp" method="POST">

<textarea style="width:600px;height:300px" name="testsql">EXECUTE MySProc @Param1=123</textarea><br>
<input type="submit" value="Execute">

</form>
<script>document.frm.testsql.focus();</script>
<%

End If
%>
</body>
</html>
[/code]
Kristen

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-07-18 : 21:46:50

I figured it out. I found the error reported on 4GuysFromRolla. "Unfortunately, you can't use the NextRecordset method with an ADO disconnected Recordset." I can use the multiple recordset with a stored procedure if I was using an ODBC connection. Thanks for the help and example code.

Huligan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources