HELP: A severe error occurred on the current command.
in [Server Configuration]
|
From: Erland Sommarskog on 23 Mar 2010 18:18 Ben Hopkins (BenHopkins(a)discussions.microsoft.com) writes: > The offending query was: > > update job > set invoice_id = null > where invoice_id = (select invoice_id from inserted where > update(invoice_status) and invoice_status = 'X') As Iain Sharp notes, this query could be improved. First of all, it would be better to move the UPDATE() to an IF statement, second using EXISTS makes the query more robuse: IF UPDATE(invoice_status) BEGIN UPDATE job SET invoice_id = NULL FROM job b WHERE EXISTS (SELECT * FROM inserted i WHERE i.invoice_id = j.invoice_id AND i.invoice_status = 'X') END However, even if there was a problem with the subquery returning more than one row, you should not get an access violation. Any AV you get with SQL Server are either due to bugs in SQL Server or a corrupted database. In this case I would suspect the odd usage of the UPDATE() function to be the culprit. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Ben Hopkins on 24 Mar 2010 04:29 Thanks for your responses. We have updated the query. As mentioned previously the old query had been working for some time before the error occured. Not sure why it decieded to stop working but is it likely to happen again? Im sure there are lines similar to the previous one elsewhere within the database. If badly written can they break over time?
From: Erland Sommarskog on 24 Mar 2010 18:47 Ben Hopkins (BenHopkins(a)discussions.microsoft.com) writes: > Thanks for your responses. We have updated the query. > > As mentioned previously the old query had been working for some time > before the error occured. Not sure why it decieded to stop working but > is it likely to happen again? Im sure there are lines similar to the > previous one elsewhere within the database. > > If badly written can they break over time? As you may know, SQL Server has a cost-based optimizer that calculates what it estimates to be the best query plan. This means that your query can execute quite differently from day to day. This is quite different from code in traditional language, where you state exacly what to do. So in SQL you can experience that code behaves quite differently from one day to another, as changes in statistisc makes the optimizer to take a different decision. Then again, an error like the one you got, can easily occur in a C++ program that has been running for a long time. There is an error that cause memory to be thrashed, but most of the time that does not happen. But one day data is different and then... But, yes, if you have more WHERE clauses in triggers that refer the UPDATE() function, I would recommend you to review these queries, as there might be a performance cost with it. (When the column in the UPDATE clause is not updated.) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Ben Hopkins on 25 Mar 2010 10:38
Thanks again for your comments They have been most useful. |