Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

SQL Server xact abort setting breaks Microsoft Dynamics GP

SQL Server xact abort setting breaks Microsoft Dynamics GP

Rate This
  • Comments 2

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 (http://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

  • Hi David,

    That's an interesting case as I recently had a concern on my GP 2010 server about performance issues with the Project Accounting module when editing the budget for an existing Project... the GP client was like freezing for about several minutes for no reasons.. I looked on the system performance on the server side while this was happening and there we go ... the CPU was hitting nearly 100% for about 2-3 minutes without any reasons. The strange thing is that it doesn't happen to every existing project... sometimes it's just a new project I'm adding a Cost Category and when the CC is entered, at the moment I tab out of the field, the client freezes for several minutes. The next line sometimes is OK, sometimes freezes too...

    This is really something I need to trace down thru SQL to understand what's going on ..

    Have a great time,

    Beat

  • Posting from John Lowther at Microsoft Dynamics GP DBA

    community.dynamics.com/.../just-some-things-i-found-today.aspx

Page 1 of 1 (2 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post