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: Friday, August 15, 2014 5:03 AM
Points: 43, Visits: 363
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: Monday, July 8, 2013 9:46 AM
Points: 101, Visits: 842
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: Friday, August 15, 2014 5:03 AM
Points: 43, Visits: 363
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: Friday, August 15, 2014 5:03 AM
Points: 43, Visits: 363
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: Friday, August 15, 2014 5:03 AM
Points: 43, Visits: 363
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:47 AM
Points: 498, Visits: 976
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: Friday, August 15, 2014 5:03 AM
Points: 43, Visits: 363
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:47 AM
Points: 498, Visits: 976
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: Friday, August 15, 2014 5:03 AM
Points: 43, Visits: 363
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:47 AM
Points: 498, Visits: 976
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