Wednesday, April 16, 2014

eConnect 2010 / 2013 error: Procedure or function 'ta___________' expects parameter '@________, which was not supplied

By Steve Endow

It can be really strange how some Dynamics GP issues come in clusters.  One week, all will be quiet, but the next week, you'll get multiple calls, often regarding the same issue.

Last Saturday, I received an email asking for help with an eConnect error.  An existing POP Receipt integration worked fine with GP 10.0, but once it was upgraded to GP 2013, the import would get the following error when trying to submit serialized XML to eConnect:

Procedure or function 'taPopRctLotInsert' expects parameter '@I_vPOPRCTNM', which was not supplied

For an existing integration that was working fine to have this error suddenly occur after being upgraded is pretty odd.  Obviously you wouldn't submit a lot record without assigning the value for the POP receipt with which it is associated.

I offered one or two guesses as to the possible cause, but those didn't pan out.  But the next day I received an update explaining that they had found the issue.  When they reviewed the serialized XML prior to submission, they found an extra node:

< taPopRcptLotInsert xsi:nil="true" / >

(spaces added to the end caps of the node to keep it from being stripped out by Blogger)

The exact same integration worked fine with GP 10, but has this problem with GP 2013.  It would seem that something changed with the eConnect Serialization library, and one might assume that this is an "eConnect bug".

But not so fast.  I've developed several POP Receipt imports that have upgraded from GP 10 to GP 2010 and 2013, and have not run into this issue.  So I had a hard time imagining that this was a fundamental bug in the POP Receipt eConnect serialization.  In this case, the developer implemented a workaround to strip out the extra "nil" node, and that solved his problem.  But I was puzzled--something didn't seem right.

So this morning I glanced at the GP forum posts, and saw a post titled "eConnect 2010,2013 Bug".  Posts like that always catch my attention, so I reviewed it.

Interestingly, this user was experiencing a very similar issue.  An eConnect integration that worked fine on GP 10 suddenly has a similar error when upgraded to GP 2013.

Procedure or function 'taPMDistribution' expects parameter '@I_vDOCTYPE', which was not supplied

