Follow Up on Event ID 110 and 117 (annoying database error)

I wrote about this a while ago and since this still is what I call an annoying database error (noice) and something very common to AX 4.0/AX 2009 solutions, I would like to call on my readers to vote for a change in the AOS kernels (x32/x64) to filter out this information to prevent bombing the event log with errors not beeing errors at all. The voting can be done at Microsoft Connect.

Finally I would like to quote a paragraph in Microsoft KB article 143339:

Applications need to code their error handling routines for SQL Server connections to call SQLError() until it returns SQL_NO_DATA_FOUND, and act on any messages other than the ones that return a pfNative code of 5701 or 5703.

Maybe it's impossible to change this behaviour, but as long as Microsoft don't state this in their feedback on Connect, it's still an issue that I find hard to accept...

Event ID 110 and 117 (annoying database error)

I observe the issue described below in both AX 4.0.x and AX 2009 SP1 x solutions running SQL Server 2005 and 2008 (the version of SQL Server Native Client is the same). This is also reported by others in several Communities like microsoft.public.axapta (last one beeing a post by Mike titled "AOS errors" from February 25 2010 where one suggestion is to syncronize the SYSLASTVALUE table).

Eventlog, application

#1 Event ID 110

Object Server 01: Dialog issued for client-less session 1: Cannot edit a record in LastValue (SysLastValue). User ID: , AdminUserSetup.
The SQL database has issued an error.

#2 Event ID 117

Object Server 01:
The database reported (session 1 (-AOS-)):
[Microsoft][SQL Native Client][SQL Server]Changed language setting to us_english.
The SQL statement was:
"UPDATE SYSLASTVALUE SET VALUE=?,RECVERSION=? WHERE (((((((USERID=?) AND (RECORDTYPE=?)) AND (ELEMENTNAME=?)) AND (DESIGNNAME=?)) AND (ISKERNEL=?)) AND (COMPANY=?)) AND (RECVERSION=?))"

I don't know if this has any impact on the solutions at all and I have also found some information on Partnersource describing this as "noice". And I also find it a little bit hard to understand why a syncronization of the system table SYSLASTVALUE should be needed since the real error message in fact is stating "Changed language setting to us_english.".

The reason for posting this is that I find it a little bit hard to understand why something is logget as an error situation if this is "just noice". AX is in general bad at categorizing events in event log application, but I would like to share some experience with others regarding this. Since it's reported from the ODBC layer it could be related to settings like default language for the DBO user (aka AOS Service Account) or inproper settings for Regional Settings for the same Service Account. This is a couple of my own teories left to verify, but bottom line I find it hard to be "just noice".

Hopefully we can start a good discussion around this and bring the issue forward to the system vendor instead of reporting it individually (perhaps a bigger impact).

Update March 10 2010:
For those of you not reading the comments, I would like to point out that Chris pointed to an important KB-article from Microsoft. After looking a little bit more at the details (Googled for NativeError 5703), I found another reference on MSDN. Quote:

The SQL Server ODBC driver returns SQL_SUCCESS_WITH_INFO on a successful SQLConnect, SQLDriverConnect, or SQLBrowseConnect. When an ODBC application calls SQLError after getting SQL_SUCCESS_WITH_INFO, it can receive the following messages:

5701—indicates SQL Server initially putting the user's context into the default database defined at the server for the login ID used in the connection
5703—indicates the language being used on the server

If either the ODBC data source has a default database specified or the application specified the DATABASE keyword on SQLDriverConnect or SQLBrowseConnect, there will be a second 5701 message that indicates the user's context has been switched to the database requested.

My conclusion is that the AOS kernel treat every error raised from the ODBC layer as an error. If Microsoft could implement a change to filter out NativeError 5701 and 5703, a lot of confusion could be saved and not at least a lot of time investigating this issue could also be saved.

Until further notice, this is my conclusion.

Again, thank you Chris for leaving this valuable information.

Update #2 March 11 2010:

I have today posted this as a suggestion on the MS Dynamics AX Suggestions Home page at Microsoft Connect. Please sign in at Connect and vote this up if you find this issue to be a valuable improvement.

Update #3 May 11 2010:

The EMEA Dynamics AX Support team recently released a post giving another explanation. It can be found here.