Post reply

Error message looking at sysdatabases

  • December 4, 2008 at 8:39 am

    #127314

    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

    #907644

    Hi Simon

    It's a long shot, but could it be case sensitivity? Try 'ortussearch' in your sp_MSforeachdb query.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

    #907648

    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

    #907674

    What do you get when you run this:

    select name from sysdatabases

    where UPPER(name) LIKE 'ORTUSSEARCH%'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

    #907679

    I get OrtusSearch.com

    As the database is called. Really confused now !

  • December 4, 2008 at 9:13 am

    #907692

    What's sp_MSforeachdb running?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

    #907696

    Try within your sp_msforeachdb puttin [ ] around the ?. This will then ensure that you capture non standard naming convention databases.


    Shamless self promotion - read my blog http://sirsql.net

  • December 4, 2008 at 9:16 am

    #907698

    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

    #907703

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 4, 2008 at 9:23 am

    #907711

    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

    #907715

    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


    Shamless self promotion - read my blog http://sirsql.net

  • December 4, 2008 at 9:31 am

    #907722

    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

    #907723

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 4, 2008 at 9:47 am

    #907741

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 4, 2008 at 9:49 am

    #907743

    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