SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 two result sets from SP into one table - HELP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peter_dtm
Starting Member

United Kingdom
7 Posts

Posted - 07/26/2013 :  19:07:53  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 08/01/2013 :  06:19:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000