December 4, 2008 at 8:39 am
I get the following error message when I try and loop through all databases to perform a query using sp_MSforeachdb.
'Could not locate entry in sysdatabases for database 'OrtusSearch'. No entry found with that name. Make sure that the name is entered correctly.'
However where I run the below code I get no results.
select * from sysdatabases
where name = 'ortussearch'
I have an entry in sysdatabases for an ortussearch.com database. I don`t understand why I am getting the above situation. Any thoughts ?
December 4, 2008 at 8:43 am
Hi Simon
It's a long shot, but could it be case sensitivity? Try 'ortussearch' in your sp_MSforeachdb query.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 4, 2008 at 8:46 am
What I don`t get is that I am not using OrtusSearch in the ? criteria in sp_MSforeachdb. Its just looping through and hitting the error. Very odd.
December 4, 2008 at 9:04 am
What do you get when you run this:
select name from sysdatabases
where UPPER(name) LIKE 'ORTUSSEARCH%'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 4, 2008 at 9:08 am
I get OrtusSearch.com
As the database is called. Really confused now !
December 4, 2008 at 9:13 am
What's sp_MSforeachdb running?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 4, 2008 at 9:14 am
Try within your sp_msforeachdb puttin [ ] around the ?. This will then ensure that you capture non standard naming convention databases.
December 4, 2008 at 9:16 am
I know exactly why you suggested that and I`ve already tried it to try and counteract the fullstop. Doesn`t make a difference though.
It looks like the initial error message is failing on a database that doesn`t exist. Vexing !
December 4, 2008 at 9:19 am
What's the code you're trying to run inside sp_MSforeachdb?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2008 at 9:23 am
Code running is as such :
DECLARE @command varchar(1000)
SELECT @command = 'IF ''[?]'' LIKE (''JM%'')
BEGIN USE ? select prefix, dataversion from system END'
EXEC sp_MSforeachdb @command
Returns results for 30 database and then error message as stated in first post.
December 4, 2008 at 9:26 am
Try [ ] around the BEGIN USE ?
thusly
DECLARE @command varchar(1000)
SELECT @command = 'IF ''[?]'' LIKE (''JM%'')
BEGIN USE [?] select prefix, dataversion from system END'
EXEC sp_MSforeachdb @command
December 4, 2008 at 9:31 am
Bingo
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' LIKE (''JM%'')
BEGIN USE [?] select prefix, dataversion from system END'
EXEC sp_MSforeachdb @command
Moving the square brackets worked fine.
If anyone can tidy everything up and work out why it the error message at the beginning references a database name that doesn`t exists then that would be an added bonus.
Thanks everyone.
December 4, 2008 at 9:31 am
and no [] inside the IF, because you're trying to do a string match there, not refer to the DB itself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2008 at 9:47 am
Simon Smith (12/4/2008)
If anyone can tidy everything up and work out why it the error message at the beginning references a database name that doesn`t exists then that would be an added bonus.
A . is not allowed within an identifier. It's used with 2, 3 or 4 part naming to denote the portions of the name. I'm guessing that SQL ignored everything after the . and took just the portion before as the DB name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2008 at 9:49 am
Groovy. And now I have closure !
Thanks all again.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply