October 24, 2001 at 12:17 pm
Has anyone been able to use this to run other things besides DBCC functions? I have been trying to use sp_MSforeachdb for stored procedures that I have written but I can not get them to work. I get the message, 'database_frag' is not a recognized function name. Do I need to make the stored procedure a function instead?
Thanks,
Wayne Robinson
October 24, 2001 at 12:49 pm
Wayne,
I use it when I run bcps, and I have no problems with it. That's running it as a stored procedure.
quote:
Has anyone been able to use this to run other things besides DBCC functions? I have been trying to use sp_MSforeachdb for stored procedures that I have written but I can not get them to work. I get the message, 'database_frag' is not a recognized function name. Do I need to make the stored procedure a function instead?Thanks,
Wayne Robinson
Patrick Birch
Quand on parle du loup, on en voit la queue
October 24, 2001 at 6:42 pm
Post your code Wayne, maybe we can help. I know it takes some juggling to get the stupid doubled tripled quadruped quotes right sometimes! I've used it to run selects against all my db's at once.
Andy
October 25, 2001 at 7:15 am
What does your command look like? I think that is where I am having problems.
October 25, 2001 at 7:57 am
sp_msforeachdb 'use ?;select db_name() as DBName, count(*) as ObjectCount from sysobjects'
Andy
October 25, 2001 at 8:06 am
I will usually slap a print in there so that I can see what I am about to run and troubleshoot any formatting issues here. An example using Andy's submission would look like;
sp_msforeachdb @command1 = "print 'use ?;select db_name() as DBName, count(*) as ObjectCount from sysobjects'"
Helps me, hope it works for you as well.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 25, 2001 at 11:37 am
Here is the text I am using run my stored procedure.
EXEC sp_MSforeachtable @command1="print '?' database_frag('?')"
October 25, 2001 at 7:30 pm
you could try it with an exec (look at sp_MSforeachtable to see what it generates).
But why use sp_MSforeachtable anyway, you will probably find it safer and easier to code yourself. I have always wondered why it was included.
Cursors never.
DTS - only when needed and never to control.
October 29, 2001 at 5:53 am
Sorry, I posted the wrong code. Here is what I use, sp_msforeachdb @command1="print '?' database_frag('?')". It says the command completes successfully but it doesn't write anything into my table. If I run the code by itself it works fine, but I have to select the database I want to use. I use the Use <database name> command when run it.
October 29, 2001 at 11:42 am
You can include use ? in your passed command.
Andy
October 29, 2001 at 3:43 pm
You can always simulate a use database command by putting the command in a string and executing via sp_executesql
i.e.
use dbname
exec sp...
should be the same as
exec dbname..sp_executesql 'exec sp...'
Cursors never.
DTS - only when needed and never to control.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply