two result sets from SP into one table - HELP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

two result sets from SP into one table - HELP

Author

Topic

peter_dtm
Starting Member

7 Posts

Posted - 2013-07-26 : 19:07:53

I am trying to read many remote linked servers to see whether the local dba teams have caught up with having to look after the sql instances.

I have several sp and tasks running happily using linked servers to pull information back.
Then I realised I needed to use sp_spaceused to get the information back on the various databases in use (local dba teams seem to be unable to get backup & shrink file maintenance plans working; resulting in crashes when the log files get stupidly large; so we have to check; even though we are not allowed to carry out maintenance ! )

Main problem was getting the result set from an SP into a local table --
I found http://www.sqlteam.com/article/stored-procedures-returning-data which is great except
sp_spaceused returns TWO result sets

I have this so far

declare @db table (
[dbase_name] [nvarchar](128) NOT NULL,
[dbase_size] [varchar](18) NOT NULL,
[unallocated_space] [varchar](18) NOT NULL,
[reserved] [varchar](18) NOT NULL,
[data] [varchar](18) NOT NULL,
[index_Size] [varchar](18) NOT NULL,
[unused] [varchar](18) NOT NULL)
insert @db (dbase_name, dbase_size, unallocated_space, reserved, data, index_Size, unused)
exec master.dbo.sp_spaceused

but that gives me this error
Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 113
Insert Error: Column name or number of supplied values does not match table definition.

If I break the db table at the break point of the result sets - I get a slightly different error - showing line 128

line 113 & 128 are the end of the select statements in the sp that make the two output result sets

--> How can I get the TWO output result sets of sp_spaceused into ONE or TWO tables (I can of course always join them later !)

Pete
-not everything digital is better ! -

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-01 : 06:19:25

You can always vote for this request I've made to Microsoft.
Tell all your friends to upvote this request as well.
https://connect.microsoft.com/SQLServer/feedback/details/470881/allow-more-than-one-resultset-to-be-stored-with-insert-into-exec-syntax

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources