Installation and Technical Issues

[RonnieB]

Hi,

I am getting the following error message from SQL when calling the "spARapiPendingInvcIns" API SP multiple times from the same SQL session:
"Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1."
For example, I have the same Query window open and EXEC a "wrapper SP" multiple times. When I close the query window and open a new one (creating a new SQL

session), the error does not appear.
I tried adding a "SET XACT_ABORT ON" in my wrapper SP, but the issue still persists.
The wrapper SP prepares the data and maps to the staging tables, then calls the spARapiPendingInvcIns SP.
This error happens rarely in the query window mentioned above but happens more often when a .net application keeps an open connection to the server and the user kicks off the process multiple times.

This is for MAS 500 7.3 on SQL server 2008 R2 (64-bit).

Any suggestions as to what may be causing the error or possible solutions?

Thanks,

-Ron

[JohnHanrahan]

Why don't you just close and dispose of all your objects?  Then reinstantiate them when needed.

[RonnieB]

Thanks but this is actually getting called by one of our customer's applications. They have an n-tier app which has an application server manage all the SQL connections, so they can't just open and close connections that way. Plus it would slow down their app if they could to do that.

I think there is a certain scenario that causes the API to leave a transaction open. I have no transactions in my wrapper SP.

[JohnHanrahan]

Based on how the api is written I doubt disposing and recreating the objects would slow it down very much.  Those APIs are not written for speed.

But since you can't do that you're stuck with tracing.  Since it doesn't happen every time this might be tough (and of course it will slow down the process).  Tracing transactions should find it (assuming that's actually the problem).  You might also check tciErrorLog and see if any errors are popping up in there.

John

[LouDavis]

I've seen this error before using this API.  There is an error occurring within a transaction somewhere whie it is running and the process isn't properly rolling back which results in the message you see.  As John points out the only way to find this is by running a trace and seeing where the error is occurring.  Also, it may not be a SQL error that caused this but some business logic within the API that writes to tciErrorLog and simply exits from that point without rolling back the transaction.

I'd also recommend not using the same Session ID each time you run this.  It is better to run with a new session id each time to avoid any issues such as reading old data rom a previous session if you are not using temp tables.  You can do this by simply getting the value of @@SPID from SQL Server for your current database connection.

[RonnieB]

Thanks Louis.

I will try to trace to see what is going on.

What do think about puting this line of code before and after the call to the API?

IF @@TRANCOUNT> 0 ROLLBACK

That would ensure that there are no open transactions "hanging" from the API SPs, no?

[LouDavis]

I do that in my testing scripts so I don't keep resources locked on the server and block others.  You can do it within production code as a failsafe, but you really need to identify where the problem is and correct that so the issue doesn't occur and cause the process to fail in the first place.

[RonnieB · Accepted solution]

Just wanted update you all on how we resolved the issue:

In short, it was a data issue that releaved a bug in the API code.

There is part of the spARapiPendingInvcIns SP that tries to insert into the tglAcctRef table.

There is a unique constraint on the table for CompanyID and AcctrefCode.

The API does not check if the combination already exists in the table and it fails because it would violate the unique constraint.

I should also mention that the GL option for account reference is set to "not validated".

We modified the API to check for existence of the CompanyID and Acctrefcode, if it exists, we get the existing key and let the API use that key, if it doesn't exist, we let the native code take over.

Regards,

-Ron

[JohnHanrahan]

Did you find it by tracing?