Disallow results from triggers – this isn’t the solution I was looking for

TL;DR Version:

You can set the advanced option “Disallow results from triggers” to 1 to get past the error “Message: A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding resultset was active.”

Before you do this, be sure that you know the root cause of the error, and the impact of that setting – the problem may not actually be with the trigger.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

My employer’s ERP encrypts all of their stored procedures.  While this is good for them, it means that whenever something must be added to the workflow for say… saving customer records…  we must implement triggers to get the job done.  The ERP also has many popular 3rd party add-ins or modules which are forced into the same methodology.  The end result is that the customer table for instance has 11 (Eleven!!!) triggers on it.  Only one of those is in-house.

Let me just say for the record that as a general rule I really do not like triggers.  Yes, I know they have their place, and are often necessary.  That said, between the fragmented code, increased potential for blocking/deadlocks, etc. I’ve found they’re generally more trouble than they’re worth.

So – on to the issue at hand.  We are currently changing some applications in our stack, and moving to a hosted CRM solution.  As part of that move, we must build integrations for master data between the ERP and CRM systems.  One of the business requirements is that the master data key from CRM must exist in the ERP so that integrations to other systems can be created.

The CRM integration utilizes two triggers on the master data tables (one each for inserts and updates) which write to a queue table; the integration then reads from that table to pull master data into CRM and do its thing.  From there, the integration then pulls the ID from CRM and pushes it back to the ERP.

I’m sure all you DBA’s out there have spotted the first problem with this sequence – updates coming from CRM will fire the triggers, placing the record back in the queue; this obviously defeats the purpose of the queue, as nothing would ever fall out of it.  To get around this, we added code to the triggers to check the login making the update – if the update comes from the CRM service account, the trigger returns without executing any additional code.  Problem 1 solved.

With the aforementioned loop handled, we proceeded to test the integration.  First with one record – success; then with multiple records – FAILURE!  I’m guessing this is why you’ve hit this entry – the error was as follows:
"Message: A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding resultset was active."
Well crap.

I’ll freely admit that this was the first time I had encountered this error.  After a few minutes of googling, I had confirmed what made intuitive sense to me, but I had never really needed to think about: triggers that return results are bad (I’m sure there will be some that disagree with that blanket statement, but I’ll stick by it).  I also learned about the advanced setting “Disallow Results from Triggers”, and how it’s set to 0 by default (allowing results) despite best practice being to not allow results from triggers…  makes perfect sense.

In all of the searching I did, it seemed like the problem was a new trigger on a table that suddenly caused the error; there were only two solutions suggested in all the sites I looked at: either fix the trigger (set nocount on, remove any select statements returning results), or set that advanced setting to 1, which affects all triggers on the server.

Armed with those bits of knowledge, I began looking at the code on my server…  and found that all of the involved triggers followed best practice.  All had nocount set to on, and none returned results via a select.

Well that was unexpected.  I had expected that at least one of the triggers would have the decency to have bad code and give me an easy fix.  Not so.  Time to dig deeper…

The next step in troubleshooting was to disable all but one of the triggers.  Because this issue cropped up during the CRM integration, we disabled the CRM triggers to prove that they weren’t the issue.  Sure enough, success with one row, failure with multiple – even with only one trigger.  Things were getting weird.

The next step was to do the obvious – set the disallow results from trigger option to 1.  As expected, everything worked, even with all triggers enabled and multiple rows pushed through the integration.  Success, right?  Not quite.

You see, we have a few other integrations that have been in place for a long time which would do mass updates on records with those triggers in place, and those NEVER FAILED.  If that setting were the solution, we should have expected to see that error long before now.  Further, our ERP has… lots…  of tables, and upwards of 10 triggers on a given table isn’t uncommon (did I mention how much I hate this model?).  So not only do we have LOTS of triggers which have never failed due to multiple resultsets, but this is spread over close to 25 databases, because each legal entity is contained in its own database for the ERP.  Where the server setting really is a server setting, I would have to go through all of the code for all of the triggers in all of the databases to ensure that this setting would not break something.  NOT GOING TO HAPPEN.

Where to look next?  Well, considering that everything was functional before the CRM integration, and that the CRM triggers follow best practice and had been proven not to be the issue, the only logical place left to look was the code for the integration itself.

The integration is built using a hosted 3rd party tool.  I set up another meeting with the vendor, and we dove in.  On my side, I set up a trace to watch activity coming from the integration.  On their side, they first ran the integration for one record.  Once again success.  In the trace, I noted that the integration ran a query to pull the record to be integrated, then ran another to push the update coming back from CRM.  I was using the default trace, so these showed up as:
Batch Starting: select top 1 * from <table>
Batch Completed: select top 1 * from <table>
Batch Starting: update <table> set...
Batch Completed: update <table> set...

Next was the run for multiple records.  This time the trace looked like this:

Batch Starting: select top 1 * from <table>
Batch Starting: update <table> set...
Batch Completed: update <table> set...
Batch Starting: update <table> set...
Batch Completed: update <table> set...

See the difference?  When running for one record, the integration is closing it’s batch before proceeding to update the record.  When running for multiple, that initial resultset is held open and processed RBAR.  Remember our error?
"Message: A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding resultset was active."
This error is complaining because there is an open resultset that prevents the triggers from executing.  Why?  We could get technical and talk about locking, transaction isolation, etc., but that shouldn’t be necessary.  Suffice it to say that the triggers can’t execute with an open resultset.

The end result in this situation was that the integration was re-written.  We tried a few different ways to force that initial query batch to terminate before processing the rows, but limitations in the development tool prevented this from working.

I’d wager that the majority of the time you see this error it is due to a problem with the triggers.  When it’s not, you always have that big hammer of changing server settings.  However, as my experience shows, it is a good idea to dig a little bit to determine if the error actually is in SQL before you do anything too drastic.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s