Question Status

Verified
Ibrahim Salah asked a question on 7 Mar 2012 7:58 AM

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
"

 

Best Regards,

Ibrahim Salah | Microsoft Dynamics AX Solution architect | My Blog |


Reply
Thomas Lorenzi responded on 7 Mar 2012 9:31 AM

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?

Reply
Mathew Grisham responded on 4 Apr 2012 1:39 PM

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.

Reply
Verified Answer
Thomas Lorenzi responded on 5 Apr 2012 12:09 AM

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.

Reply
Tom Van Dyck responded on 29 May 2012 5:09 AM

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)?

Reply
Søren N. Rasmussen responded on 22 Aug 2012 5:05 PM

Hi Thomas,

Have you heard anything from Microsoft yet?

Do you have a hotfix number there will fix this?

Cheers

Reply
Suggested Answer
Thomas Lorenzi responded on 22 Aug 2012 11:27 PM

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.

Reply
Suggested Answer
KOD responded on 13 Aug 2013 10:07 AM

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

Reply
Thomas Lorenzi responded on 13 Aug 2013 11:18 AM

@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?

Reply
KOD responded on 14 Aug 2013 5:38 AM

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

Thanks.

Reply
Tommy Skaue responded on 14 Aug 2013 11:43 AM

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.

Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

Reply
Juan Carlos Gallegos responded on 28 Oct 2013 5:11 PM

What about Dynamics AX 2012  Feature Pack CU4 ? I have the same error, Are there any KB. thank you.

Reply
Thomas Lorenzi responded on 5 Nov 2013 1:47 AM

@Juan carlos

Assuming you are refereing described by me, as CU's are always incremental the issue resolved in CU3 is present also in CU4.

Reply
Dilipn responded on 14 Nov 2013 5:59 PM

Hi Guys,

We are also having similar problem on AX 2012 CU3. Is there a temporary workaround or fix for this or have to wait till we upgrade to R2?

Cheers,

Dilip

Reply
Dilipn responded on 14 Nov 2013 6:00 PM

To be specific, we get the tempdb error when we go to Vendor > Invoice > Payment history and this happens once a week

Cheers,

Dilip

Reply
Suggested Answer
Joseph Kamau responded on 14 Oct 2014 8:31 AM

Hi,

I got the same Problem and it was to the extent nothing could be posted and some reports like Customer statements were not printing. to sort this out i used an idea i got about How AX works and How it conects to the database

How AX works:

on starting the AOS the temp tables are created and AX will store the table names  until the AOS is restarted. Incase the SQL server restarts and the AOS doesnt refresh the error you are getting appears.

A quick fix to this is

1. Stop the AX server.

2. Stop the reporting Services (to be on the safe side)

3. Stop the MSSQL Server (Services etc)

4. Restart the SQL services and Report Server services

5. Restart the AOS.

this should do a quick fix

to learn more about Dynamics AX customization, .Net Programming and MSSQL database Programming visit my site at


http://joseph.skilledsoft.com

Reply
Verified Answer
Thomas Lorenzi responded on 5 Apr 2012 12:09 AM

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.

Reply
Suggested Answer
Thomas Lorenzi responded on 22 Aug 2012 11:27 PM

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.

Reply
Suggested Answer
KOD responded on 13 Aug 2013 10:07 AM

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

Reply
Suggested Answer
Joseph Kamau responded on 14 Oct 2014 8:31 AM

Hi,

I got the same Problem and it was to the extent nothing could be posted and some reports like Customer statements were not printing. to sort this out i used an idea i got about How AX works and How it conects to the database

How AX works:

on starting the AOS the temp tables are created and AX will store the table names  until the AOS is restarted. Incase the SQL server restarts and the AOS doesnt refresh the error you are getting appears.

A quick fix to this is

1. Stop the AX server.

2. Stop the reporting Services (to be on the safe side)

3. Stop the MSSQL Server (Services etc)

4. Restart the SQL services and Report Server services

5. Restart the AOS.

this should do a quick fix

to learn more about Dynamics AX customization, .Net Programming and MSSQL database Programming visit my site at


http://joseph.skilledsoft.com

Reply