Post reply

Unable to update Query Processor Statistics

  • February 12, 2010 at 7:41 am

    #100101

    During my nightly database maintenance routines I am receiving the following error message when the Query Processor Statistics are being updated:

    [Microsoft SQL-DMO (ODBC SQLState: 42S02)] Error 2706: [Microsoft][ODBC SQL Server Driver][SQL Server]Table '(null).<tablename>' does not exist.

    This is occurring on several databases and the tables in question (insert any tablename in <tablename>) do indeed not exist, but of course I don't know how a table could be in the (null) schema anyway. I have run DBCC CHECKDB and I have rebuilt all the indexes etc. all with no failures. My research on the web has suggested that this may have something to do with copying the data from one server to another using DTS which I do. However, at least one of the databases in question is in a database that I have never copied data to or restored or anything of that nature.

    Any clues?

  • February 12, 2010 at 8:08 am

    #1118057

    This is typically a result of direct updates to the system tables, dropping users that own the tables. Does the following produce any results?

    SELECT name FROM sysobjects WHERE uid NOT IN (select uid FROM sysusers) AND xtype = 'U'

    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

  • February 12, 2010 at 8:16 am

    #1118063

    Yep, you got me! Good catch. At least I know what I have done wrong and how to fix it. Thanks.

  • February 12, 2010 at 8:54 am

    #1118080

    Do you know how to fix this, or do you need a hand?

    Any idea who would have gone modifying the system tables?

    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

  • February 12, 2010 at 9:06 am

    #1118086

    Ummm... yeah *looks around sheepishly* Me.

  • February 12, 2010 at 9:08 am

    #1118091

    Oh... I should read your whole post. I was thinking I could re-assign the tables in question to another uid in sysobjects, or simply remove them. The latter I would think may have ramifications.

  • February 12, 2010 at 12:08 pm

    #1118216

    Mike Tutor (2/12/2010)

    I was thinking I could re-assign the tables in question to another uid in sysobjects,

    You can. dbo (uid 1) is best. You know how or would you like assistance?

    or simply remove them.

    Only if you want a corrupt database. That would result in allocated pages that don't have a matching object, rows in syscolumns that don't have a matching row in sysobjects and a variety of other really nasty problems.

    Please note that CheckDB does not run CheckCatalog on SQL 2000. That's why CheckDB's finishing without errors with missing records in the system tables. Upgrade to SQL 2005 and (if the upgrade even succeeds) you'll be in a very bad position.

    I would strongly suggest that once you get this fixed you run DBCC CheckCatalog to make sure there aren't more problems waiting to be found.

    Why, why, why were you directly updating the system tables?

    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

  • February 12, 2010 at 12:20 pm

    #1118219

    Thanks so much for your help. And yes, I am in the process of upgrading my SQL 2000 databases to 2008. Fortunately all of these databases except one are development DBs that I was copying over from prod. It was my own doing and I have always avoided messing with system tables in the past... but I got sloppy trying to clean up some ancient straggling users that no longer work here. No excuse for sloppiness. Live and learn.

    Anyway, I did move the ownership of the objects to uid 1 (dbo) and that seems to have fixed the problem.

    You ask 'Why, Why, Why would update system tables?' , because I Can, Can, Can. 😛 (and I'm stupid, stupid, supid)

    Mike

  • February 12, 2010 at 12:34 pm

    #1118231

    Does DBCC CheckCatalog come back without errors?

    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

  • February 12, 2010 at 12:37 pm

    #1118234

    Yes. Whew! 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply