none
current provider does not support returning multiple recordsets from a single execution

    Question

  • 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-ccdev\sql2005;Database=catcheck;Integrated Security=SSPI"

      stConn = "Provider=SQLOLEDB;Server=crivm-ccdev\sql2005;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

    Thursday, June 14, 2007 6:24 PM

Answers

All replies

  • Can you provide a simplified version of the stored procedure so I can try it out?
    Thursday, June 14, 2007 9:19 PM
    Moderator
  • you should be able to test using something like

     

    SELECT 'TEST1' ; SELECT 'TEST2'

     

    which should give you 2 diff result sets.  so given this example when you do rs.nextresult you should get the same error.

    Friday, June 15, 2007 1:21 PM
  • You should not disconnect before calling NextRecordset (it is not nextresultset btw.)
    See e.g.  http://support.microsoft.com/kb/313861
    Tuesday, June 19, 2007 4:42 AM
    Moderator