Current Provider Does Not Support Returning Multiple Recordsets From A Single Execution

Hi all -



I know this is prolly an old one but I would certainly appreciate some assistance =)



environment:

SERVER (IIS6, .NET2.0, SQL2005)

CLIENT (WIN2000,IE6,VBScript)



I have an aspx that is invoking ado on the clientside. I read somewhere that javascript does not support connecting to SQL clientside. I may be wrong. In any case I am using VBScript on the client.



I am using something like this to invoke the script




Code Snippet

<a href="#" onclick="doReport()">Click Here</< FONT>a>



doReport() looks something like this ...






Code Snippet

' ========================================================

sub doReport()



Dim stSQL, rs, oWord, oDoc, oRng

Dim stCurrentSection, stTemplatePath, dtNow, dtSOR, dtLastDataPoint

Dim iLastDataPoint



set oWord = CreateObject("Word.Application")

stTemplatePath = "http://crivm-ccdev/ccprocharts/supportfiles/CCWordReport.doc"

set oDoc = oWord.Documents.Open(stTemplatePath)

oWord.visible = true









stCurrentSection = "a"



loadDebug

'loadLive







openConnection()

' == THIS DB CALL GETS 2 RESULT SETS

'set rs = getrsCustomerInfoReport(mstUnits, left(mstCycles,3), mstAppName)









set rs = getReadOnlyRS("sCC_GetCustInfo_Report '" & mstUnits & "','" & left(mstCycles,3) & "','" & mstAppName & "'")



' == POPULATE VARS FROM 1st RESULT SET

dtLastDataPoint = rs(0)

iLastDataPoint = rs(1)



set rs = rs.NextRecordset ' <== THIS IS WHERE IS ERROR IS



msgbox rs(0)

end sub









here is my openconnection sub ... oConn is global




Code Snippet

<script type="text/vbscript" language="vbscript">



'== Cursor Location

CONST adUseClient = 3

CONST adUseServer = 2

' == Cursor Type

CONST adOpenStatic = 3

CONST adOpenForwardOnly = 0

CONST adOpenDynamic = 2

' == Lock Type

CONST adLockReadOnly = 1

CONST adLockOptimistic = 3

CONST adLockPessimistic = 2



' ========================================================



sub openConnection()



Dim stConn

set oConn = CreateObject("ADODB.Connection")





'stConn = "DRIVER={sql server};Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"

stConn = "Provider=SQLOLEDB;Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"

'msgbox oConn.state & vbcrlf & stConn1

oConn.open(stConn)

'msgbox oConn.state

ApplySecurity



end sub

' ============================================================

Sub ApplySecurity

'gbhasDBAccess is a global variable on both client and server sides.

'Server side is set in seccheck.asp, which should be at the top of every page

'Client side is set in ApplySecurity() of ConnectServer.asp

Dim appRole



appRole = "appWriters,(tsvc123)"



oConn.Execute "sp_setapprole '" & split(appRole,",")(0) & "','" & split(appRole,",")(1) & "'"



End Sub



</< FONT></script>



Before we moved to SQL2005 I always used this




Code Snippet

' ========================================================

Function getReadOnlyMultRS(strSQL)

set rs = CreateObject("ADODB.Recordset")

rs.CursorLocation = adUseClient

rs.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly

'Disconnect the Recordset

Set rs.ActiveConnection = Nothing

'Return the Recordset

Set getReadOnlyMultRS = rs

End Function



and this always allowed me to use set rs = rs.nextresultset

but now that we switched to SQL2005 it does not seem to work. I have verified in sql studio that this sp does indeed return 2 resultsets