Getting error while executing sql command

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi all

When ever i am running the below query:

delete from email_data where exp_date < getdate()
I am getting the below error:

Msg 208, Level 16, State 1, Procedure trgInsteadOfDelete, Line 9
Invalid object name 'email_test'

Please tell me whats the error

Posted 15-Aug-12 20:52pm

Code-Hunt

Add a Solution

Comments

Santhosh Kumar Jayaraman 16-Aug-12 3:32am

post ur trigger here

[no name] 16-Aug-12 3:37am

there is no sp or trigger...its a one line delete command..

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2012-08-15T20:56:00

Solution 1

You need to look at your stored procedure "trgInsteadOfDelete" at line 9 - the problem is that the procedure is being called by a trigger when a delete occurs, and it is probably looking at the wrong table.

Posted 15-Aug-12 20:56pm

OriginalGriff

Comments

[no name] 16-Aug-12 2:57am

But i don't have any such stored procedure in my DB

OriginalGriff 16-Aug-12 3:34am

Check under "Database Triggers" in the "Programmability" section with SSMS - I bet you do! :laugh:

Wendelius · Answer 2 · 2012-08-15T21:19:00

Solution 2

based on your question: You get an error saying that the table 'email_test' does not exist but you state that you try to delete from email_data

So re-check the statement you're executing against the database just before it's executed in the code. Is the SQL string you're using changing somewhere or does it contain a typo.

Posted 15-Aug-12 21:19pm

Wendelius

Comments

[no name] 16-Aug-12 3:20am

email_test id the database and email_data is the table.

Wendelius 16-Aug-12 3:24am

Ok, so the table is qualified in the delete statement so somethng like:

delete from email_test.email_data

If that's the case, does the user have permissions to access and delete from that table in that database?

[no name] 16-Aug-12 3:36am

yes permissions are there..actully the error giving at "trgInsteadOfDelete" stored procedure but there is no stored procedure with such name..

Wendelius 16-Aug-12 3:38am

Based on the name it's probably not a procedure but a trigger. So using for example SSMS, check the triggers of the email_data table.

[no name] 16-Aug-12 3:40am

but there is no trigger in the database...i don't know why it is giving such error...

Wendelius 16-Aug-12 3:45am

In the email_test database, try running the following:

select * from sysobjects where lower(name) = 'trginsteadofdelete'

do you see any results?

[no name] 16-Aug-12 3:48am

there is one table structure..but no data...

Wendelius 16-Aug-12 3:54am

What is the value in Type column when you executed the statement. Is it TR? If it is, then you have a trigger (possibly an InSTEAD OF trigger) on the table

[no name] 16-Aug-12 3:57am

there is no value..its an empty table...

Wendelius 16-Aug-12 4:03am

Does the query:

select * from sysobjects where lower(name) = 'trginsteadofdelete'

return any rows?

Sysobjects is not an empty table. It's a system table containing information about every object in the database.

The point is that using the above query you can find out what object has the name 'trginsteadofdelete' in your current database.

[no name] 16-Aug-12 4:05am

the output of ur query it returned me a empty table structure..no data...