(I'm guessing that the error message was mistyped and actually read "which was not supplied", but perhaps the error message does vary between eConnect methods)

Finding it very odd that I have seen this error twice in less than a week, I tried searching for this particular version, and I found gold.

On this thread, someone else had the same error.  Coincidentally, the last post references one of my eConnect blog articles, but at the bottom, it appears that he traced the issue to how he was dimensioning his taPMDistribution_Items array.

In VB, you can use the ReDim Preserve command to dynamically increase the size of your eConnect arrays as you build them.  Well, if you have an incorrect counter, or forget that you are working with a zero based array, you can easily end up dimensioning your array to be one larger than what it should be.  When you do that, an extra node gets serialized and gets output with the "nil" value.

So here is my theory:  eConnect 10 used to dispose of these extra "nil" nodes during serialization, but eConnect 2010 (and 2013) do not ignore them, and dutifully output them.  So when someone upgrades their integration from GP 10 to 2013, an integration that used to work perfectly may get an error, due to what they will believe is an "eConnect bug".

I also theorize that this issue will only affect integrations that were written in Visual Basic, as VB has the ReDim Preserve command, which is not available in C#.  I speculate that these VB developers are really running into a bug in their integration code that improperly dimensions the array to be one value larger than it should be.

In C#, this is probably very unlikely, but I suppose if arrays were used, someone could still incorrectly dimension the array.  I personally use List<> objects for my eConnect array, and there is no dimensioning required, so I should never experience this particular issue.

I've responded to the forum post, so I'm interested to see if they confirm that they are using VB and if they did improperly dimension their array.

If anyone else thinks this theory makes sense, or has a different interpretation or theory, I'm interested to hear about it!

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

Monday, April 14, 2014

Microsoft Project Auto Schedule option

By Steve Endow

I recently started a new project that looked like it would require a bit of planning between at least two different client teams, the GP partner, and two contractors.  I initially created an Excel file to start recording tasks, dates, and estimated hours, but it quickly became clear that it would be difficult to manage the task list in Excel.  When I added a task or changed an estimated date, I had to manually update other dates and try and figure out dependencies.

I almost never use MS Project, but it has some nice features that make it easier to try and figure out dependencies and timeline.  This project was a good candidate, as it has several dependencies, several scope changes, and a lot of changing dates.

Since I so rarely use Project much, I quickly ran into an annoying behavior when I changed a task date:  Dates for dependent tasks would not update automatically.

A red squiggly line would appear in the date for a dependent task, and the Gantt chart would show dashed lines around the task.

I found the option to "Respect Links", which would fix the task dates and Gantt chart for that item--but it would only update the one item.  The next dependent item would then have the same issue, on down the line.

After seeing this a few times, I searched for the issue and found this nice post of MS Project frequently asked questions.  Seems I'm not the only one with this problem.

The solution is to select the Auto Schedule option instead.

This changes the color and style of the task in the Gantt chart and thankfully has the task dates update automatically on the dependency when an upstream task is modified.

This option is also available in the Task Information window.

Presumably this is obvious to MS Project pros, but it took me a few minutes to track it down.

Happy project planning!

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

Tuesday, April 8, 2014

Search for text in all SQL Server stored procedures

By Steve Endow

A customer emailed me this morning saying they were getting this error when posting some batches in their test environment:

The stored procedure glpPostBatch returned the following results:  DBMS: 0, Microsoft Dynamics GP: 20507

I opened up the glpPostBatch stored procedure in SQL Server Management studio and searched the script, but there was no error number 20507.

Hmmm.  So my guess was that glpPostBatch was calling other procedures (which might themselves be calling other procedures), and one of those sub-procedures was raising error 20507.  Rather than dig through the stored procedure and try and track down the sub-procedure, I figured I should be able to search all stored procedures for the error number 20507.

Thanks to this Stack Overflow post on this very topic, I used this script:

SELECT DISTINCT AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
ON m.object_id=o.object_id
WHERE m.definition Like '%20507%'

In a few seconds, it found that the number was present in a procedure called smAddPostingSecurityRecord.  I then scripted that procedure, and found this code:

select   @sCompanyID = CMPANYID  from  DYNAMICS.dbo.SY01500  (NOLOCK)  where  CMPNYNAM = @I_cCompanyName   
if @@rowcount <> 1  begin  
select @O_iErrorState = 20507  

So it is querying SY01500 for the company record, and if it doesn't get exactly 1 record, it returns that error number.  Makes sense.  But obviously there should always be exactly 1 company record in SY01500, so that doesn't help explain the error.  My only guess is that something is locking or blocking the table, causing the query to fail or return NULL--I'm skeptical that it is returning 0 or 2, but I've asked the client to check the table to confirm the company record is valid.

If that is what is causing the error during batch posting, then my assumption is that there is little I can do to troubleshoot it.  I've been down this road with glpBatchCleanup errors, and have learned that these types of errors are generally best handled by GP support.

Fortunately, the error is only occurring in the customer's test environment and not in their production or UAT environments, so it does appear to be isolated.  I recommended that they try and restore the Dynamics and company databases to see if that helps.

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

Tuesday, April 1, 2014

Finding your Dynamics GP Site Name to Request ISV Registration Keys

By Steve Endow

This may seem pretty basic to many, but it is such a common issue for me that I want to discuss it in the hopes of clarifying it for GP customers.

I sell several ISV "add-on" solutions for Microsoft Dynamics GP.  To allow customers to use trial versions of the software, and ultimately license the software to customers, I generate software license keys.

Like the Dynamics GP license keys, my ISV license keys are based on the "Site Name" that is entered in the Dynamics GP Registration window, under the Tools -> Setup -> System -> Registration menu (or Administration page -> System -> Registration).

This window is where you type the company name that is provided to Microsoft to generate your Dynamics GP license keys.  Microsoft performs some type of hash or calculation with the company name, licensing scheme, and modules to create Dynamics GP license keys that will allow you to use the software and perhaps also additional modules that you have purchased.

When you trial or purchase an ISV "add-on" solution, the ISV typically generates its own separate license key based on the Site Name.  So in my case, when a customer wants to try Post Master Enterprise, I ask that the customer send me their exact Site Name so that I can generate a trial key.

Surprisingly, I regularly have customers who don't know what it means to find their Site Name, have a hard time finding their exact site name, or more commonly, they send me the wrong site name.  

When finding your Dynamics GP Site Name, every single character matters, and the name must be exact.

The following are five different Site Names:

Consolidated Holding Company Inc
Consolidated Holding Company Inc.
Consolidated Holding Company, Inc
Consolidated Holding Company, Inc.
Consolidated Holdings Company, Inc.

The spelling, spaces, commas, and periods matter.  And, I don't know if it matters for the GP reg keys, but for my reg keys, capitalization also matters.

So it matters if you send your ISV a name of "Consolidators Unlimited, Ltd." with a comma and period, or without the comma or period, or with LTD capitalized.

As a side note, I used to ask people to send me a screen shot of their Dynamics GP Help -> About window, as that window shows both the GP version number and the Site Name.  But there is a catch.

The Help -> About window has a limited amount of space to display the Site Name.  So if the customer has a very long site name, the name will be truncated when it is displayed on the About window.  Once I discovered that, I had to stop using the About window as an option.

There is another popular location to find the Dynamics GP site name, and that is in the Microsoft VOICE system where partners can view and manage their registered customers.  When I am working with partners, they typically understand the Site Name and the need to get it exact, so they either send me a screen shot from VOICE, or know to get the exact site name from the GP registration window.

To help make make our licensing process a little easier, both I and Envisage Software Solutions now display the full site name on our registration key windows.

So when a customer tells us that the license key doesn't work, we ask them to send us a screen shot of the License window so that we can verify the site name.  In most cases, the customer missed a comma, period, apostrophe, or misspelled something.

One other problem that I have had is with Microsoft Office and its "Smart Quotes" feature.  When a customer emails me their site name, if the site name contains an apostrophe, I can't always copy the name into my license key generator.

In the above example, the first line was copied from Microsoft Outlook and pasted into Notepad.  I typed the second line.  Notice that the apostrophe is different.  If I generate a license key by copying the first line, I will generate an invalid key--something I did just yesterday.  I then need to remember to fix the apostrophe or just re-type the entire name instead.

With that said, I have found that Dynamics GP has a relatively simple and easy licensing system that is very easy for ISV solutions to piggy-back on for their keys.  Just make sure to send your ISV your exact site name, exactly as it appears in your GP Registration window!

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

Wednesday, March 12, 2014

SOP Orders imported with eConnect auto allocate and auto fulfill

By Steve Endow

I received a call from a customer who has an eConnect application to import SOP orders.  The import was developed by another partner many years ago, and I inhereted it for ongoing support.  It has been working fine, but the client is changing their warehouse processes, and they want to use a separate fulfillment process for the imported orders.

No problem, except that the orders imported with eConnect  were automatically coming into GP as allocated and fulfilled, so they wanted to turn that off.  I checked the code for the eConnect import, and it was not set to auto allocate, and it was not specifying a quantity fulfilled on the line items, so I assumed it must be an option in GP.  But we then checked the Order setup options in GP, and it was properly setup to use a separate fulfillment process.

And to confirm that the issue was with the import, they manually entered an order, and it did not automatically fulfill.

I was puzzled.

We checked the GP settings again, and I checked the code again, but I still didn't see what was causing the fulfillment.

I then went back to the eConnect Help file and started searching for "fulfill".  Behold, on the taSopLineIvcInsert schema page, the word "fulfill" showed up on the DOCID field.  And to my surprise, here is what it says:

 Document ID; if left blank, line will autoallocate and fulfill

Huh, go figure.  Even though the DOCID was being specified at the Order Header, if you don't specify it at the line level as well, those lines will auto allocate and auto fulfill.

I checked the GP 2013 eConnect documentation, and it's the same.

That type of defaulting (to no Doc ID) would have never occurred to me, and seems a little odd.  I would have thought that the lines would default to the Doc ID used by the header, but apparently not.  This would explain the issue the customer was seeing.

After all the work that I've done with eConnect, I would thought I would have been aware of that--maybe I knew it at one point but forgot--but it's just another example of the depth and detail involved in ERP systems.  I continue to learn new things regularly...

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

Tuesday, March 4, 2014

Delete Deductions? Um, Not So Fast.

Did you know you can delete a deduction assigned to an employee even if there is activity for that deduction?   As long as the activity is in a prior year, and the year end close for Payroll has been completed, Dynamics GP will allow you to delete the code from Cards-Payroll-Deduction.

Well, that sounds like an easy way to clean up your deduction list?  Just delete the ones that are no longer in use (they can't have any current year activity).  However, it is important to note that removing a deduction that was tax sheltered can cause issues.  The two that I have come across:

1.  Your 941 for prior years will be incorrect, as it will no longer have the deduction's TSA settings to refer to when calculating the taxable wages.
2.  There is no reliable method for determining the tax settings for the deduction once it has been deleted, so it becomes difficult to reconcile anything for the prior year.

Of course, both of these items are not an issue if you have completed your reconciliations and no further questions come up.  But if you are concerned about potentially having to revisit your filings and/or reconciliations for the prior year-- it is better to inactivate the deduction rather than delete it (even if there is no current year activity).

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing 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.

Tuesday, February 18, 2014

"Background process is running: Exit Aborted" message when trying to close GP

By Steve Endow

I am developing a Dynamics GP VS Tools AddIn for a customer, and while testing the AddIn, I started to get this message when I tried to close GP:

"Background process is running: Exit Aborted"

In my VS Tools AddIn code, I am accessing a table buffer, and during my first few versions of the rough code, I had forgotten to close the tables.  This left them open, and caused GP to think that they were still being accessed.  So I added the following lines to close the table buffers:


But even after that, I would still get the Background Process message from GP.

After further review, I realized that I did not have a Try / Catch block in my code yet.  So an error was causing an exception before the Close commands were processed.

After adding the Close commands in a Finally block, the Background Process message went away.

As my daughter says, "Easy peasy lemon squeezee"...

Update: This issue was discussed by David Musgrave in a blog post back in 2009.  Funny that I had this problem back in 2010 and posted a comment on his blog.  And now four years later, I made the same mistake again!

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