Professional Web Applications Themes

INSERT...EXEC with multiple results sets - Microsoft SQL / MS SQL Server

This may be a silly question, but I'm pretty much a newbie, and I'm stumped. I'm writing a stored procedure which makes use of one of the system stored procedures (sp_helplogins). That system stored procedure returns multiple results sets--two, specifically, and I need to be able to access just one of them (the first). Specifically, I want to dump the contents of that first result set into a temporary table for further selecting. The following code (which only refers to columns from the first results set) doesn't work: CREATE TABLE #logins (LoginName sysname, SID varbinary(85), DefDBName sysname, DefLangName sysname, AUser ...

  1. #1

    Default INSERT...EXEC with multiple results sets

    This may be a silly question, but I'm pretty much a newbie, and I'm stumped.

    I'm writing a stored procedure which makes use of one of the system stored
    procedures (sp_helplogins). That system stored procedure returns multiple
    results sets--two, specifically, and I need to be able to access just one of
    them (the first).

    Specifically, I want to dump the contents of that first result set into a
    temporary table for further selecting.

    The following code (which only refers to columns from the first results set)
    doesn't work:

    CREATE TABLE #logins (LoginName sysname, SID varbinary(85), DefDBName
    sysname, DefLangName sysname, AUser char(5), ARemotechar(7))
    INSERT #logins(LoginName, SID, DefDBName, DefLangName, AUser, ARemote)
    EXEC sp_helplogins

    Any suggestions?...




    Dennis Guest

  2. #2

    Default Re: INSERT...EXEC with multiple results sets

    Take a look at the code for this sp and extract what's needed.

    exec master..sp_helptext 'sp_helplogins'

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Dennis S." <org> wrote in message
    news:phx.gbl... 
    stumped. 
    of 
    set) 


    oj Guest

  3. #3

    Default Re: INSERT...EXEC with multiple results sets

    You cannot extract a single resultset from an EXEC. When you have multiple
    resultsets, each result set must be compatible with the columns in the table
    or in column list.

    One alternative is to use sp_helptext on the procedure to see the query
    which returns the initial resultset & use it with a INSERT ...SELECT
    statement. However, with sp_helplogins this seems like a tall order.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  4. #4

    Default Thanks everyone

    In the interest of maintaining compatibility with future versions of SQL
    Server, I was trying to avoid writing my own SQL (or copying it out of the
    system stored procedure), but you've answered my question, and the answer
    is: tough luck in this case!

    Thanks for your responses.

    "Dennis S." <org> wrote in message
    news:phx.gbl... 
    stumped. 
    of 
    set) 


    Dennis Guest

  5. #5

    Default Re: INSERT...EXEC with multiple results sets

    Yes.

    Open Enterprise manager, expand master/stored procedures Right
    Click/Properties.
    Copy the whole SP text to the clipboard and paste in QA.
    Change the name to sp_myhelplogins and comment out the 2nd SELECT statement.

    press F5, There you go.

    HTH

    "Dennis S." <org> wrote in message
    news:phx.gbl... 
    stumped. 
    of 
    set) 


    Amy Guest

Similar Threads

  1. Question Support for multiple char sets?
    By daggar3 in forum Brainstorming Area
    Replies: 0
    Last Post: September 14th, 08:21 AM
  2. Support for multiple char sets?
    By Hemant Shah in forum IBM DB2
    Replies: 5
    Last Post: September 14th, 07:38 AM
  3. DataAdapter and multiple result sets
    By Michael C# in forum ASP.NET Web Services
    Replies: 2
    Last Post: May 10th, 06:05 PM
  4. Replies: 4
    Last Post: February 26th, 06:35 PM
  5. ADO.NET with Instead-Of-Insert Triggers, sets NULLs
    By Brian Cook in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 3rd, 05:07 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