Dynamics Community Forum Thread Details

Invalid object name '##taAnalyticsDistributionUSRDEFND4'.

Hello,

I have a Windows service that uses eConnect to transfer transactions from a custom Customer Management Application to GP (2015). I do not have any insight into a GP application itself. This integration has been in place since 2016 and nothing changed in the Windows service code for years. In August of 2021 we started experiencing an issue with one of the processes that runs once a month. When it runs on schedule on the first day of the month it return an exception:

The GMS/DGP Integration Service has an exception. See event log and restart service: Microsoft.Dynamics.GP.eConnect.eConnectException: Invalid object name '##taAnalyticsDistributionUSRDEFND4'. at Microsoft.Dynamics.GP.eConnect.ServiceProxy.HandleSqlException(FaultException`1 ex) at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateEntity(String connectionString, String xml) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction) at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateEntity(String connectionString, String sXML) at GMS_DGP.Common.DGPBuilder.RunMonthlyTransactions(Int32 MM, Int32 YY) in D:\a\1\s\DGP\GMS_DGP.Common\DGPBuilder.cs:line 1330 at GMS_DGP_IntegrationService.GMS_DGP_IntegrationService.ExecuteImports(Object sender, EventArgs e) in D:\a\1\s\DGP\GMS_DGP_IntegrationService\GMS_DGP_IntegrationService.cs:line 193

However, if the job is requeued, it runs as expected with no errors.

I'm not sure how to approach this issue as it's transient and I am unable to consistently reproduce it. What could be causing it?

Any help is appreciated. Thank you!

Categories:

Other

Hello,

This is a temp table that is created within the taAnalyticsDistribution stored procedure, which handles adding AA data to the distributions you're creating.

The stored procedure checks if a ##taAnalyticsDistribution temp table already exists and if so, a series of these temp tables are created. It seems like ##taAnalyticsDistribution is present, so it skips over creating ##taAnalyticsDistributionUSRDEFND4. The other 3 temp tables must exist for this process because there are no errors when those are called.

Since these are ## temp tables they are global in nature and should only be dropped when all connections to the table are closed. They aren't tied a single user or SPID.

It doesn't look like we've had any reports of an error like this in the past, so I don't have an easy answer for you right now. Maybe if you can identify what changed, either in your environment or in a Dynamics GP component (e.g. an upgrade or server move) that could point to something. Something seems to be causing the temp table to get dropped when other temp tables involved in the same process are not. Unfortunately the eConnect procedures are encrypted, so running a SQL Profiler trace while you reproduce the issue probably won't show much unless you have a method to decrypt them.

Quick Links

Leaderboard