Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Agent - SQLSTATE 21000 error Expand / Collapse
Author
Message
Posted Monday, January 11, 2010 4:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 22, 2015 10:50 AM
Points: 46, Visits: 392
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
Posted Monday, January 11, 2010 4:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 22, 2015 11:25 PM
Points: 106, Visits: 865
Hi,

What is the version of SQL server and OS ?


Regards,
Shivrudra W
Post #845280
Posted Monday, January 11, 2010 4:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 22, 2015 10:50 AM
Points: 46, Visits: 392
SQL 2008 , Windows Server 2003 Standard x64
Post #845287
Posted Tuesday, January 12, 2010 7:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 22, 2015 10:50 AM
Points: 46, Visits: 392
Anyone...
Post #846149
Posted Monday, February 8, 2010 10:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 22, 2015 10:50 AM
Points: 46, Visits: 392
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
Posted Tuesday, February 9, 2010 1:39 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 501, Visits: 992
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
Posted Wednesday, February 10, 2010 8:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 22, 2015 10:50 AM
Points: 46, Visits: 392
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
Posted Wednesday, February 10, 2010 9:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 501, Visits: 992
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
Posted Wednesday, February 10, 2010 10:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 22, 2015 10:50 AM
Points: 46, Visits: 392
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
Post #863472
Posted Thursday, February 11, 2010 5:59 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 501, Visits: 992
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 »

Add to briefcase 12»»

Permissions Expand / Collapse