eConnect 2010 Error: The transaction has aborted

I have a client for which I have developed over a dozen eConnect integrations.  They have been running on GP 9 for several years, and since the client recently upgraded to GP 2010, I upgraded them all a few months ago.

Although the upgraded integrations work properly, one issue that we have observed is that several of them receive an error occasionally that simply says "The transaction has aborted."

This error is occurring with multiple imports for different entities and transaction types.  For example, the error occurs when importing an inventory item, an AR cash receipt, an AR payment application, and a SOP invoice.

The error occurs intermittently and inconsistently, and the error does not seem to be related to any specific entity or transaction.  For example, when importing 2,577 inventory items, one item received the error.  If the same file is re-imported, a different item will receive the transaction aborted error.

All of the integrations are submitting each transaction individually, and each of the integrations usually processes thousands of transactions at a time.

If you check your eConnect Event Log, you will see two different messages related to the transaction aborted error.

Example 1:

Action:  Create Transaction

Current User Name:

Input parameters:

Exception type:  Microsoft.Dynamics.GP.eConnect.eConnectException

Exception message:  The transaction has aborted.

Stack Trace:

   at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateTransactionEntity(String connectionString, String xml)

   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction)

   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ProcessEntityImport(String connectionString, String sXML, Boolean isTransaction)

   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateTransactionEntity(String ConnectionString, String sXML)

Example 2:

Action: Service Create Transaction

Current User Name:  DELIVERYAGENT\greatplains

Input parameters:

Exception type:  System.Transactions.TransactionAbortedException

Exception message:  The transaction has aborted.

Stack Trace:

   at System.Transactions.TransactionStateAborted.EndCommit(InternalTransaction tx)

   at System.Transactions.CommittableTransaction.Commit()

   at System.Transactions.TransactionScope.InternalDispose()

   at System.Transactions.TransactionScope.Dispose()

   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction)

   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ProcessEntityImport(String connectionString, String sXML, Boolean isTransaction)

   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateTransactionEntity(String ConnectionString, String sXML)

   at Microsoft.Dynamics.GP.eConnect.Service.CreateTransactionEntity(String ConnectionString, String sXML)

Exception type:

System.InvalidOperationException

Exception message:

The requested operation cannot be completed because the connection has been broken.

 After trying several tests and various troubleshooting at the client, we submitted a support case.  Aaron at Microsoft called me promptly to review the details, and after searching through his notes and databases, found that this appears to be an issue with eConnect 2010 that was resolved in Service Pack 2.

The client currently has eConnect 2010 Service Pack 1 installed, so I'm going to install the Post-SP2 June 2011 Hotfix (KB 2561289), which will bring eConnect up to version 11.0.1812, to see if that resolves the issue.

I'll add an update next week as to whether this resolves the issue.


Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

How to Serialize eConnect XML - Properly

I'm posting this for my own reference because I can never remember the full syntax and I regularly have to look it up.

I think I've seen at least 3 or 4 different code samples showing how to serialize eConnect XML in .NET.

One version, which I think is actually based on sample eConnect code that Microsoft provided years ago, writes the XML out to a file on the hard drive, then reads it back into memory.  The first time I saw this my eyes rolled in disbelief.  Clearly that is a terrible idea and is completely unnecessary.

The other versions, which I have used over the last few years, used in-memory techniques to serialize the XML using a combinations of a MemoryStream, XmlTextReader, and StringBuilder.  Although these worked fine, the process to use an XmlTextReader and StringBuilder never seemed ideal.

The best example I've seen so far is the one that Brian Prince posted on the MBS Guru blog back in December 2010.  The XML Document approach is simple and very clean, and it is the technique I've been using ever since he wrote about it.

Here is Brian's approach:

eConnectType eConnect = new eConnectType();

MemoryStream memoryStream = new MemoryStream();
XmlSerializer xmlSerializer = new XmlSerializer(eConnect.GetType());
xmlSerializer.Serialize(memoryStream, eConnect);
memoryStream.Position = 0;

XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(memoryStream);
memoryStream.Close();

econnectMethods.eConnect_EntryPoint(

connectionString, EnumTypes.ConnectionStringType.SqlClient, 

xmlDocument.OuterXml, EnumTypes.SchemaValidationType.None,  

string.Empty);

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

Little Tricks With Report Writer Package Files

I remember when I figured out that I could open, in Notepad, a package file (Microsoft Dynamics GP-Tools-Customize-Customization Maintenance-Export) containing a report. I thought, hey, that's cool.  And then I figured out that I could copy the "guts" of a report from one package to another in order to do something like....make the SOP Other Invoice Form look exactly like the SOP Blank Invoice Form.  With that, I thought, hey, that is really cool!  So, I thought I had it all figured out-- easy enough to do as long as the tables of the reports were pretty much the same. 

But, then, my coworker Cindy Boersma asked if I knew I could make the SOP Blank History Invoice Form look exactly like the SOP Blank Invoice Form using the same method.  Holy cow!  No, I did not, although it all makes logical sense.  Since all the information, including table names, is in the package file.  So, here are the basic steps, adapted from Cindy's notes.  As always, please make sure you either keep backups of the package files or the reports dictionary before attempting any of these steps to avoid the loss of your modifications :)

1.  Export the report you want to use as the "basis" of the other report (Microsoft Dynamics GP-Tools-Customize-Customization Maintenance-Export)

2.  Open the package file created using Notepad

3.  At the top of the package file, you should see the component and report name as highlighted in the screenshot below

4.  Simply edit these two (2) references to the report name to match the report that you wish to copy the package to (for example, SOP Blank History Invoice Form"). Check Report Writer if you have issues finding the report name (it needs to be the name used in Report Writer for the report, which is also the name that appears across on the top border of the Screen Output window when you print a report to the screen-- NOT the name that prints on the report itself, this is not always the report writer name).

5.  Next, if the report you are copying to uses different tables (e.g., the SOP Blank History Invoice Form uses the SOP history tables while the SOP Blank Invoice Form uses the SOP work tables), you will need to use Edit-Replace in Notepad to make the substitutions.  Now, in this case, it assumes that the field names in the tables are the same.  So we find/replace SOP_LINE_WORK with SOP_LINE_HIST and SOP_HDR_WORK with SOP_HDR_HIST.  And here are the results...

Not only did we replace the component and report identifiers, but the find/replace also updated the table references in the report package as well.

6.  Now, we can import our package file back in to GP, Microsoft Dynamics GP-Tools-Customize-Customization Maintenance-Import.  Remember, no other users can be in Dynamics GP when you complete this process (assuming you are accessing a shared reports dictionary).

And there you go.  Creating a historical invoice that looks identical to the standard invoice in minutes! So cool.  And why I love this job-- learn something new (even if it was sooo obvious) every day!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Using Custom Icons to Differentate GP Versions

I develop Dynamics GP customizations and integrations for alot of different customers, and currently they are running GP 9, GP 10, GP 2010, and GP 2010 R2 (thankfully nothing older than GP 9!).  Most use only the core GP modules, while some use Project Accounting, and a few use Analytical Accounting.  Because of this, I have to maintain several different versions of GP on multiple virtual servers.

This is a pretty standard situation, but one small annoyance I have had is that the GP 10 icon is the same as the GP 2010 icon.  So if I have both versions installed on a server, I have to try and differentiate the icons on my start menu, task bar, and desktop so that I can tell which is GP 10 versus 2010.  And then I have one machine with GP 2010 and a separate install of GP 2010 R2, so that is also fun.

I don't know why it didn't occur to me earlier, but tonight I thought I would try and create custom icons for each version of GP.  If you are using Visual Studio 2008 or Visual Studio 2010, it's a pretty simple process.

I already have a standard Dynamics GP icon file that I created several years ago, or if you want to create your own, you can use an icon editor or extractor.

I just made a copy of the icon file, opened it in Visual Studio, and then added a basic "2010 R2" to the 32x32 icon, and just an "R2" to the 24x24 and 16x16 icons.  I've made a similar one for GP 10.  And now I can use the same technique to create one for my separate Analytical Accounting, Project Accounting, and Manufacturing installs.

Obviously desktop icons have a text label, but the custom icons are very helpful for the icons in the system tray.

Unfortunately, while writing this, I just discovered one very big limitation.  The Visual Studio 2010 icon editor does not support editing of 32-bit (color depth) icons.  Since I am using my custom icons on a virtual server that I access via RDP, the 16-bit icons are fine for me.  But if you want to make 32-bit versions, you'll need a real icon editor, such as the very good Axialis Icon Workshop, or perhaps there is a free product available.

It appears that Axialis Icon Workshop includes a Visual Studio 2010 Add In, so that's a nice feature if you do much icon editing.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

Credit Limits and Sales Process Holds

I just finished up a post for the BKD blog on sales process holds, and I thought I might spend a little time here on one the neat side benefits of using process holds.  For those of you that are not familiar with them, sales process holds are user-definable holds that you can assign to Sales Order Processing transactions (Quotes, Orders, Invoices, etc) that can prevent the document from being printed, posted, fulfilled, and/or transferred.  These holds are quite handy, as they can be applied to ranges of documents, individual documents, or even default when certain document types are used.

There is a bit of a "bonus" to using sales process holds in tandem with customer credit limits. You can actually specify a process hold to be automatically assigned to a document when the customer exceeds their credit limit.  For example, let's say that your credit manager has to review all documents that exceed a customer's credit limit.  By assigning the process hold automatically, the credit manager could complete his or her review and remove the holds on those documents that can continue to be processed. 

The setup for this feature is quite simple.  Start by setting up the process hold, Microsoft Dynamics GP-Tools-Setup-Sales-Process Holds.

Enter the Process Hold ID and Description.  Specify a Password if you want users to enter a password in order to remove the hold.  And then mark the items you want to "Apply Hold To".  Click Save.

Next, you need to specify the hold on the specify invoice and order types that should automatically have a credit limit hold applied.  Go to Microsoft Dynamics GP-Tools-Setup-Sales-Sales Order Processing Setup.  Click the Sales Document Setup button and choose either Invoice or Order.



Select the Order or Invoice ID, and then select the appropriate process hold in the Credit Limit Hold ID field.  Click Save.  Now, when you enter an order using this Order ID and the customer exceeds their credit limit, the process hold specified will be automatically assigned to the order.  In order for this functionality to work, you CANNOT have a password specified for the "Exceed Credit Limit" option in Receivables Management Setup (Microsoft Dynamics GP-Tools-Setup-Sales-Receivables).

When entering an order that exceeds the customer's credit limit, a warning will be displayed.  Choose Continue, and the hold will automatically be applied to the order when it is saved.

Note that the CREDIT process hold has automatically been applied.  How easy is that (to borrow from the fabulous Ina Garten)?

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Mysterious eConnect Error: The stored procedure does not exist. Watch your schemas!

I recently upgraded a Dynamics GP Visual Studio Tools AddIn from GP 9 from GP 2010.  The AddIn reviews some GP transactions, performs some calculations, and then inserts new payroll transactions using eConnect.

The upgrade was straightforward and worked fine on my development server.  It installed smoothly on the client server and looked like it was working...right up to the point where the following eConnect error occurred:

The stored procedure 'taCreatePayrollBatchHeaderInsert' doesn't exist.

In this particular case, the AddIn was inserting an entire payroll batch, but the specific stored procedure in the error could have been any eConnect procedure.

I've never seen that type of error before, so I started with the obvious--I checked the SQL database to see if the procedure existed.  It definitely did exist in the database.

I then thought that maybe it was a permission issue, so I confirmed that the eConnect domain user was setup as a SQL Login, and that an eConnect user was properly setup for the company database.  I even checked the DYNGRP role to make sure that the stored procedure was listed with EXEC permissions.

Everything looked fine.

I did a little research on the error, and saw a post that recommended checking the database connection string.  If the database was not specified properly, and the import was somehow referencing the master database or even DYNAMICS, naturally that would cause this type of error.  So I added some debugging code, but the connection string looked fine--the proper GP company database was being referenced.

I was running out of options, so I dug out the Direct Document Sender, a tool used by GP support to troubleshoot eConnect issues.  It allows you to specify the database connection info, select an XML file containing the eConnect data to be imported, and then it sends the XML document off to eConnect.  This helps to rule out any bugs in your custom integration.

Interestingly, the Direct Document Sender received the same error message indicating that the stored procedure did not exist.  Although it helped to rule out the VS Tools AddIn and its eConnect code, and it helped me realize that there was a lower level issue with the client's environment, I still didn't have any specific clues as to the real cause.  I was thinking that maybe it was a problem with the eConnect procedures in the client's databases, or that perhaps something did not upgrade properly from GP 9 to GP 2010.

As a long shot, I ran the dbmaintenance.exe utility in the Dynamics GP application directory to recreate the Functions and Stored Procedures on the company database.  That completed successfully, but when I ran the integration and the Direct Document Sender, both still received the same error.

As a final test, I wanted to get a baseline to test against in the client's environment, so I created the TWO / Fabrikam test company database.  GP Utilities setup the company, and I then went into SQL Server Management Studio and added the eConnect login as a user on the TWO database.

To perform my test with the Direct Document Sender (which uses Windows authentication to access the SQL Server), I logged into the client's server as the eConnect domain user, just to make sure that no other user account variables were involved.  I launched the Sender and started to setup the connection string, but when I went to select the database, TWO was not listed.  I verified everything several more times, but TWO didn't show up.

I then logged out of the server and logged back in as the Administrator.  When I did that, the TWO database did show up.  Puzzled, I checked and rechecked the eConnect user for the TWO database, but it looked fine.  For some reason I couldn't get TWO to appear in the database list.

Since this didn't make any sense, I went back to the SQL Server Login window for the eConnect user and checked the settings for at least the fifth time.  While I was staring at the window, something caught my attention.

I scrolled to the right and saw that the user record for the TWO database had a Default Schema of dbo, but the records for the company databases had the eConnect user listed as the Default Schema.

The light bulb instantly came on and it all made sense.  Well almost all.

If you aren't familiar with SQL Server Schemas, you should definitely read about them just enough to understand what they are.   They are not used by GP, so you don't need to be an expert; however, because they are not used by GP, if someone happens to use them in a GP database, it will likely cause problems, so you will need to know just enough to identify, understand, and resolve the issue.

Because the eConnect SQL user was assigned a Default Schema other than dbo, when eConnect logged into the database and tried to execute the payroll stored procedure, it literally didn't exist.  In fact nothing existed--no tables, no stored procedures, nothing.

I tried changing the Default Schema for the user back to dbo, but for some reason that didn't solve the problem.  I had to delete the eConnect user and eConnect schema completely from all databases, and delete the eConnect login for good measure, and then recreate the login and users with the proper dbo default schema.

Once I did that, the TWO database showed up in Direct Document Sender, and sure enough, eConnect worked just fine.  The AddIn immediately started working properly and was able to import payroll batches.

I'm not entirely sure how the eConnect schema got created on the SQL Server or how it was assigned as the Default Schema for the eConnect user on the company databases.  And I don't understand why changing the Default Schema back to dbo didn't fix the problem.  But in the end, it was easy enough to remove the users completely and recreate them.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

eConnect 2010: CreateEntity vs. CreateTransactionEntity

If you develop eConnect integrations, you probably know by now that with eConnect 2010, Microsoft changed the method used to submit transactions to eConnect.

With GP 10 and earlier, there was a single method called "eConnect_EntryPoint".  Simple and easy, it accepted all of the eConnect document types.

With GP 2010, there are now two different methods.  There is now a method called "CreateEntity", and a second called "CreateTransactionEntity".

Although I have one guess, I haven't asked and don't really know why this change was made, or what the rationale was behind implementing two methods--I just hope that some thought was put into it, and I hope that there was some benefit behind the scenes.

I hope that because it's slightly annoying to have to make two different calls now.  By no means a big deal, but just slightly annoying, especially if you are upgrading from eConnect 9 or 10.  You have to review your code and make sure that your code to create customers calls CreateEntity, and your code that imports invoices calls CreateTransactionEntity.

The eConnect 2010 Programmer's Guide provides this mediocre and slightly vague explanation the two new methods:

CreateEntity:  Creates a record using information from an eConnect XML document. Use CreateEntity to add data entities like customers or vendors. You specify the type of record to create with a parameter that represents an eConnect XML document. If the operation succeeds, the method returns True as a boolean value.

CreateTransactionEntity:  Create a transaction using information from an eConnect XML document. Use CreateTransactionEntity for transaction documents like sales orders or inventory transactions. You specify the type of transaction to create with a parameter that represents an eConnect XML document. If the operation succeeds, the method returns an XML string that represents the eConnect XML document that was created.

So, this is fine for obvious record types like a vendor vs. a voucher, but what if I create a new batch using SMTransactionBatchType?  Is a batch an "Entity", or a "TransactionEntity"?  That starts to become an existential question, because the current eConnect documentation offers no guidance when discussing each of the transaction types.  You're stuck trying both methods to figure out which one to use (hint--the one that causes your app to crash is the wrong one). 

With the griping aside, there is one last thing to pay attention to.  Okay, it's really one last gripe.

Unless you paid very careful attention, you probably missed one critical difference between CreateEntity and CreateTransactionEntity.  Do you know what it is?

They have different names obviously, but they both accept the same parameters, and they both seem to function in the same way.  Read the last sentence of each of the paragraphs from the help file quoted above.

"If the operation succeeds, the method returns True as a boolean value."

"If the operation succeeds, the method returns an XML string that represents the eConnect XML document that was created."

WHY????

Despite having done at least a dozen eConnect 2010 integrations, I apparently hadn't paid close enough attention to that, so I discovered this the hard way:  By having to submit a support case.  I had upgraded an integration from GP 10, and when I had to create my two eConnect methods, I created a CreateEntity method, then copied that to do a CreateTransactionEntity method.  I just changed the eConnect method name and that was it.  Except that it wasn't.

I forgot to change my return variable from a bool to a string.  When you make that mistake, you will get this error:

Unexpected error in InsertTransaction: Conversion from string " < eConnect xmlns:xsi="http://www." to type 'Boolean' is not valid.

After inserting the transaction, eConnect returns the XML string, but the code fails when it tries to send that into your boolean return variable.

Strangely, the .NET compiler didn't catch the mistake, so I thank Chris Roehrich at GP support for pointing out my mistake.

UPDATE:  I just figured out why the .NET compiler didn't catch the problem.  This particular eConnect upgrade was the first time I had upgraded an old VB eConnect integration to GP 2010.  It seems that the VB compiler does not detect the type mismatch.  I just tried the same thing in C#, and the compiler picks it up right away.  All of my previous eConnect 2010 integrations and upgrades have been in C#, which is why I never had the issue before.

Why they couldn't just make both methods have the same return type...well, again, I hope there was a some thought put into it and there is a reason and a benefit...

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com