eConnect General Ledger and Duplicate Key Issues

(0) Share

Report

Posted on by Tommy G

355

Hello,

Recently at my workplace we implemented eConnect to integrate general ledger batches into GP. However, in the past week we have had (3) instances of users encountering duplicate key issues when attempting to save their batches in GP. In one instance, the error message was 'A save operation on table GL_TRX_HDR_WORK has created a duplicate key.' The accompanying error message said, 'Cannot insert duplicate key row in object dbo.GL10000 with unique index AK2GL10000. The duplicate key value is (84584).

We are not aware of these errors occurring prior to implementing eConnect. I have removed the C# code that gets the next journal number from GP, allowing eConnect to automatically handle it. I am not convinced that eConnect is causing the problem, but it is interesting that these errors have occurred since we put eConnect in place. Does eConnect make this concurrency issue more prone to happen? Just wondering if anyone has encountered this from an eConnect perspective, and what they did to fix it?

Thanks!

Tom

*This post is locked for comments

I have the same question (0)

All responses (24)

Answers (0)

Suggested answer

steveendow 2,281 on at

Like (0)

Report
Hi Tom,

How is your C# code getting / issuing your JE numbers?

This is the stored procedure I use and it works well. I've generated many thousands of JE numbers with it and never had an issue with duplicates.

public int GetNextJENumber() { string database = Properties.Settings.Default.gpDatabase; string commandText = "taGetNextJournalEntry"; SqlParameter[] sqlParameters = new SqlParameter[3]; sqlParameters[0] = new SqlParameter("@I_vInc_Dec", System.Data.SqlDbType.TinyInt); sqlParameters[0].Value = 1; sqlParameters[1] = new SqlParameter("@O_vJournalEntryNumber", System.Data.SqlDbType.Char, 13); sqlParameters[1].Direction = ParameterDirection.Output; sqlParameters[1].Value = 1; sqlParameters[2] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int); sqlParameters[2].Direction = ParameterDirection.Output; sqlParameters[2].Value = 0; int nextJE = 0; int recordCount = ExecuteNonQuery(database, CommandType.StoredProcedure, commandText, ref sqlParameters); nextJE = Convert.ToInt32(sqlParameters[1].Value.ToString()); return nextJE; }

Was this reply helpful? Yes No
Tommy G 355 on at

Like (0)

Report

Hi Steve,

Thanks for your response. I removed the code that assigns the next journal number and let EConnect handle it on its own. Do you think its preferable to handle it programmatically?

Regards,

Tom

Was this reply helpful? Yes No
Suggested answer

sandipdjadhav 18,306 on at

Like (0)

Report

Tommy,

Instead of using eConnect StoreProc you can use eConnect Methods to get Next Document Number. Below is code.

using Microsoft.Dynamics.GP.eConnect;
using Microsoft.Dynamics.GP.eConnect.Serialization;

string sConnectionString = "Your ConnectionString";

string NextJournalEntryNumber = myTransNumber.GetNextGLJournalEntryNumber(IncrementDecrement.Increment, sConnectionString);

return NextJournalEntryNumber;

Same way you can get all other Transactions Next Document Number. - http://sandipdjadhav.blogspot.com/2012/04/next-document-number-web-service-using.html

Thanks

Sandip

Was this reply helpful? Yes No
steveendow 2,281 on at

Like (0)

Report

Hi Tom,

I'm embarrassed to say that I didn't even realize that eConnect could automatically assigned the JRNENTRY number on its own--I had never bothered to try it before. I just tried it, and it appears to work fine.

However, it looks like there one caveat. In my test, the JE numbers were incremented by 2. 5617, 5619, 5621, etc. I don't know why it is doing that, but I have encountered auditors that do not like transaction number sequences with gaps. You may want to check if you are getting sequential numbers, or increments of 2. So that might be one reason to use your own JE numbering.

There could be some situations where you need to know the JE number prior to insert, such as if you use Analytical Accounting, Extender, etc. where you have to tie other data to the JE number and line sequence. But if those aren't relevant, than you have the option to let eConnect do the numbering.

Was this reply helpful? Yes No
steveendow 2,281 on at

Like (0)

Report

Hi Sandip,

I recall that one issue with using the eConnect GetNext methods is that they utilize Windows authentication.

If you only want to use SQL / GP authentication, then the stored procedures would be preferred.

Was this reply helpful? Yes No
Tommy G 355 on at

Like (0)

Report

Steve and Sandip,

A hearty 'thank you' for your responses. Sandip, I originally had something similar to your suggestion (see below), but because a user encountered the duplicate key message on table GL_TRX_HDR_WORK when they attempted to save a batch, I took out the code and let eConnect handle the JRNENTRY assignment:

private int getNextJournalNum()

