What would cause an existing job to stop working? 1

[JFoushee]

Hello.

I have a SQL Server Management\Job that deletes records from a table that are over 45 days old.

Recently, the job has been failing, with this message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

Yes, normally more than one record gets deleted each time it runs. So what happened?

Thanks.

 

[demopro]

I don't think the error has anything to do with the number of rows that you are deleting, rather it is informing you that it is returning more than it is supposed to.
I had a similar experience with this error and it turned out that I did not account for a user to have the same last name (in my situation) and had to changed the code. According to MSDN these error has these description.

Select returns more than one row, or subquery preceded by = returns more than one value.

Check back over your code to see if you can find it or post it here so we can look at it.

 

[tlbroadbent]

If you haven't solved the problem, post your query so we can help debug it. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[JFoushee]

The query behind the job is this:

delete table1 where datediff(day, time_end, getdate()) > 45

It is the only step in the job.

Step ID 0
The job failed.  The Job was invoked by Schedule 10 (delete table1 on sundays at 1:15 AM).  The last step to run was step 1 (delete table1 over 45 days old).

Step ID 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

 

[tlbroadbent]

I can't see any reason for the error. Try changing the query as follows.

delete table1
where time_end < getdate() - 45
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[JFoushee]

Same outcome

 

[JFoushee]

It actually gives the same response in Query Analyzer. I have a development database with practically the same data, and I do not get this message on the development database.

 

[tlbroadbent]

There must be something more happening. Is there a delete triggger on the table? If there is, it is probably causing the problem. Frequently triggers are written to handle one record at a time and multiple deletes, inserts or updates can cause problems. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[JFoushee]

There is a delete trigger not mirrored on the development database:

CREATE TRIGGER delete_jobdetails ON JOBSUMMARY FOR DELETE AS DELETE from JOBDETAIL where JobNum = (Select JobNum from deleted)

The table &quot;JobSummary&quot; has been the one I refer to as &quot;Table1&quot; above.

I will come back after some testing. But I already like the idea.

 

[JFoushee]

That was the problem. I attempted to make a foriegn key constraint (to replace the trigger) by using the Diagrams tool in Enterprise Manager. However, this particular server has that &quot;CoInitialize has not been called.&quot; problem when creating diagrams.

I guess I'll use T-SQL to make that foreign key.

But thanks again!!