December 11, 2011 at 7:56 am
Hi, Please advise. We have a regular rebuild/reorg index job that runs weekly against the database(s), and ran successfully before. Last night it failed, with the following error:
Cannot find the object " .dbo.myobject" because it does not exist or you do not have permissions. [SQLSTATE 42000] (Error 1088). The step failed.
The permissions can't be the issue (sysadmin), and I queried the sys.sysobjects view and it's there. I can only surmise it was locked during the attempt to reindex?
Any experience to resolve this? Most important, I would want to add a check to it so it could skip and continue with the next indexes. The job abended at this point.
ie: If exists obj Alter Indexe blah on blahtab....
TIA!
December 11, 2011 at 9:12 am
sql_jr (12/11/2011)
I would want to add a check to it so it could skip and continue with the next indexes. The job abended at this point.ie: If exists obj Alter Indexe blah on blahtab...
Good idea! go ahead; please remember to send an alert when a particular object fails the test.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 11, 2011 at 9:27 am
sql_jr (12/11/2011)
I can only surmise it was locked during the attempt to reindex?
No. Locking would just make the reindex wait, or at worst fail with a timeout.
The error 'object does not exist or you don't have permission' means just that, either the object name is wrong (watch for objects in different schemas or with names that have disallowed characters) or, for whatever reason, the user running the code does not have permission on the object.
Without seeing the real name of the object or the exact error message (unedited) that's about all that can be said.
My guess is that the object is not in the dbo schema. You'll still see if it you query sys.objects and filter on name, but an access attempt on dbo.objectname would fail
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply