Invalid object error when I run ANYTHING!
-
This has become worse and worse as the day has gone on, but suffice it to say - now I cannot do anything to any scripts in my SSMS2k5 instance. The error I get (no matter what I run) is as follows...
Msg 208, Level 16, State 1, Procedure EventCapture, Line 7Invalid object name 'dbEvt.dbo.DTA_Events_Log'.
It looks like some solution from the DTA is hung up for some reason, but I cannot find anything that would lead me to clear this out.
Any thoughts or direction to take would be much appreciated!
SQL_ME_RICH
-
What is 'dbEvt.dbo.DTA_Events_Log' ? Does it exist ? Is it an event log type table referenced in triggers and stored procedures ? Did it get deleted ?
-
Hi Homebrew - great question, and my apologies for the vagueness of my question here. The problem has been essentially this...
I have 1 database in particular (in my local 2005 Standard Edition x64 instance - default instance) that whenever I do ANY DDL (create a test table for example) that this error is getting thrown. DML/DQL - no problem, but if I need to create any new objects, this error is getting thown.
I think something got hosed in a DTA package I was running to put some Stats and a couple of new Indexes in place for a query I was optimizing, because I am able to connect to other instances where this database exists (where I made the backups from that I have locally installed in my instance for), and I have no problems creating a test table there.
I am going to restore another copy of it, and see if it is just my test copy. If this issue is still in place after I do that - then I am wondering if some invalid object has been registered in my sys somewhere that is causing all this difficulty.
Hope that might help in your coming up with ideas for me.
-
Yep - still get the same error after restoring the db from a backup. Blaaaaaaaaaaaah. I am guessing that for some reason (in my local instance only) this is causing an issue.
I am going to try one other thing, and that is to take a backup of the db from the instance I was able to run a successful DDL statement to, and see if when I restore it I still get the error. If I do - then I have to figure out how to fix this, because my environment is not working for this particular database in my local instance.
🙁
-
So - I installed SQLPrompt from Red Gate, and still no luck (didn't find the object at all on that db in question).
So - I completely uninstalled and reinstalled SQL Server 2005 Standard Edition x64 (just to eliminate that the install was the issue), and the error still happens in my local instance (which is a named instance - not a default because my default instance is used in a 2008 R2 install on the same box), and I even change the name of the instance from SQL2005_STANDARD to SQL2005_STD. Still get this error with any DDL against that db.
Very frustrating for me right now - do not know what else to try!
🙁
-
I just now mounted the 2005 instance in my copy of 2008 R2 and tried to do some DDL against it from in there, same error. It has to be something in my sys.properties or somewhere, but I cannot find it and am not sure what I need to do to fix this.
-
it sounds to me like there's a server wide or database trigger tracking DDL changes, but the auditing table hasn't been granted rights to teh users doing DDL, so it rollsback the trigger.
select * from sys.triggers
select * from sys.server_triggers <--look here first!
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell!
I just now before coming out here, delete all of my .mdf/.ldf files from the original instance, and also got rid of directories and files that were created for a Full Text Index that this database uses as well (thought maybe that was it, but again - same error when trying to create any DDL against the database from yet another restore of fresh file into the previously emptied directories).
I rant the second SELECT statement on sys.server_triggers, but no results came back. The first SELECT to sys.triggers returned this result set...
Any other thoughts?
-
see your database trigger EventCapture?
do SELECT OBJECT_DEFINITION(EventCapture)
you'll see that that's the culprit; it's writing to the table in the database 'dbEvt.dbo.DTA_Events_Log'
instant fix is to disable the trigger:
DISABLE TRIGGER EventCapture ON DATABASE
lazy fix is to GRANT INSERT TO PUBLIC ON 'dbEvt.dbo.DTA_Events_Log',
another fix is to change the trigger so it uses EXECUTE AS to write to the table isntead of the calling users context.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell sir - you are GENIUS! Thank you so much! I disabled the trigger, and am back in business.
My question is though - this was only happening in my local instance, not in other instances that I access for various SDLC stages of our databases. If this issue crops up in one of them, and I need to disable the trigger from one of those levels - what overall effect is this going to have on it's main function/purpose for being in place to begin with?
Thank you so very much again though - so happy to finally have this resolved!
😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply