eConnect Error: Unable to obtain the next note index

A client was using an eConnect load testing tool that I had developed when they encountered this error messsage.

eConnectException
Error Number = 277 
Stored Procedure = taIVTransactionHeaderInsert
Error Description:  Unable to obtain the next note index

And below that was a second error.

Error Number = 3564
Stored Procedure = tasmGetNextNoteIndex
Error Description:  Input parameter contains a NULL parameter

Taking a look at the tasmGetNextNoteIndex procedure in the Dynamics database, I found that the error occurs when either the Company ID or SQL Session ID value is NULL.

A Company ID value would be NULL if the eConnect import was attempting to import into an invalid company database.

So we checked the import configuration file, and sure enough, the database name was incorrect.  It was set to TWO, and the client did not have a TWO database in their test environment.

Once we set the database configuration option to a valid company database, the import ran fine.

Written By Steve Endow

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.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Not an eConnect Bug: taPMTransactionInsert Error 313: Tax table detail does not equal the tax amount

Mea culpa!  After further digging, I found that *I* was the culprit behind this error--not a huge surprise.  I had mistakenly assigned the document number to the VCHRNMBR node for taPMTransactionTaxInsert.  My bad!

=============

A client asked for a modification to an existing AP invoice eConnect import.  They wanted to start importing freight and sales taxes for their AP invoices.  No big deal, right?

So I do a quick test to confirm that there would be no issues bringing in freight and tax.  When I attempted to import a Payables Transaction with sales tax, I received the following eConnect error:

taPMTransactionInsert Error 313: Tax table detail does not equal the tax amount

I figured I did something wrong with the sales tax setup, so I double checked the tax setup and confirmed that I could manually enter the same transaction info I was attempting to import.  Everything looked okay.

But the Error 313 just wouldn't go away.  I assumed I was doing something wrong, perhaps not populating an eConnect property, or setting a value incorrect.  I tried several things, but nothing worked.

I then did a full trace of the transaction insert performed by taPMTransactionInsert, as well as the insert performed by taPMTransactionTaxInsert.  I retrieved the EXEC call and all of the parameters from the trace, confirmed the tax record was being inserted properly into PM10500, and then tested the validation code from taPMTransactionInsert that was throwing the 313 error.  Everything looked fine.  But obviously something wasn't working properly when the process was called by eConnect.

After Googling the error several times and checking various forums, I finally tried searching the GP Partner Forum.  Behold, I found a post from May 2011 where a poor soul was experiencing the same problem with eConnect 10.

https://community.dynamics.com/gp/f/32/p/55151/213635.aspx#213635

Miraculously, he had discovered that if you set the document number and voucher number to the same value, eConnect will successfully import the AP invoice.  But if the document number and voucher number are different values (which they normally are), eConnect will return the 313 error.  (David C., hats off to you for figuring that one out!)

Skeptical, I modified my import to set the document number value to be the voucher number.  And to my surprise, the transaction imported without error.  So it seems that this is an eConnect bug that has been tucked away for at least 2 years.   So I then incorrectly started to think that this was a bug.

I've glanced at the eConnect stored procedure code to see how this workaround could possibly solve the problem, but it isn't obvious where or how the problem might be occurring.

UPDATE: After reviewing the serialized XML, I found the my mistake in my code.

In my test data, I have a voucher number of V0000560, and a DOCNUMBR value of 11.

    < taPMTransactionInsert >
      < BACHNUMB>MXAP0906-13< /BACHNUMB>
      < VCHNUMWK>V0000560< /VCHNUMWK>
      < VENDORID>GHDIN< /VENDORID>
      < DOCNUMBR>11< /DOCNUMBR>
      < DOCTYPE>1< /DOCTYPE>   


But in taPMTransactionTaxInsert, the document number is being sent in for the VCHRNMBR node.

     < taPMTransactionTaxInsert>
        < VENDORID>GHDIN< /VENDORID>
        < VCHRNMBR>11< /VCHRNMBR>
        < DOCTYPE>1< /DOCTYPE>
        < BACHNUMB>MXAP0906-13< /BACHNUMB>
        < TAXDTLID>APTAX< /TAXDTLID>
        < TAXAMNT>1< /TAXAMNT>
        < PCTAXAMT>1< /PCTAXAMT>
        < TDTTXPUR>12.22< /TDTTXPUR>
        < TXDTTPUR>12.22< /TXDTTPUR>
      < /taPMTransactionTaxInsert>
        
     
