SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Agent - SQLSTATE 21000 error


SQL Agent - SQLSTATE 21000 error

Author
Message
romanoplescia
romanoplescia
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 433
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?
Shivrudra
Shivrudra
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 880
Hi,

What is the version of SQL server and OS ?

Regards,
Shivrudra W
romanoplescia
romanoplescia
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 433
SQL 2008 , Windows Server 2003 Standard x64
romanoplescia
romanoplescia
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 433
Anyone... Crying
romanoplescia
romanoplescia
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 433
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?
aureolin
aureolin
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1071 Visits: 1056
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.



romanoplescia
romanoplescia
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 433
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.
aureolin
aureolin
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1071 Visits: 1056
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.



romanoplescia
romanoplescia
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 433
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
aureolin
aureolin
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1071 Visits: 1056
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?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search