{

int nextJournalNum = 0;

try

{

GetNextDocNumbers nextDocNumbers = new GetNextDocNumbers();

nextJournalNum = Convert.ToInt32(nextDocNumbers.GetNextGLJournalEntryNumber(IncrementDecrement.Increment, MSDynamicsConnectionString));

}

catch (Exception x)

{

throw x;

}

return nextJournalNum;

}

But after I took out the code, we encountered the same error when a different user attempted to save a newly-created batch. Steve, I'm in the process of implementing your code to see if we get different results.

What is so puzzling is the fact that to our knowledge, we have not encountered the duplicate key message prior to implementing eConnect. Is there a parameter somewhere in GP or in the C# code that needs to be set in order to reserve or lock on an obtained JNENTRY?

Regards,

Tom

Was this reply helpful? Yes No
Tommy G 355 on at

Like (0)

Report

Hi Steve,

In attempting to call 'taGetNextJournalEntry', I get an 'EXECUTE permission was denied on the object 'taGetNextJournalEntry'' error. I would think the eConnect service would have the access to invoke this. What needs to be done, and how, so that eConnect will be able to call this?

Thanks,

Tom

Was this reply helpful? Yes No
steveendow 2,281 on at

Like (0)

Report

Hi Tom,

The security will depend on how you are calling the stored procedure. It is not called by eConnect--it is called by your .NET application. So your .NET app needs access to SQL Server, either through SQL authentication or Windows authentication.

How are you building your SQL connection string? Using SQL auth, or Windows auth?

In either case, the user account that you use needs to either be a member of the DYNGRP database role, or needs EXECUTE permissions on that stored procedure (either through direct permissions or a custom database role).

In my integrations, if it is designed to run using a GP login, the GP user is already a member of the DYNGRP role, so it should have access to the procedure. If my integration is designed to run using Windows authentication, then the windows account is added to the DYNGRP role (i.e. customers often use the same Windows login utilized by the eConnect Service, which must be a member of DYNGRP).

Does that help?

Was this reply helpful? Yes No
Tommy G 355 on at

Like (0)

Report

Hi Steve,

As it turns out, the issue was that I was firing off the executable in our development environment from a desktop DOS window; my account did not have permission to run taGetNextJournalEntry. Our sysadmin guy granted me permission and now the code runs fine.

Correct me if I'm wrong, but it looks like you have a wrapper method for ExecuteNonquery(). Here's my slightly modified version - if you wouldn't mind letting me know if it looks okay:

private int getNextJENumber()

{

String storedProcName = "taGetNextJournalEntry";

int nextJE = 0;

using (SqlConnection GPConnection = new SqlConnection(MSDynamicsConnectionString))

{

GPConnection.Open();

SqlCommand sqlCommand = new SqlCommand(storedProcName, GPConnection);

sqlCommand.CommandType = CommandType.StoredProcedure;

SqlParameter[] sqlParameters = new SqlParameter[3];

sqlParameters[0] = new SqlParameter("@I_vInc_Dec", System.Data.SqlDbType.TinyInt);

sqlParameters[0].Value = 1;

sqlParameters[1] = new SqlParameter("@O_vJournalEntryNumber", System.Data.SqlDbType.Char, 13);

sqlParameters[1].Direction = ParameterDirection.Output;

sqlParameters[1].Value = 1;

sqlParameters[2] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);

sqlParameters[2].Direction = ParameterDirection.Output;

sqlParameters[2].Value = 0;

sqlCommand.Parameters.Add(sqlParameters[0]);

sqlCommand.Parameters.Add(sqlParameters[1]);

sqlCommand.Parameters.Add(sqlParameters[2]);

sqlCommand.ExecuteNonQuery();

nextJE = Convert.ToInt32(sqlParameters[1].Value.ToString());

}

return nextJE;

}

Thanks,

Tom

Was this reply helpful? Yes No
steveendow 2,281 on at

Like (0)

Report
Hi Tom,

Yes, I have an ExecuteNonQuery method in my Data Access class, but your modification looks good.

My data access method basically adds the params just like your code.

public int ExecuteNonQuery(string database, CommandType commandType, string commandText, ref SqlParameter[] sqlParameters) { gpDatabase = database; int rowsAffected = 0; SqlConnection gpConn = new SqlConnection(); try { gpConn = Connection(gpLoginType); SqlCommand gpCommand = new SqlCommand(commandText); gpCommand.Connection = gpConn; gpCommand.CommandType = commandType; if ((commandType == CommandType.StoredProcedure) || (commandType == CommandType.Text)) { if (sqlParameters != null) { foreach (SqlParameter sqlParameter in sqlParameters) { gpCommand.Parameters.Add(sqlParameter); } } } rowsAffected = gpCommand.ExecuteNonQuery(); return rowsAffected; } catch (Exception ex) { throw ex; } finally { gpConn.Close(); } }

Was this reply helpful? Yes No