Freigeben über

SQL Server xact abort setting breaks Microsoft Dynamics GP

  • Artikel

David Meego - Click for blog homepageHappy New Year. Wishing everyone a very successful 2014.

I have been back from leave for two weeks, but have been really busy with support cases.

One recent case highlighted a SQL Server setting that I have never used before and the negative consequences of changing the setting from its default value of "off".

 

Background

The customer reported that when Payables Management transactions were fully applied and should be moved to history, that what was happening was the transaction was being removed from the Open tables, but was failing to be added to the History tables. The end result was that the transactions would just disappear from the system.

We used the Support Debugging Tool (https://aka.ms/SDT) to capture logs. From the Dexterity side nothing looked unusual, but from the DEXSQL.LOG the system showed a couple of unusual behaviours.

  1. We were seeing many more of the Invalid column name 'desSPRkmhBBCreh' errors than would be expected. These errors are not a problem in themselves (see blog post), but the frequency that they occurred at was unusually high.
     
  2. We were also seeing many commands in the DEXSQL.LOG repeated. Calls to stored procedures and select statements being issued multiple times when they would normally only occur once.

Reviewing the case notes, I found that it was mentioned in passing that the problem started occurring AFTER the Microsoft Dynamics GP system was migrated to a new server.

So we needed to look at the Database configuration and Server Configuration to see what differences could be responsible for this behaviour

 

The Problems

Further investigation using the sp_helpdb command did highlight that the Dynamics GP system and company databases were set to compatibility 900 (SQL Server 2005) and the database owner was not set to DYNSA.  These settings were easily fixed using the Options page on the Database Properties to change the Compatibility Level and the T-SQL command sp_changedbowner 'DYNSA' to change the owner for each of the databases.  While these settings were wrong, I doubt they were responsible for the issues we were seeing.

I then received an email which explained that the customer's IT Administrator had been comparing settings on the SQL Server between the old and new servers and noticed some differences on the Server Properties on the Connections page.

 

The differences notices was that on the old server: the Remote Query Timeout value was set to 1200 seconds instead of the default 600 seconds, and on the new server: the option xact abort was checked (the default value is unchecked).

The Remote Query Timeout being 1200 would not cause any problems for normal operation. I suggested they move that setting to the new server. There may have been a reason it was increased.

The xact abort setting however was once that I have never come across before, so I did some research to understand what it means and how it can affect the behaviour.

 

The Solution

Looking at SQL Books online for the Server Properties Connection Page it explains the xact abort settings as "Rolls back a transaction if a Transact-SQL statement raises a run-time error." 

I also found an interesting article about the option: Use Caution with Explicit Transactions in Stored Procedures.

While the blog article suggests that it would be safe to leave the option on, the author does not know the Dynamics GP application.  Dynamics GP does generate a number of expected runtime errors (such as the column desSPRkmhBBCreh one) and ignores them or handles them as appropriate.

However, if the xact abort option is enabled, it will cause transactions to be rolled back for the slightest error.... even ones that Dynamics GP expects. 

After unchecking the option and restarting the SQL Server, the problems stopped occurring.

 

Hope you find this useful.

David

Comments