The database design for Dynamics / Great Plains is well known to be incredibly poor. Now I’ve run into another reason why the program sucks.

As per best practices, I’ve got alerts set up on all my SQL Servers. One of these alerts is for severity 20 errors. I have a server that is host to some databases used by Microsoft Dynamics (formerly known as Great Plains). For security audit reasons, on this server I also have a trigger that logs each time someone logs in .

After the trigger was installed, I noticed I would get alerts that a severity 20 error had occurred. This was fairly regular, but I noticed it was only happening for three logins. After pulling my hair out for several weeks, I managed to capture some good information via Profiler and was able to figure out what was going on.

The error I was getting was “The client was unable to reuse a sessions with SPID xxx, which had been reset for connection pooling…” The trace showed this error happening each time before the following command:

SELECT desSPRkmhBBCreh FROM DYNAMICS.dbo.SY10550

In fact, further investigation showed the particular table being queried varied, but the column name was always the same. Some Googling turned up this page, which makes my blood boil. To summarize, the developers needed to check for the existence of a table and they discovered that SQL returned results faster when it encountered an error than when it didn’t. So they created a column name based on the initials of the five developers who wrote this code, figuring it would be a safe bet that such a column name would never exist. they would then try to select this column from a table and, if a table not found error was returned, they knew the table didn’t exist. Thus, they got their results quicker. I should also mention this was developed 15+ years ago,when SQL Server 6.5 was the latest version. But this code is still in Dynamics.

The problem is that behind the scenes, unknown to these developer, SQL throws the error and closes the connection. Their program simply recreates the connection in the background, so the end user never notices anything is wrong. However, from SQL Server’s point of view, the closed connection is a severity 20 error. If you are following best practices, your SQL Server has been configured to issue an alert every time a severity 20 error is encountered. This means the DBA will be hit with these alerts every time this code is executed.

This is a classic case of sloppy programming. I hope the developers responsible for this code (Dan Seefeldt, Sean Ryan, Kevin Honeyman, Bruce Chenoweth, and Ray Holzhey) have since learned better programming methods.

I’ve created a Microsoft Connect item to have this fixed. Please vote for this change.