After reviewing my code one more time, I found that this was my dumb mistake.  I was incorrectly assigning the invoice document number as the voucher number in taPMTransactionTaxInsert.

I'm going to write it up and submit a support case.  No need for a support case!

Written By Steve Endow

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.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Submitting a Microsoft Dynamics Support Case: What to Send

Doug Pitcher, curator of the OFFICIAL 100 most famous, awesome and totally influential Dynamics people for 2013, has an excellent article on Interacting with Microsoft Dynamics Support.

In his post, Doug offers some excellent information regarding how to handle support cases, and some great tips on how to save time and frustration when you are going through the support process.  And when Doug speaks, you should listen.

Related to Doug's topic of working with Dynamics Support, one thing that I like to do is "front load" the support case as much as possible.  By that, I mean that when I submit a support case, I want to have clear documentation of the issue, as well as full documentation of everything I have done to try and troubleshoot the issue, including screen shots, sample data, error messages, data files, etc.

When I submit the support case, I include a high level version of this info, describing the issue and what we have already tried.  But, I also document everything in great detail in a Word document and always attach the Word document to the case.

So far with this technique, I have always gone straight to the escalation engineers with the problems that I have encountered.  This would seem to indicate that it is saving me time avoiding the basic questions that a first level support tech might ask--but it might also be due to the fact that most of my support issues are eConnect related.

Here is an example of an old support case, and below is the Word document that I attached.

We developed a GP 9 AddIn DLL that uses eConnect to import a payroll batch.  The GP 9 AddIn has been working fine for many months.

The AddIn was recently upgraded to GP 2010.  No modifications were made to the AddIn other than to upgrade to GP 2010 and eConnect 2010.

The AddIn works fine on the development server and TWO database.

When installed the AddIn in the client's test environment, the AddIn receives the following error when trying to import a payroll batch:

The stored procedure 'taCreatePayrollBatchHeaderInsert' doesn't exist.

We are able to reproduce the issue on two different client servers, so it does not appear to be computer-specific.

To troubleshoot the issue, we added debugging code to the AddIn to display the SQL connection string that is being passed to eConnect.

The server name and database name in the connection string appears to be correct.

We also used the Direct Document Sender to submit the payroll transaction XML directly to eConnect, and we receive the same error message:

Integrated Security=SSPI;Persist Security Info=

False;User ID=sa;Initial Catalog=ORION;Data Source=GreatPlains;Packet Size=4096

Microsoft.Dynamics.GP.eConnect.eConnectException: The stored procedure 'taCreatePayrollBatchHeaderInsert' doesn't exist.

We have verified the following on the SQL Server:

1. The stored procedure does exist in the client's GP 2010 database
2. The eConnect domain user is a SQL user assigned to the database and assigned to the DYNGRP role
3. The DYNGRP role does include EXEC access to the stored procedure

The client environment and development environment both have eConnect version 11.0.1812.0 installed (June 2011 hotfix).

Please see the attached Word document with screen shots, along with the sample XML file that we are submitting to eConnect.

Please let us know if you require any additional information.

Here is a link to the Word document that I submitted with the case.

Update:  For any die-hard eConnect geeks who were left hanging by the support case I posted above, here is the very obscure solution.

The problem was that when the client setup the SQL Server login for eConnect, they set the Default Schema value to equal the user name.  The Default Schema should have been left as dbo for all databases.

Changing the default schema back to dbo did not resolve the problem.  I had to delete the eConnect schema from all databases, and then delete the eConnect SQL login.  I then re-created the eConnect login and users.

Written By Steve Endow

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.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Word Templates and Report Sections

Just when you think you have something figured out, the Microsoft Word templates (with Microsoft Dynamics GP) will throw you a curve ball.  Last week, a client was having issues with the Purchase Order Blank template.  It had previously been working fine, but they wanted to print both the vendor item number and the company's item number on the form.  They added the appropriate field without issue, but when they printed the form-- it didn't show up.

