SP with two dataset to table

Question

Post reply

SP with two dataset to table

MiguelSQL

Hall of Fame

Points: 3112
More actions
July 14, 2010 at 12:00 pm

#387357

Hello everyone,
My very first post... and I've look everywhere, I have the impression it can't be done, but I don't want to give up just yet.
I have a problem running a store procedure and sending the results to a table:
Here is my script (written on MSSQL2000), however the problem might still be present on MSSQL2005 and MSSQL2008
I am trying to output a list of users on a list of databases on a server.
The problem happens when the database is using aliases.
Here is the relevant part of the code:
CREATE TABLE #tUsers
(
UserName sysname NULL,
GroupName sysname NULL,
LoginName sysname NULL,
DefDBName sysname NULL,
UserID smallint NULL,
SID smallint NULL
)
INSERT #tUsers
EXEC SP_HELPUSER
The code works great, However, this script fails when the database is using ALIASES, as the EXEC SP_HELPUSER part returns TWO results sets instead of just one, making the INSERT clause fail
Any idea on how to workaround this dilemma?
Any help would be greatly appreciated.
Thank you
Miguel

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i can't duplicate the issue;

i can certainly insert the EXEC SP_HELPUSER into a table, but what do you mean by a database alias?

do you mean where you rename a column or table with an alias, like

select

column1 AS Address

From sometable AS TheSource?

that's the only alias i can think of...SYNONYMS point at an object like a table or view, which are kind of like what i think an alias might be...

can you give a specific example i could throw into SSMS to test?

you did not post the whole code...if you are doing multiple selects in a stored procedure, did you use SET NOCOUNT ON at the beginning of the proc? that will supress results to only the last SELECT, i believe.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 2

Thank you for your attention Lowell.

I am not running really a store procedure, but I'm having issues OUTPUTING the results of SP_HELPUSER to a table. Actually, just running the code attached on the "right" database will fail.

Msg 213, Level 16, State 7, Procedure sp_helpuser, Line 258

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

I can do it in almost all my databases (over 100+ in almost a dozen servers) except a in a couple servers, where there are one or two databases that make use of aliases.

If you go to MSDN website , under RESULTS SET, it says

"The following table shows the result set when no user account is specified and aliases exist in the current database"

I'm in the same boat as you are Lowell, as I've never hear of database aliases before

Here is a pic of one of those databases and what happens when I run EXEC SP_HELPUSER

I couldn't put the pic on this forum but here is the direct link:

http://picasaweb.google.com/lh/photo/o4xbSlDjMVNC7z_eMLeepKnXTsbcZg6a2Ug1Ms4411s?feat=directlink

Thank you for your help.

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok i see what you mean now;

i think the solution is to simply sp_helptext sp_helpuser, copy the parts of the code you DO want to use, and use THAT in your own script instead.

then you can snip out/remove the other recordset and just use the part you do use.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 4

Interesting.

Really nice tip to use sp_helptext to find out how the SP is written. Thank you

I was able to isolate the piece of code that was causing the extra result set, and after commenting it out it runs just fine.

however, in order for this to work, I will have to create a new, special store procedure on these dozen or so servers in order to run my special script, which will make the solution not easily supportable.

Anyway, thanks for your help.

Miguel