Post reply

Inserting results from SP execution to a table.

  • September 1, 2005 at 6:31 pm

    #165050

    Hello folks,

    I am trying to insert the results from this execution to a table:

    insert into report1

    exec master.dbo.sp_msforeachdb

    "USE [?]

    BEGIN

    SELECT '?'

    EXEC sp_helprolemember

    END"

    I get 4 results which are the: dbname, dbrole, membername and membersid.

    My table is:

    CREATE TABLE report1

    ( dbname varchar(20),

    dbrole varchar(20),

    memname varchar(20),

    memsid smallint

    )

    The error is:

    Server: Msg 213, Level 16, State 7, Line 3

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

    Thanks for your help.

    Stevie

  • September 1, 2005 at 8:14 pm

    #587076

    When you execute, 

    exec master.dbo.sp_msforeachdb 
"USE [?] 
BEGIN
SELECT '?'
EXEC sp_helprolemember 
END"

    by itself in Query Analyzer you will see that seperate resultsets are returned. You can't insert seperate resultsets into a table in one query.

    Instead you should have the insert inside the quotes so it is executed once for each execution of sp_helprolemember.

    EG:

    CREATE TABLE ##report1
( 
    dbrole sysname,
    memname sysname NULL,
    memsid varbinary(85) NULL
) 
    exec master.dbo.sp_msforeachdb 
"USE [?] 
BEGIN
insert into ##report1 (dbrole)
values (db_name())
insert into ##report1
EXEC sp_helprolemember
END" 
    select * from ##report1
    drop table ##report1

    NOTE: I've used a global temporary table in this script to test it on my system. It should work exactly the same if you use a three part object name in your create and insert statements. eg: dbname.owner.tablename

     

    --------------------
    Colt 45 - the original point and click interface

  • September 6, 2005 at 1:38 pm

    #587899

    Thanks Phil you saved me a lot of work =)

Viewing 3 posts - 1 through 2 (of 2 total)

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