So, we did the normal things...

1. Made sure the field printed fine on the report writer report (it did)
2. Removed and re-added the XML source to the Word template
3. Removed and re-added the field to template
4. Added static text to the template to make sure we were printing the right template (we were)

Urgh.  And then I noticed something odd about how the report was printing.  It seemed like none of the other sections of the report (that normally print below the item number) were actually printing.  Instead, it was printing a header line a second time.  I noticed this because the payment terms were being duplicated, printing both above and below the line items.  Odd.

But then I remembered a blog post that I had read about making sure that fields were associated with the section that are printing in.  When you are looking at the field pane, you will notice that fields are displayed based on the section (this is derived from the report writer report itself, so wherever the field is on the standard report- that is where it shows in the field pane).

So, to ensure that fields are in the correct sections on the template, you can click on Design Mode (located on the Developer pane).  It switches the view to something like this...

What I noticed when I did this is that the shipping method field in the header row was actually from the Comment 3 section.  Notice that all of the other fields in that section are from the Report  Header.  So I removed the errant field, and the PO worked great!  Then we added the field back, but this time we made sure we added the Shipping Method fields from the Report Header section.

In a standard report writer report, we can drag fields in to any section on the report.  But when working with the Word templates, it seems like you want to add fields to the section that they are defined in.  So if the field is not available within the section where you want to add it, you would want to add the field to that section on the standard report writer report and then delete/re-add the XML source to update the field list.

I just wanted to share the Design View option, as this is a quick way to check and make sure your fields align to the sections and won't cause any undesired sorting issues when you generate the template.

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.

Integration Manager 2010 error: Input string was not in a correct format

By Steve Endow

As I've written previously, I am not a fan of Integration Manager 2010.  It is so flaky, buggy, and unstable that I consider it barely usable at times.  And today I encountered yet another error that wasted an hour of my life. 

One of my clients has a pretty simple AP invoice import in Integration Manager 2010.

They have been using it for a few weeks, but today the integration didn't work.  Used to work fine.  Suddenly it doesn't work.

As soon as they clicked the Run button, the following error would appear.

"The destination could not be initialized due to the following problem:
- Input string was not in a correct format.
Input string was not in a correct format."

If you search for this error message, you will likely find an article indicating that this is a Windows permission issue or a registry permission issue.  The client found that article and tried it, but it didn't resolve the error.  Run as Administrator didn't help.  UAC is not enabled.  Registry permissions were checked.

I then reviewed all of the data, thinking maybe there was a bad field or row.  But the data was fine.  I even made a sample data file consisting of just one record, but the error persisted.

I won't bore you with the dozen other things I tried that did not resolve the issue--let's just say that I tried EVERYTHING, including recreating the integration from scratch.  No joy.

The latest IM service pack was not installed, so I got SP3 ready for installation.  Because IM is installed on the client's Terminal Server where other users run GP, we were not able to install the service pack initially--the service pack updates the IM.dic file, which is locked by Dynamics GP.  Lovely.

Finally, in the evening when all users were out of GP, I installed SP3 and magically, the error went away.

Why did the integration work for several weeks and then stop?  Why did SP3 fix the problem if it isn't documented as a known issue with a prior version?  Who knows.  It is Integration Manager 2010, after all.

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

eConnect 2010 GL JE Import Slows up to 300% as Work Table Fills

By Steve Endow


UPDATE: October 2018:  I finally performed some additional testing to see if I could reproduce this issue and narrow down the cause.  I tested importing standard GL JEs, with no AA data, and I was unable to observe any performance decrease, even after having over 1,000 JEs with over 120,000 JE lines in the GL10001 table.  Given this observation, my current guess is that it may have been the AA portion of the import that is causing the eConnect import to slow as JE records accumulate.  This is consistent with other performance issues I've observed importing transactions with AA data using eConnect.  The performance of an eConnect import with AA data is usually significantly slower than the same import without AA data.

UPDATE 2: October 2018: I performed more testing on a fresh install of GP 2018 R2 running on SQL Server 2017.  After numerous imports of JEs with AA data, I am unable to reproduce the obvious performance issue documented below.  Importing multiple single JEs with 2,000 lines each did not result in slower times--in fact the times improved slightly after 5 JEs.  So this seems like it must be an environment or version specific issue that would require specific analysis on the SQL Server where the problem occurs.



I received an inquiry from a client regarding the performance of an eConnect GL journal entry import that I developed for them.  They were told that if they changed a setting in Dynamics GP, it would make eConnect faster.  I had never heard this claim before, so I figured I would give it a try and either prove or disprove it, at least when testing on one of my test servers.

Before I tested changing the Dynamics GP setting, I wanted to get a performance baseline of my JE import and record numerous import times to get an average.

I fired up my eConnect JE import that I developed for the client and added a few timers to the code.  I used the .NET System.Diagnostics.Stopwatch class to determine the time that it took for eConnect to import the journal entry transaction. 

I had a test data file that has 1,710 lines in a single JE and also has Analytical Accounting codes that my application is importing.  My import sends all 1,710 lines, plus AA data, to eConnect as a single XML document.

I ran the import several times and started recording the eConnect import durations.  After 5 imports, I was a little puzzled by the times.  They ranged from 28 seconds to 43 seconds, which seemed like a pretty big variance for the exact same JE data.  Obviously eConnect was getting slower each time the import was run.

28.71

35.35

41.47

41.93

43.03

I thought maybe it's the eConnect service holding on to SQL connections, so I restarted the eConnect service.  That didn't seem to make a difference at first, but after 7 more test imports I saw that the times suddenly dropped and then went back up.  I now had times that ranged from 15 seconds to 45 seconds, an even bigger variance that before!

42.89

27.94

15.64

16.95

31.22

33.00

45.20

Very odd, and highly suspect.  I found it very difficult to believe that the same JE data could take 15 seconds to import, and also take 45 seconds to import.  And why did the numbers decrease, and then increase again?

Then I tried restarting the SQL Server service, thinking that maybe SQL was somehow slowing down on certain operations with multiple imports.  This theory doesn't make any sense, but I wanted to rule it out.  After restarting SQL, the first time was slower, which makes sense, but subsequent imports were pretty consistent around 45 seconds.  I also tried restarting eConnect again, but the numbers stayed consistent.

56.50

43.35

43.35

45.24

45.18

44.45

46.11

43.20

So now the numbers were consistent at about 45 seconds, but why?  How did I get 15 and 16 seconds previously, which is a huge variance?

After puzzling over it for a minute, I tried one more test.  I deleted all of the unposted test JEs that I had imported so that there were no JEs in the GL work table.

Like magic, the first JE imported in under 14 seconds.

13.65

17.87

23.37

32.73

41.09

Each subsequent JE took a few seconds longer as the import time grew to 40+ seconds.

I then deleted all of the JEs and started the import again.  And again, the time dropped to under 15 seconds.

14.66

16.96

23.84

33.15

37.74

45.68

And I deleted the JEs again, just to make sure it was consistent.  And it was.

13.48

16.84

I found this to be very interesting and surprising.  My interpretation of this performance data is that the eConnect JE import performance is highly dependent upon the contents of the GL JE work tables.  Each JE that was imported added 1,710 lines to that table, and as those lines accumulated, the eConnect JE insert operation slowed. 

And my results where the times decreased and increased again were likely because I deleted all of the imported JEs in the middle of that particular test run.

I haven't looked into the eConnect stored procedures, so I don't know all that is going on, but my guess is that it probably has to do with SQL indexes and perhaps a query in the eConnect procedure that is not fully optimized.  I find it unlikely that SQL Server itself would have degraded performance after inserting a meager 6,000 records into a database table, so my suspicion is that it's a query optimization issue.  eConnect stored procedures are typically very complex and sometimes several thousand lines long, so it wouldn't surprise me if there were a few queries that weren't optimized, or can't be optimized for all scenarios.

And to be clear, this is a very specific test on one of my development servers, so I can't say that this applies in other environments, with other transaction types, or even with different types of JEs.  But my test results seem very consistent and highly reproducible, so it's something I'm going to keep in mind for other clients or projects that require high volume integrations.

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