store/save results from sprocs

  • I've been trying to figure this out but either don't know where to look or how to ask The Google, but I'm trying to save/store what various delivered sprocs return.

    For example, I want to store, either in variables or directly into a table I can then manipulate, the results from sp_helpsrvrolemember. There are others but this is one that returns several columns and several rows and it is a good place to start.

    I guess I could declare three variables of varchar but how do save a result set in them? I'm just not sure how to search for this answer.

    I'm new to MSSQL and hope this question doesn't qualify for the "questions getting worse" thread.

  • You would use:

    INSERT INTO {table}

    EXECUTE {procedure}

    It is documented in books online under the INSERT INTO topic. The caveat for this is that the procedure cannot return more than one result set, or the insert will fail.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks. I didn't think to look there.

    Regarding the "more than one result set" -- that would mean doing an INSERT INTO {table} EXECUTE SP_MONITOR would fail (as an example)?

    I'll fiddle with this and see what I can figure out before I ask any other questions.

    Thanks a lot! That was a big help.

  • Yes - executing something like that would fail due to the multiple result sets.

    One of the things you can do is view the code used in those procedures and extract the code you want to use yourself.

    Oh, and another thing that will cause this to fail is if the procedure you are executing using an INSERT INTO ... EXECUTE itself. You can't nest them.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • It also looks like you can't insert another column for each row returned by the INSERT INTO ... EXEC.

    For example, this gives me an error:

    CREATE TABLE #mytemptable

    (

    myserverrole VARCHAR(50) DEFAULT @@SERVERNAME

    ,mymembername VARCHAR(Max)

    ,mymembersid VARBINARY(Max)

    ,mycheckdatetime datetime

    )

    insert into #mytemptable

    exec sp_helpsrvrolemember @srvrolename = 'sysadmin',

    @mycheckdatetime = getdate

    go

    select * from #mytemptable

    (I realize the code is pretty bad; c/p and messing with it until I get what I want then I figure out what I did and clean it up.)

    It says, "Procedure or function sp_helpsrvrolemember has too many arguments specified." I tried getdate() but it didn't like the () and when I removed the () I realized it was because it thought I was passing parameters into sp_helpsrvrolemember.

  • oh, but I could put a default value on that datetime column...

  • NO!! (*&#(*$&#W%$&83w4&$@!$^#

    "Insert Error: Column name or number of supplied values does not match table definition."

    Argh!

  • ppcx (4/15/2009)


    oh, but I could put a default value on that datetime column...

    Yes - you can use a default, or computed column, or even add it to the select statement. 😉

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Try adding the column list to the insert statement:

    If object_id('tempdb..#mytemptable') Is Not Null

    Drop Table #mytemptable;

    CREATE TABLE #mytemptable

    (

    myserverrole VARCHAR(50) DEFAULT @@SERVERNAME

    ,mymembername VARCHAR(Max)

    ,mymembersid VARBINARY(Max)

    ,mycheckdatetime datetime default getdate()

    )

    insert into #mytemptable (myserverrole, mymembername, mymembersid)

    exec sp_helpsrvrolemember @srvrolename = 'sysadmin'

    go

    select * from #mytemptable

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey: your help has been great. Thank you.

    Everyone/Anyone: I'm still working on this problem. Currently I'm trying to do something like:

    select name, (exec master..xp_logininfo @acctname = name,@option = 'members')

    from syslogins

    where isntgroup = 1

    I want to get each group that has access inside the database (like \SQLServer2005MSSQLUser$$) and list its members. Then I take each of those members and get data that matches what's returned from syslogins and merges with syslogins into my own table. (I'm trying to get a list of every user and what server roles they have.)

    Currently I have a separate INSERT INTO... EXEC for each of my three known SQLServer2005 groups and then manipulate the data as I want. I don't want to have to modify my code if another Windows group gets added to the database. So I want it to get each Windows/NT Group (isntgroup) itself and go from there.

    The only problems with that is I don't seem to be able to run an sproc inside a select. I realize this sproc may return multiple rows in its result set. I'm trying to avoid the RBAR method and am having trouble figuring it out. Heck, at this point I'd go with RBAR to get it working.

    Any suggestions? The Google has been difficult to query and interpret.

  • How about something like this:

    Declare @exec_sql varchar(max);

    Set @exec_sql = '';

    Select @exec_sql = @exec_sql + 'Execute master..xp_logininfo @acctname = ''' + sp.[name] + ''', @option = ''members'';'

    From sys.server_principals sp

    Where sp.[type] = 'G' -- Windows Group

    Execute (@exec_sql);

    If a new windows group is added, the above will include it. Is this what you are looking for?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Oooh... dynamic sql. And it is fine with the multiple lines being crammed together. I just added the "insert into #mytemptable" at the beginning and it does seem to work. And it gets the list from server_principals which is a better place to go. I may have to see if I can get the server roles from somewhere besides the backward-compatible syslogins. (I'm trying to get the same info out of MSSQL2005 and in the same format as we used to get out of MSSQL2000.)

    That's another big help Jeffrey. Thanks!

  • Select * From sys.server_principals Where type = 'R';

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply