server can t find SP sp_MSforeachDB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

server can t find SP sp_MSforeachDB

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-06 : 10:57:12

Hi if I do exec sp_MSforeachDB '......etc'
and i get SP sp_MSforeachDB not found how do i reference it in order for the server to find it.
Because when we check in master we find the SP there but when we run the exec sp_MSforeachDB ...
we get SP not found
any help pls

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-01-06 : 11:37:36

EXEC Master.dbo.sp_MSForEachDB .....

--Jeff Moden

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-06 : 12:19:35

they did that, it worked on 2 instances but not on another 3 instances
any explanantion why EXEC Master.dbo.sp_MSForEachDB ..... might not work.
is the syntax case sensitive?
Thanks a LOT

Kristen
Test

22859 Posts

Posted - 2007-01-06 : 12:42:51

"is the syntax case sensitive"

It will be if the database collation is case sensitive

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 12:52:11

on a case-sensitive collation, you need to use the same captitalization of the sproc as is in master. Try this:

exec sp_MSforeachdb 'print ''?'''

www.elsasoft.org

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-01-06 : 13:36:01

quote:
Originally posted by rtutus

they did that, it worked on 2 instances but not on another 3 instances
any explanantion why EXEC Master.dbo.sp_MSForEachDB ..... might not work.
is the syntax case sensitive?
Thanks a LOT

Guess I don't understand... if you got it to work in 2 instances, why don't you compare those against the ones that didn't and just figure it out?

--Jeff Moden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 14:11:56

It's a good bet that the 2 instances it worked on were case-insensitive, and the ones where it failed were case-sensitive.

www.elsasoft.org

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-06 : 14:56:36

hell... i completly understand the guy... i had the same problem a while ago:
http://weblogs.sqlteam.com/mladenp/archive/2006/12/07/44176.aspx

stupid case sensitivity... i'd just prohibit the worldwide use of uppercase.
If you want to put 2 words together use a _.
this_is_what_i_mean_see

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-01-06 : 16:10:35

YesISeeWhatYouMean!!

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-06 : 16:30:46

[quoteGuess I don't understand... if you got it to work in 2 instances, why don't you compare those against the ones that didn't and just figure it out?

--Jeff Moden
[/quote]
compare what against the 2 other working instances. it s all the same script that needs to run on all the instances. can explain what u mean by the comparison idea pls
Thanks

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-06 : 16:33:12

quote:
stupid case sensitivity... i'd just prohibit the worldwide use of uppercase.
If you want to put 2 words together use a _.
this_is_what_i_mean_see

dear spirit, i understand what u mean by case sensitivity issue. i need to spell the msforeachdb the exact sensitivity as in the real SP name. But what do u mean by:
If you want to put 2 words together use a _.
this_is_what_i_mean_see
is that related to the topic or it just your signature?
Thanks man

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-06 : 16:46:21

no, it's just my little rant. doesn't apply directly to your problem.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-01-06 : 16:53:05

I haven't got a Case Sensitive server handy to test it on, but this may help:


SELECT	name
FROM	master.dbo.sysobjects
WHERE	name = 'sp_MSforeachdb' COLLATE SQL_Latin1_General_CP1_CI_AS

On my database the case is as shown in the example above.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-01-06 : 16:53:18

So this is where people hang out..
The New to Sql Server and case sensitivity forum.

rockmoose

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-08 : 10:59:07

tell me if we don t find it can I just copy the Microsoft script that creates sp_MSforEachDB and create my own SP: myforEachDB in my database that I can use. I hope that won't screw up something in the server or in an other database.
Thank you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 11:07:56

You can create your own SP based on the logic in the original SP provided the nested SPs that sp_MSforeachdb calls is available to you. (in this case sp_MSforeach_Worker sp)

Beware, you are using undocumented functionality, you may face problems if it is removed in the future.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-08 : 11:41:32

What would be a good reason for case sensitivity....

It's just been a royal pain everytime I run into a box configured that way

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-08 : 11:47:26

so you're with me Brett?
This is another notch in the "world domination plan" (tm)!!!

No case sensitivity anywhere!!!!
MWAHAHAHAHAHAHAHAHAHA!!!!

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-01-08 : 12:26:28

I've come across a surprising number of Big Corporation Software Packages that use Case Sensitive collation ...

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-09 : 17:19:50

You guys were so RIGHT. It s collation problem. It has been confirmed. Thank you so much.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-09 : 18:03:05

quote:
Originally posted by Kristen

I haven't got a Case Sensitive server handy to test it on, but this may help:

SELECT name
FROM master.dbo.sysobjects
WHERE name = 'sp_MSforeachdb' COLLATE SQL_Latin1_General_CP1_CI_AS

On my database the case is as shown in the example above.

Kristen

What does the "COLLATE SQL_Latin1_General_CP1_CI_AS" part do exactly. What s its purpose pls?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources