SQL Agent - SQLSTATE 21000 error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SQL Agent - SQLSTATE 21000 error

18 posts, Page 1 of 2

1 2 »»

SQL Agent - SQLSTATE 21000 error

Rate Topic

Display Mode

Topic Options

Author

Message

romanoplescia

Posted Monday, January 11, 2010 4:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:22 AM
Points: 40, Visits: 322

I have found weird error in my SQL Agent which is produced about every 30 seconds. The message reads as follows

[298] SQLServer Error: 512, 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]

Strange thing is I cannot find the process which is causing this error, I have even disabled all SQL Agent jobs and restarted the service and server eventually but it still produces this error message every 30 seconds.

Anyone come across this before?

Post #845276

Shivrudra

Posted Monday, January 11, 2010 4:23 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 7:00 AM
Points: 99, Visits: 837

Hi,

What is the version of SQL server and OS ?

Regards,
Shivrudra W

Post #845280

romanoplescia

Posted Monday, January 11, 2010 4:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:22 AM
Points: 40, Visits: 322

SQL 2008 , Windows Server 2003 Standard x64

Post #845287

romanoplescia

Posted Tuesday, January 12, 2010 7:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:22 AM
Points: 40, Visits: 322

Anyone...

Post #846149

romanoplescia

Posted Monday, February 08, 2010 10:19 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:22 AM
Points: 40, Visits: 322

Hi, just want to bump this post again. I'm still having this issue and no luck with it so far. Has anyone experienced this with the SQL Agent?

Post #861849

aureolin

Posted Tuesday, February 09, 2010 1:39 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:36 PM
Points: 494, Visits: 944

Two places to look

1) Check your triggers. This error may be a 'side effect' rather than directly in a SQL Agent job

2) Run profiler to see if you can isolate the transaction that's causing the problem.

And, finally, one question: When you disable the SQL Agent process, but leave SQL Server running, do you see this problem in the logs?

Steve G.

Post #862716

romanoplescia

Posted Wednesday, February 10, 2010 8:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:22 AM
Points: 40, Visits: 322

Hi Thanks for that advise, I have disabled the only trigger I have on the database and have run a trace in sql profiler for error logs, SQL batch exec ect but it hasn't turned up anything. Are there any specific events you recommend I should check in the profiler.

Post #863282

aureolin

Posted Wednesday, February 10, 2010 9:08 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:36 PM
Points: 494, Visits: 944

One thing to consider: Since you're not finding this where you expect (SQL Agent jobs) then perhaps it's somewhere else. Is this a production database or a development system?

As for profiling, I'd track all T-SQL transactions regardless of whether they're Agent jobs or not.

One last question: when you completely disable the SQL Agent service, do the errors go away?

Steve G.

Post #863356

romanoplescia

Posted Wednesday, February 10, 2010 10:48 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:22 AM
Points: 40, Visits: 322

This is a production database, the errors appear in the SQL Server Agent log only and not in the normal Sql Sever log so naturally when I stop the Agent the the problem no longer appears in the log as the Agent is not logging anything.

This has been a really tricky one, I have tried to replicate it on the staging/test server but there it works fine, no errors. On the productions server I have stopped every process I can think of other that stopping the SQL Server and monitored the log but they still appear Unsure

Post #863472

aureolin

Posted Thursday, February 11, 2010 5:59 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:36 PM
Points: 494, Visits: 944

I'm assuming from your earlier comments that you can't determine the job name or id from the log file? Which version of SQL Server are you using?

Post #864419

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions