web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AX 2012 - Tetmpdb tabl...
Microsoft Dynamics AX (Archived)

AX 2012 - Tetmpdb table error

(0) ShareShare
ReportReport
Posted on by Ibrahim Salah Mohamed 1,165

I have got an error during posting transaction in AX 2012, that SQL temporary table doesn't exist, this error happen suddenly and the same transaction that make an error might be work fine after few minutes, does anyone see this issue before in AX 2012

===================

"Object Server 01:  The database reported (session 23 (#####)): [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table
'tempdb.DBO.t100098_34E6314310D94206A53AE7197E1B289E', because it does not exist or you do not have
permission.. The SQL statement was: "DROP TABLE tempdb."DBO".t100098_34E6314310D94206A53AE7197E1B289E "

 

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    We expierence the similar/same problem on one of our AX2012 Demo Systems. In our case it happens randomly after a while the system is running.

    In our case we could detect this issue several time as following:

    In Global Address book List page when you do a double click you open the details for an entry the following error is thrown:

    Cannot execute the required database operation.

    The SQL database has issued an error.

    SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot find the object "t100111_B174BE2BEF07450D908577EF7BEDF080" because it does not exist or you do not have permissions.

    SQL statement: TRUNCATE TABLE tempdb."DBO".t100111_B174BE2BEF07450D908577EF7BEDF080

    So far what we could see is that a restart of the AOS solves the issue, but we couldn't establish why this error is thrown. Some users also reported that after closing several times the client also helps.

    What I could identify is that if you go under System Administration --> Inquiries --> Database there is a form called "SQL statement trace log".

    Here these errors are logged.

    In our case here is the stacktrack of the problem with GAB:

    (c)\Classes\QueryRun\next

    (c)\Classes\FormDataSource\executeQuery

    (c)\Forms\DirPartyTable\Data Sources\DirPartyTable\Methods\executeQuery - line 3

    (c)\Classes\FormDataSource\linkActive

    (c)\Forms\DirPartyTable\Data Sources\DirPartyTable\Method\linkActive - line 11

    (c)\Classes\FormRun\run

    (c)\Forms\DirPartyTable\Methods\run - line 3

    (c)\Classes\FormFunctionButtonControl\Clicked

    (c)\Classes\FormRun\task

    (c)\Classes\SysSetupFormRun\task - line 20

    The problem so far happend about 1 or 2 times a week.

    Is there someone who could find the reason for that?

  • Mathew Grisham Profile Picture
    Mathew Grisham 45 on at

    Ibrahim and Thomas, I was experiencing the same error and resynchronized the database and it appears to have worked. I'm not sure what will happen in 2 weeks' time. Hopefully it won't occur again.

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Finally thanks to the microsoft engineer we discovered the following:

    AX2012 in order to manage the temporary tables has a cache per user session on the AOS where the created temp tables are cached. I suppose to avoid sending DDL statements to the database server.

    This caches are cleared when the AOS goes done.

    Here a short scenario for testing it:

    1. If you open an AX client with your user account and i.e. open the GAB List page and with a double click open the GAB details form, a temporary table should be created the first time. You can see it on the temp db they always start with "t100...".

    2. If you close and reopen the AX client AX will give you the same Session ID from the AOS. If you repeat the step of open the GAB details you can see that after closing the client the temp table will still remain on tempdb and after opening the details form no other temp table is created.

    3. At this point you can restart SQL server or kill the AOS sql sessions. Having this the temporary tables created will get purged from tempdb.

    4. If you now again on the AX client try to open the GAB details the error will be raised. Let the current AX client open and try to open a second instance of the client you will not get the error anymore. This is caused by the fact that the second client instance will get a new session ID from AOS and therefore there is no cache until first access.

    So the problem could be reproduced in the following scenario:

    If for some reason the db session from the AOS server was "lost" the temp tables on the tempdb database will get cleared. The AOS next time he tries to access the Database recreates a new SQL session, but the temp tables are still in the AOS cache, but not present on the tempdb.

    In our case we expierenced the problem always on a dedicated machine. The problem in our case was caused due to the fact that one time a week the SQL server (on another machine) was rebooted but our machine having the AOS installed not. After rebooting the AOS the problem no longer exists.

    In addition from an active/passive SQL server cluster point of view the same can happen. I mean in case the active SQL server instance for some reason goes down the sql sessions on the active machine will get closed, the passive machine will go up.

    Next time the AOS tries to access a cached temp table you will expierence the problem.

    We have opened a request to Microsoft in order to manage this issue in order to support active/passive clustering of SQL server.

    One time I get a fix I will post it here.

  • Community Member Profile Picture
    Community Member on at

    I thought (but seem to be wrong) the AOS would stop working when the DB goes offline. But if I understand correctly: the AOS keeps running even if the DB server is rebooted? And when the DB is back online, users can just connect to that AOS (that was DB-less for a short time)?

  • Community Member Profile Picture
    Community Member on at

    Hi Thomas,

    Have you heard anything from Microsoft yet?

    Do you have a hotfix number there will fix this?

    Cheers

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Microsoft has prepared the following hotfix (not yet included in CU3). It is a binary hotfix.

    KB2729496 Kernel does not handle a SQL Active/Passive cluster failover in regards to TempDB tables.

  • Suggested answer
    KOD Profile Picture
    KOD 235 on at

    Hi Ibrahim Salah/Thomas Lorenzi

    Greetings...............!

    we are also struck with below error in event viewer with error code - 117 and 110.

    Whenever we are trying to delete transaction in any company.

    [Microsoft][SQL Server Native Client10.0][SQL Server] The operation failed because an index or statistics with name 'i100.........ID' already exists on table 'tempdb.DBO.........' The SQL statement was" "create index i100.......(Dataareaid,partition,instancerelationtype) where instancerelationtype IN (100654)"

    +Thomas Lorenzi Presently, we are using Ax2012 R2 kernal & application version (6.2.1000.156). The above specified KB2729496 will works for this problem also.

    Thanks,

    Koduri

  • Community Member Profile Picture
    Community Member on at

    @Koduri

    I don't know if your problem described is connected the this particular issue. The initial problem I had in AX2012 but also in R2 it was present. I requested to fix it and MS released KB2729496 (as you already mentioned) for R2 version. With the given hotfix the kernel will be brougth to 6.2.1000.805,

    Did you try a kernel upgrade?

  • KOD Profile Picture
    KOD 235 on at

    No Thomas will try with Latest kernel build 6.2.1000.891and get back to you....!!

    Thanks.

  • Tommy Skaue Profile Picture
    Tommy Skaue Moderator on at

    Interested to learn if this is solved in any of the recent builds. I've seen the same behavior in various AX2012 environments and always solve them by restarting the AOS.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans