April 15, 2009 at 1:52 pm
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.
April 15, 2009 at 2:05 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 15, 2009 at 2:24 pm
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.
April 15, 2009 at 2:48 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 15, 2009 at 2:59 pm
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.
April 15, 2009 at 3:01 pm
oh, but I could put a default value on that datetime column...
April 15, 2009 at 3:07 pm
NO!! (*&#(*$&#W%$&83w4&$@!$^#
"Insert Error: Column name or number of supplied values does not match table definition."
Argh!
April 15, 2009 at 3:08 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 15, 2009 at 3:36 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2009 at 2:42 pm
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.
April 22, 2009 at 3:29 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2009 at 3:54 pm
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!
April 22, 2009 at 4:10 pm
Select * From sys.server_principals Where type = 'R';
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply