Professional Web Applications Themes

Suppressing second recordset from stored procedure - ASP Database

Hi, I've ran into a small dilemma. I use ADO to get Recordset from Stored Procedure. We use generic function written in VBScript to retrieve last Recordset returned by it. One of our stored procedures calls another stored procedure, which also returns a recordset. That causes a problem, because now ADO returns 2 recordsets instead of 1, and our generic data access function fails. I'd like to find a way of suppressing all recordsets except one inside stored procedure. I can't change VBscriptc function, because it's used by more than one application. Just a thought, I've tried SET NOCOUNT, and ...

  1. #1

    Default Suppressing second recordset from stored procedure

    Hi,

    I've ran into a small dilemma.
    I use ADO to get Recordset from Stored Procedure.
    We use generic function written in VBScript to retrieve last Recordset
    returned by it.
    One of our stored procedures calls another stored procedure, which also
    returns a recordset.
    That causes a problem, because now ADO returns 2 recordsets instead of 1,
    and our generic data access function fails.

    I'd like to find a way of suppressing all recordsets except one inside
    stored procedure.
    I can't change VBscriptc function, because it's used by more than one
    application.

    Just a thought, I've tried SET NOCOUNT, and it doesn't work in the above
    situation.

    Thanks
    Alex


    A_X_L_V Guest

  2. #2

    Default Re: Suppressing second recordset from stored procedure

    A_X_L_V wrote:
    > Hi,
    >
    > I've ran into a small dilemma.
    > I use ADO to get Recordset from Stored Procedure.
    > We use generic function written in VBScript to retrieve last Recordset
    > returned by it.
    > One of our stored procedures calls another stored procedure, which
    > also returns a recordset.
    > That causes a problem, because now ADO returns 2 recordsets instead
    > of 1, and our generic data access function fails.
    >
    > I'd like to find a way of suppressing all recordsets except one inside
    > stored procedure.
    > I can't change VBscriptc function, because it's used by more than one
    > application.
    >
    > Just a thought, I've tried SET NOCOUNT, and it doesn't work in the
    > above situation.
    >
    > Thanks
    > Alex
    You cannot "suppress" recordsets returned by a procedure. You COULD revise
    the vbscript code so that the "wrong" recordset is ignored, but you say you
    want to avoid this, leaving only one alternative: you will have to rewrite
    the procedure so it only returns a single resultset

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows Guest

  3. #3

    Default Re: Suppressing second recordset from stored procedure

    Bob, I can't rewrite the procedure...I have to call the other stored
    procedure inside mine,
    because it performs business logic. That second proc returns a recordset,
    which I don't use
    inside my main stored proc other than checking for status code. My main
    stored procedure also
    has to return a recordset, which makes it 2.

    So what you are saying is that I can't tell SQL Server "I just need the last
    recordset", similar to what SET NOCOUNT does?

    Of course, I can, for instance, split my current proc into 2, call proc_1,
    then that other proc I am calling in the middle,
    and then proc_2, but this could be a bit of a maintenance problem in the
    future

    Thanks


    "Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    news:%23dM8FY%23oDHA.3688TK2MSFTNGP11.phx.gbl...
    > A_X_L_V wrote:
    > > Hi,
    > >
    > > I've ran into a small dilemma.
    > > I use ADO to get Recordset from Stored Procedure.
    > > We use generic function written in VBScript to retrieve last Recordset
    > > returned by it.
    > > One of our stored procedures calls another stored procedure, which
    > > also returns a recordset.
    > > That causes a problem, because now ADO returns 2 recordsets instead
    > > of 1, and our generic data access function fails.
    > >
    > > I'd like to find a way of suppressing all recordsets except one inside
    > > stored procedure.
    > > I can't change VBscriptc function, because it's used by more than one
    > > application.
    > >
    > > Just a thought, I've tried SET NOCOUNT, and it doesn't work in the
    > > above situation.
    > >
    > > Thanks
    > > Alex
    >
    > You cannot "suppress" recordsets returned by a procedure. You COULD revise
    > the vbscript code so that the "wrong" recordset is ignored, but you say
    you
    > want to avoid this, leaving only one alternative: you will have to rewrite
    > the procedure so it only returns a single resultset
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >

    A_X_L_V Guest

  4. #4

    Default Re: Suppressing second recordset from stored procedure

    > So what you are saying is that I can't tell SQL Server "I just need the
    last
    > recordset", similar to what SET NOCOUNT does?
    No.

    Your only option, it seems, is to let VBScript ignore (using
    rs.nextRecordset()) the set(s) you don't care about.

    Or to rewrite the stored procedure (e.g. get past whatever it is that makes
    it so that you "can't")


    Aaron Bertrand [MVP] Guest

  5. #5

    Default Re: Suppressing second recordset from stored procedure

    > I can't change VBscriptc function, because it's used by more than one
    > application.
    You can't change the VBScript, you can't change the stored procedure, and
    you can't change how SQL Server works. Something has to give here. Your
    requirements make your job impossible to do; you're going to have to go back
    to whoever is dictating that you can't change any of these things, and tell
    them that either you are allowed to change them, write similar versions of
    them for this specific use, or tell them to off.
    > Just a thought, I've tried SET NOCOUNT,
    What ever made you think that would have any impact on the resultsets that
    actually return valid data?


    Aaron Bertrand [MVP] Guest

  6. #6

    Default Re: Suppressing second recordset from stored procedure

    >-----Original Message-----
    >> So what you are saying is that I can't tell SQL
    Server "I just need the
    >last
    >> recordset", similar to what SET NOCOUNT does?
    >
    >No.
    >
    >Your only option, it seems, is to let VBScript ignore
    (using
    >rs.nextRecordset()) the set(s) you don't care about.
    >
    >Or to rewrite the stored procedure (e.g. get past
    whatever it is that makes
    >it so that you "can't")
    >
    >
    >.
    >
    I'm just winging this but:

    The only thing you can change is the SP that calls the
    second one, correct?
    You might try to capture the recordset from the secondary
    SP in a CURSOR, then deallocate the CURSOR, and then
    return your own recordset.

    Just a thought, haven't tried it.

    Keith Guest

Similar Threads

  1. Help - stored procedure not returning a recordset
    By Bill S. in forum ASP Database
    Replies: 5
    Last Post: February 13th, 10:06 AM
  2. Stored procedure from stored procedure
    By Red Valsen in forum Informix
    Replies: 3
    Last Post: October 2nd, 02:22 PM
  3. Stored Procedure vs. SQL Through Recordset.
    By craig v in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 10th, 03:15 PM
  4. need help on a stored procedure
    By Helixpoint in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:26 AM
  5. Help with Stored Procedure returning recordset
    By Ivar in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 04:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139