December 29, 2009 at 2:40 pm
I have an SSIS package that does the following, read list of server names from table A. Pass servername[var of type object] to a for each loop.For each loop iterates thru servername being passed.Within for each loop, Im executing exec sp_msforeachdb 'exec ?.dbo.sp_spaceused' for each servername using a Execute SQL Task.The Execute SQL Task has the following properties.
Result Set = Full Result Set
SQL Statment = sp_msforeachdb 'exec ?.dbo.sp_spaceused'
Parameter mapping = var name [Input] , var type [String]
Result Set= var name [Output] , var type [Object]
I would like to store the results of the execute sql task within the for each loop to a table.
How can I achive this? What is my next step ?
December 30, 2009 at 7:18 am
Anybody has advice on this?
December 30, 2009 at 9:39 am
Here are some issues you'll need to deal with:
1. sp_msforeachdb returns a result set for each database. You might be able to get around that by creating a temp table in your script task and putting the results of sp_msforeachdb into it and then selecting from the temp table.
2. sp_spaceused returns 2 result sets and you'll only get 1. This also means that my suggestion in problem 1 does not work either as the temp table doesn't work as the 2 result sets returned have a different # of columns.
From this TechNet article:
If the Execute SQL task uses the Full result set result set and the query returns multiple rowsets, the task returns only the first rowset. If this rowset generates an error, the task reports the error. If other rowsets generate errors, the task does not report them.
Basically I don't know if you can do what you need to do. I worked on my own spaceused stored procedure that returns 1 result per database. I've attached what I use. I am logging to a table in the procedure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 14, 2010 at 10:02 am
Here is how you can do it......
Truncate Table "Your Table"
Insert Into "Your Table"
Exec Master.Sys.SP_msforeachdb ' use [?]; exec sp_spaceused'
However, you will have to do as i did and modify the system sp_spaceused proc so that it doesn't seperate the results but instead does a union to ensure that the results can be insterted into a temp table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply