Inserting results from SP execution to a table.
-
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
-
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
-
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