February 13, 2009 at 2:08 am
Hi Friends,
When i execute following statement through query analyser it works fine.
its gives out put for all databases. but when i run this through Excel sheet
from Data>>New database query
it runs only in master and gives out put only for this.
how can i import data from all databases in excel using this query.
Query is =
exec sp_msforeachdb "use [?]; print'?' exec sp_helpdb '?'"
Please do needful.
Regards,
Nikhil
February 13, 2009 at 7:23 am
Nikhil (2/13/2009)
Hi Friends,When i execute following statement through query analyser it works fine.
its gives out put for all databases. but when i run this through Excel sheet
from Data>>New database query
it runs only in master and gives out put only for this.
how can i import data from all databases in excel using this query.
Query is =
exec sp_msforeachdb "use [?]; print'?' exec sp_helpdb '?'"
Please do needful.
Please do needful? Not sure what you mean by that.
Are you saying you're actually trying to run this proc from an open Excel sheet or are you using this in Integration Services?
This information would help us help you resolve your issue.
February 13, 2009 at 7:40 am
My guess is that this is due to the way the sp_MSForeachdb actually works in that it runs the statement in the database then moves on with each statement execution being a direct output. So, to do what you are suggesting you would have to build that into a stored procedure taking the output from each run and inserting that into a temp table. Final statement in the procedure would be the select * from #temptable.
I believe this will get you what you need.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 13, 2009 at 9:50 pm
hi,
i m doing things as follows...
1 open MS excel sheet
2 Go to Data > new database query > selecting edit query > adding my query over there
3 Save sheet.
By doing this next time when i open my sheet it asks me for server name and sa password
when i fill it
it gives me output.
this is the way how it works.
but i m getting output diff from wht query gives i query analyser
February 13, 2009 at 10:08 pm
Please find attachments to see outputs
February 13, 2009 at 10:31 pm
DavidB (2/13/2009)
My guess is that this is due to the way the sp_MSForeachdb actually works in that it runs the statement in the database then moves on with each statement execution being a direct output. So, to do what you are suggesting you would have to build that into a stored procedure taking the output from each run and inserting that into a temp table. Final statement in the procedure would be the select * from #temptable.I believe this will get you what you need.
Thanks a lot Devid it worked
February 15, 2009 at 4:56 am
DavidB (2/13/2009)
My guess is that this is due to the way the sp_MSForeachdb actually works in that it runs the statement in the database then moves on with each statement execution being a direct output. So, to do what you are suggesting you would have to build that into a stored procedure taking the output from each run and inserting that into a temp table. Final statement in the procedure would be the select * from #temptable.I believe this will get you what you need.
David can you plz tell me why this happens??
February 15, 2009 at 9:43 pm
Hi Nikhil,
You have to first understand first sp_msforeachdb works. It initially executes for master database and sends the output and goes for next database and executes the query and send's the output. Thats the reason you see lot of results when you execute in query analyzer. But in Excel it does not allow you to switch over to other database except to the database you had connected and by default whenever you get connected to sql server your default database will be master. So you are getting the output of only master. Thats the reason you are asked to collect the output from all databases into a single temporary table and execute a select query on that temporary table.
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
February 15, 2009 at 9:48 pm
chandramohann (2/15/2009)
Hi Nikhil,You have to first understand first sp_msforeachdb works. It initially executes for master database and sends the output and goes for next database and executes the query and send's the output. Thats the reason you see lot of results when you execute in query analyzer. But in Excel it does not allow you to switch over to other database except to the database you had connected and by default whenever you get connected to sql server your default database will be master. So you are getting the output of only master. Thats the reason you are asked to collect the output from all databases into a single temporary table and execute a select query on that temporary table.
Thanks
Chandra Mohan N
Thanks chandramohan!!!!!
February 16, 2009 at 5:32 am
You can accomplish this output in two ways. The first way, as earlier suggested, is to write a stored procedure and put everything in a temp table, then select from the temp table.
The other way is to create an SSIS package with Excel sheet destination(s) and pull the data from your different sources that way and put them in the same sheet.
The only real reason to do the SSIS package and all the coding involved, though, is when you're running your proc (which uses sp_MSforEachDB) on multiple servers.
February 16, 2009 at 5:57 am
Brandie Tarvin (2/16/2009)
You can accomplish this output in two ways. The first way, as earlier suggested, is to write a stored procedure and put everything in a temp table, then select from the temp table.The other way is to create an SSIS package with Excel sheet destination(s) and pull the data from your different sources that way and put them in the same sheet.
The only real reason to do the SSIS package and all the coding involved, though, is when you're running your proc (which uses sp_MSforEachDB) on multiple servers.
Thanks but i got solution by putting it in temp table
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply