Thursday, February 26, 2009

SysServerSessions and SysClientSessions are missing in the Ax 2009 database

Sometimes during an upgrade to Dynamics AX 2009, you get the following errors -

Symptoms

Object Server 05: The database reported (session 1 (-AOS-)): [Microsoft][SQL Native Client][SQL Server]Invalid object name 'SYSSERVERSESSIONS'.. The SQL statement was: "SELECT A.SERVERID,A.AOSID,A.INSTANCE_NAME,A.VERSION,A.LOGINDATETIME,A.LOGINDATETIMETZID,A.STATUS,A.LOADBALANCE,A.WORKLOAD,A.LASTUPDATEDATETIME,A.LASTUPDATEDATETIMETZID,A.RECVERSION,A.RECID FROM SYSSERVERSESSIONS A WHERE (STATUS=?)"

Object Server 05: SQL diagnostics: [Main Microsoft Dynamics Application Object Server Session]Unable to report problem. Attempted to open message 310.

Object Server 05: Dialog issued for client-less session 1: Cannot select a record in Current AOS instances (SysServerSessions). ServerId: 0, .
The SQL database has issued an error.

Object Server 05: The database reported (session 1 (-AOS-)): [Microsoft][SQL Native Client][SQL Server]Invalid object name 'SYSCLIENTSESSIONS'.. The SQL statement was: "SELECT A.SESSIONID,A.SERVERID,A.VERSION,A.LOGINDATETIME,A.LOGINDATETIMETZID,A.STATUS,A.USERID,A.SID,A.USERLANGUAGE,A.HELPLANGUAGE,A.CLIENTTYPE,A.SESSIONTYPE,A.CLIENTCOMPUTER,A.RECVERSION,A.RECID FROM SYSCLIENTSESSIONS A WHERE (SERVERID=?)"

Object Server 05: Internal Error occurred executing stored procedure when creating session for the AOS.

Resolution

Make sure to backup your database before running this below script

Run this SQL and restart the AOS

update SqlSystemVariables set value = value - 1 where parm = 'systabversion'

The kernel uses the SYSTABVERSION parameter value in the SqlSystemVariables table to synchronize its list with the DB. By running the below query you tricked the AOS to think that the DB is outdated and during startup it created those missing tables.

at

3 comments:

  1. AnonymousAugust 14, 2009 at 4:48 PM

    thank you, you are a lifesaver!

    ReplyDelete
  2. DanielMarch 8, 2011 at 4:15 AM

    It really works Thanks, from Mexico, i was upgrading from ax 2009 sp1 to rollup 6 and having issues while trying to sync database, try your trick and everything went completely smooth after that. Thanks a lot. www.drattek.com

    ReplyDelete
  3. AnonymousSeptember 26, 2013 at 4:42 PM

    HI Dilip, I am also facing similar error during Ax 2012 R2 Upgradation .
    Error :
    Object Server 01: The database reported (session 2 (-AOS-)): [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'SYSCLIENTSESSIONS'.. The SQL statement was: "UPDATE BATCH SET RETRYCOUNT=(RETRYCOUNT+?),STATUS=?,MODIFIEDDATETIME=?,RECVERSION=? WHERE ((STATUS=?) AND (RETRYCOUNT?)))"

    But this update query does not work in my case. Can you help.

    ReplyDelete