my script not working when the name of a DB has -

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

my script not working when the name of a DB has -

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-05 : 00:28:41

Hi, this script uses MSforEachDB to check all the SPs in all the databases.

The script runs well except when there is a database with a dash - in ots name.
ie: When ? in the script is replaced by a database whose name contains a dash - the dash and the rest of the database name after the dash is ignored.

And I get the message for example:
Could not locate entry in sysdatabases for database 'SharePoint_AdminContent_f5c0f71f'. No entry found with that name. Make sure that the name is entered correctly.

Here is the script:
exec sp_MSforeachDB
'
use ?
select o.name
from

.dbo.sysobjects as o
where o.type=''P'''

You can run it
How can I consider the dash also as part of the database name and kind of put an escape. the escape should be dynamic because of the way my script is construceted as u can see.
Thanks a lot for your help.

Kristen
Test

22859 Posts

Posted - 2007-01-05 : 00:44:55

Put [ ] around the database name

Kristen

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-05 : 08:55:08

where do I put that in my script. Even if i remeove o.name in my script i still get the error
any help Por Favor (pls):)

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-05 : 09:16:25

actually try just this little script. it doesn t work if any - in the database name:
exec sp_MSforeachDB
'
use ?
select o.name,
''?'' as dbName
from ?.sys.objects as o
where o.type=''P'''
Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-05 : 10:00:24

Square brackets around ? like this:

exec sp_MSforeachDB
'use [ ?]
select o.name, 
''?'' as dbName 
from [ ?].sys.objects as o
where o.type=''P'''

Note: Ignore spaces inside []

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-05 : 14:53:41

works perfectly
gracias

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources