Custom Trigger in Microsoft Dynamics SL 2011 -- Set Nocount On

  • Comments 2

If you plan to use a custom trigger in any version of Microsoft Dynamics SL 2011, be sure to add "set nocount on" at the beginning.  Some of the symptoms of a custom trigger firing without "set nocount on" are SQL Server Message 62, SQL Server Message 63, and SQL Server Message 93 in conjunction with a timeout error.

The “set nocount on” command limits the amount of noise (such as status messages, row counts, etc..) that get returned to SWIMAPI.   SWIMAPI has never liked this "noise", but in 7.x and prior it was a bit more tolerant.  In 2011, it is far less tolerant.  In any version of SL, any custom trigger should start out with a “set nocount on” command or it could cause problems such as the aforementioned error messages 

 

  • We have just upgraded a client to Dynamics SL 2011 FP1 and SQL 2008. They keep getting Sql Server Message 67 and 93 when the user releases an AP Voucher & Adjustment Entry batch.

    I did find another Post that referenced the Stored Procedure Batch_Mod_Rlsed, but ours appears to be correct as shown in that post. "Sql Server Msg 93 - connection is busy with results for another command" - marked as answered - posted by Kim Huggins on Aug 27, 2012 and updated by Peter Lee on May 8, 2013.

    One comment was made about custom triggers and SQL 2008 being very picky about the Set Nocount On verbiage.

    Based on the AP tables, I checked APDoc and APTran and there are no triggers on those tables.  Upon checking the BATCH table there is a trigger named 'batchhold_trigger'.

    Although it is not a custom trigger, it does not contain the Set Nocount On verbiage. Could that be the issue of why the 67 and 93 errors are coming up?

    Your review of the trigger would be appreciated. If the Set Nocount On is the resolution, please provide an explanation of how to get it modified.

    Thanks,

    Laura Vogel

  • Hi.  I am receiving this same error.  Have you found a fix for the problem?  Did you take off the nocount on the batchhold_trigger?

    Thanks,

    Serena