Error in Script Created a bad object - How do I delete the object?
-
Hello....Im new to the SQL world and need to learn the environment still.....I was attempting to create a trigger for the first time and was creating the table and all that goes along with it and managed to run one of my statements that had a typo in it....which somehow created a rogue object. I will show you the scripts I created below and which one had the deadly typo in it... :
Script #1
/* Create Audit Log */
USE Database_stage
GO
CREATE TABLE dbo.AuditLog
(Command NVARCHAR (1000),
PostTime NVARCHAR(24),
HostName NVARCHAR(100),
LoginName NVARCHAR(100)
)
GO
Script #2 (the script with the misspelled table name)
/* Create the trigger */
CREATE TRIGGER AuditOperations
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @hostname NVARCHAR(100)
DECLARE @loginname NVARCHAR(100)
SET @data = eventdata()
SET @cmd = CONVERT(NVARCHAR(100),@data.query('data(//TSQLCommand//CommandText)'))
SET @posttime = CONVERT(NVARCHAR(24),@data.query('data(//PostTime)'))
SET @spid = CONVERT(NVARCHAR(6),@data.query('data(//SPID)'))
SET @hostname = HOST_NAME()
SET @loginname = SYSTEM_USER
INSERT INTO dbo.AudtitLog(Command,PostTime,HostName,LoginName)
VALUES (@cmd, @posttime, @hostname, @loginname)
GO
For some reason the script above ran just fine without errors which is why it took me a while to find out what my issue was.....the table AuditLog was misspelled in the INSERT statement at the bottom of the above script. Now whenever I try to run anything or delete the table itself or even delete a user associated with the databas I get an error "invalid object name dbo.AudtitLog". I cant even find the object to delete it. Like I said I am still very new to SQL so bear with me.
How can I find the object? and then....How do I delete it?
Thank you for any help you all can give!
-
And does dbo.AuditLog exist?
Maybe the table was created with a different owner (no clue how but you seem conviced)... or maybe it just wasn't created at all!!
-
When I ran the create table script is created the table just fine. But when I ran the second script that creates the trigger and then "INSERT INTO dbo.AudtitLog(Command,PostTime,HostName,LoginName)" runs....because of the misspelling in that INSERT statement I now have a bad object called dbo.AudtitLog which I cannot find but I still get errors when running other scripts on that db that refer to the bad object.
I guess I need to find out how to find the location of the object called dbo.AudtitLog. How do I do that kind of search and destroy?
-
I'm pretty sure it wasn't created.
Check in the list of tables and I'll bet you won't find it.
or in here : Select * from INFORMATION_SCHEMA.TABLES
-
Sorry...are you refering to the bad object? The table was created for sure....but the bad object being refered too i cant find....so if it didnt get created whats generating the errors that refer to the misspelled object? Thank you so much for replying by the way
any help is a big help! -
The create table part worked. But the trigger itself cannot find the misspelled object and throws you a can't find object error. There's no way that 2nd obejct was created with the script you pasted.
-
It's gonna give the same error wether you select from it or try to destroy it... can't find the object
. -
your problem is that any operation such as dropping a table is causing your trigger to run which is causing your error.
Try dropping the trigger first:
drop
trigger AuditOperations on database
Then re-create your trigger with the correct table name.
-
Nice catch... would never have though of that one
. -
NICE! I didnt even think of that! Wish I had checked the forum before I did the following....although my resolution worked I would have much rather just dropped the trigger. THank you very much for the replies!!
Update - I had a couple of the so called SQL Guru's here look at the issue and they had no idea what was causing the error when we tried to delete the table either. The mystery object was preventing the daily maintenance scripts and the backup script from running as well. Because I couldnt wait to find the answer I just restored the database from Monday morning's backup which has us back on track.
Im still baffled by the problem though.
-
The drop trigger didn't work either???
That's what made the most sens in all of this.
-
SOrry.....yes Im certain it would have solved the problem....unfortunately I didnt see the post till after I did the restore....although I ran the same scripts and created the same problem on my test environment and did a drop trigger and it DID solve the problem.
Thanks again!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply