sp_MSforEachDb gives weard output

  • 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

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • 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

  • Please find attachments to see outputs

  • 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

  • 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??

  • 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]

  • 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!!!!!

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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