Bug in Dynamics GP eConnect taCreateSOPTrackingInfo: Error 4628

By Steve Endow

I'm working on an import that will insert shipment tracking numbers for Dynamics GP SOP Sales Orders.  Seems pretty straightforward.

When I attempt to import the tracking number for an order, I get this error from eConnect.

Error Number = 4628  
Stored Procedure= taCreateSOPTrackingInfo  
Error Description = The Tracking Number (Tracking_Number) is empty

Node Identifier Parameters: taCreateSOPTrackingInfo
SOPNUMBE = WEB0001
SOPTYPE = 2
Tracking_Number = 1Z12345E0205271688
Related Error Code Parameters for Node : taCreateSOPTrackingInfo
Tracking_Number = 1Z12345E0205271688

< taCreateSOPTrackingInfo>
  < SOPNUMBE>WEB0001< /SOPNUMBE>
  < SOPTYPE>2< /SOPTYPE>
  < Tracking_Number>1Z12345E0205271688< /Tracking_Number>
< /taCreateSOPTrackingInfo>


It seems pretty obvious that something isn't right with this error.  Clearly the tracking number is being supplied.

So off we go to debug eConnect.

When we open the taCreateSOPTrackingInfo stored procedure and search for error 4628, we see this gem:

    IF ( @I_vTracking_Number <> '' )

        BEGIN

            SELECT  @O_iErrorState = 4628;

            EXEC @iStatus = taUpdateString @O_iErrorState, @oErrString,

                @oErrString OUTPUT, @iAddCodeErrState OUTPUT;

        END;

So.  If the tracking number parameter has a value, the stored procedure returns error 4628, saying that the tracking number is empty.  Genius!

I altered the procedure to fix the if statement so that it uses an equal sign, and that eliminated the error, and the tracking numbers imported fine.

    IF ( @I_vTracking_Number = '' )

        BEGIN

            SELECT  @O_iErrorState = 4628;

            EXEC @iStatus = taUpdateString @O_iErrorState, @oErrString,

                @oErrString OUTPUT, @iAddCodeErrState OUTPUT;

        END;

What is baffling is that this bug exists in GP 2016, 2015, and 2013, which is where I stopped looking.  I'm assuming that it has existed prior to 2013.

However, I recently worked with another customer who imports tracking numbers for their SOP Orders, but they did not receive this error.  Why?

Looking at their taSopTrackingNum procedure, I see that it is an internal Microsoft version of the procedure that was customized by MBS professional services for the customer.  The stored procedure was was based on the 2005 version from GP 9, and it does not appear to have the validation code.  Because it is customized, it was just carried over with each GP upgrade, always replacing the buggy updated version that is installed with GP.

So some time between 2005 and 2013, someone monkeyed with the procedure, added error 4628, and didn't bother to test their changes.  And the bug has now existed for over 4 years.

I can't possibly be the only person to have run into this.  Can I?  Does nobody else use this eConnect node?

Anyway, the good news is that it's easy to fix.  But just remember that every time you upgrade GP, that buggy proc is going to get reinstalled, and you'll forget to update the buggy proc, and it will cause your tracking number imports to start failing.

Carry on.

Steve Endow is a Microsoft MVP 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 Twitter, YouTube, and Google+

http://www.precipioservices.com

Importing SOP Orders with sales taxes using eConnect

By Steve Endow

I don't remember if I've ever had to import Dynamics GP Sales Orders with sales tax amounts before.  If I have, it's been so long that I've completely forgotten about it.

So let's just say that today was a mini adventure.

My customer is importing "multi-channel" web site orders that are coming from major national retailers and online merchants.  Some of them calculate and charge sales tax, while others do not.  The customer is using Avatax with Dynamics GP, so Avatax is ultimately handling the final sales tax calculation.

For a few reasons that I'm not entirely clear on, the customer wanted to import the sales tax amounts for the web sites that calculated and provided sales tax--even though Avatax would be recalculating the taxes.  And thus began the journey of figuring out the quirky and barely documented process of importing Sales Order header level taxes using eConnect.

We first tried sending in the sales tax amount to the taSopHdrIvcInsert TAXAMNT node.  That resulted in this error:

Error Number = 799  
Stored Procedure= taSopHdrIvcInsert  Error Description = Tax table detail does not equal the tax amount


In the famously ironic process of Googling this error, I found my own thoughts on this error in this forum post.

https://community.dynamics.com/gp/f/32/t/140923


While my response to the post didn't directly address my issue, it gave me some clues.  I used SQL Profiler to trace the activity of my eConnect import and confirmed that the SOP10105 table was not being touched and that taSopLineIvcTaxInsert was not being called.

I checked the eConnect documentation on SOP taxes, but it might as well have been Greek.  I now see that there is one key sentence that is a clue, but without knowing what to look for, it didn't make any sense.

Let me know if you are able to spot the clue.

But it seemed like the taSopLineIvcTaxInsert node may be required even for header level taxes. Which made me concerned that I might have to send it in for each order line item--which would be a hassle.

I updated my eConnect code to add tax lines to my order, leaving out LNITMSEQ because I was only sending in header level taxes, and it resulted in this:

< taSopLineIvcTaxInsert_Items>
< taSopLineIvcTaxInsert>
< SOPTYPE>2< /SOPTYPE>
< SOPNUMBE>WEB0006< /SOPNUMBE>
< CUSTNMBR>CUST0001< /CUSTNMBR>
< SALESAMT>78.75< /SALESAMT>
< TAXDTLID>AVATAX< /TAXDTLID>
< STAXAMNT>5.75< /STAXAMNT>
< /taSopLineIvcTaxInsert>
< /taSopLineIvcTaxInsert_Items>


That did the trick.  The order imported successfully, the sales tax amount came in properly, and the SOP10105 table was populated.

So if you need to import SOP transactions with sales taxes, it appears you have to include taSopLineIvcTaxInsert.

Good times!

Steve Endow is a Microsoft MVP 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 Twitter, YouTube, and Google+

http://www.precipioservices.com

Stop typing passwords...completely. Use a fingerprint reader and Windows Hello!

By Steve Endow

Many, many, many years ago I finally got tired of remembering all of my passwords, and started using an Excel file to track them. After a few years of that, I got tired of insecurely tracking passwords in Excel and started using RoboForm to manage my passwords.  It had a few rough edges way back then, but worked well enough and also worked on my BlackBerry (yup, it was a long time ago).  I now manage a few thousand logins and notes in RoboForm, and needless to say, it's pretty essential in my daily life.

So that's great.  But there are still a few passwords I am having to constantly type.  Every time I sit down at my desk, I have to login to Windows.  I've been doing it for so many years that it's second nature.  I don't even think twice about it--it's pure muscle memory.  Except when I mistype my password or don't realize that Caps Lock is on, and it takes me 3-4 tries.  Grrr.

The second password I am constantly typing is my RoboForm master password.  So when a web site needs a login and I tell RoboForm to handle it, RoboForm will sometimes prompt me to enter my master password if I've just unlocked my desktop or have been away for a few hours.  Again, I've been doing it for so many years that I don't even think about it.

Then came the iPhone fingerprint sensor called TouchID.  It has taken a few years to gain traction, but now I can use my fingerprint to unlock my phone, pay for my groceries, login to my banking apps, and...access the RoboForm iOS app.  It is absolutely fantastic.  Typing my long RoboForm master password on my phone was moderately painful, so being able to use TouchID to unlock RoboForm on my phone was a wonderful improvement.  Once you start using Touch ID, it becomes strange to see a password prompt on the iPhone.

Then, a few years ago, I bought a Surface Pro 4 (which I do not recommend, at all, long story).  While shopping for the Surface Pro 4, I didn't know anything about Windows Hello, and I didn't realize that the Surface Pro 4 had an infrared web cam that could be used for face recognition authentication with Windows Hello.  But when I saw that Microsoft offered a keyboard with an integrated fingerprint reader, I knew I wanted one.  I waited a few months until the keyboard with fingerprint reader was in stock before buying the SP4, and I'm glad I waited.

After a few dozen firmware updates and software fixes made the horrible SP4 minimally usable and allowed the keyboard to actually work, the fingerprint reader on the SP4 keyboard was great.  It was surprisingly fast and easy to use.  It was much faster and more reliable than the Windows Hello face recognition, so I ended up using the fingerprint reader quite a bit.

But I still kept typing in my RoboForm password on my laptop...until one day I was poking around in the RoboForm settings and I accidentally discovered that RoboForm supported fingerprint authentication!  Eureka!  I don't know when the support was added, but I wasn't about to complain.

I enabled the fingerprint support and like magic, RoboForm unlocked with a touch of my finger.  Wow.  This was YUGE.

Having suffered for a few years with the SP4, I finally gave up and bought a real laptop, a Lenovo X1 Carbon 2017, and was thrilled that it had an integrated fingerprint reader as a standard feature.  Having experienced how useful the reader was on the SP4, I was just as happy with it on the Lenovo X1.  And after installing RoboForm on the X1 Carbon, I enabled fingerprint support and was on my way.

So life was then grand in mobile-land.  My phone and laptop had seamless fingerprint authentication to login and authenticate with RoboForm.

Which made using my desktop painful.  I actually...had to... type... my... Windows... password... every... single... time...I sat down.  After being spoiled by my iPhone and my laptop, it felt like a complete anachronism to actually have to TYPE (gasp!) my password!  Barbaric!

I apparently started to get rusty and seemed to regularly mistype my password on my desktop.  I then had several cases where it took me 4 or 5 password attempts before realizing Caps Lock was on.  Ugh.  I felt like I was in the stone ages, where Minority Report style authentication didn't actually exist.  It was...unacceptable.

So I searched for desktop fingerprint readers for Windows.  And...I was underwhelmed.  I found one that looked legit, for about $100, but the reviews were very mixed, citing driver issues and reading that the company had apparently been acquired and that they seem to have disappeared.  After seeing the mixed reviews on other models, I gave up.

But after a few more weeks of password typing punishment, I tried again and figured I would reconsider the small mini fingerprint readers that seem to have been designed for laptops.  A few seemed okay, but again, mixed reviews.

After a few more searches, I found one that seemed legit, and seemed designed for Windows 10 Windows Hello authentication.  (there are probably a few others that work well, but caveat emptor and read the reviews)

https://www.amazon.com/gp/product/B06XG4MHFJ/

It was only $32 on Amazon and seemed to have pretty good reviews, so I gladly bought.  I plugged it in to my Windows 10 desktop, Windows automatically detected it and set it up, and then I added a fingerprint in Windows Hello.  I then enabled fingerprint support in RoboForm.


UPDATE May 3, 2018:  I purchased the PQI fingerprint reader in August 2017, and by Spring 2018, it was acting flaky. Windows wouldn't gradually stop recognizing my fingerprint.  If I reset the fingerprint in Windows Hello, it would work okay for a few days, but then it would eventually 'drift' and have a hard time recognizing me, until after a week, it wouldn't recognize my fingerprint at all.

So I just bought a new Kensington model to try.

https://www.amazon.com/gp/product/B01NAVWPOJ/

So far it seems to work fine, but I'll see how well it works in 6-12 months.


Based on my tests so far, it works great.  I can now unlock my desktop by very briefly touching the sensor with my finger.  And I no longer have to type my RoboForm master password, which is a huge, huge benefit.  Just like my iPhone and my laptop.  No more passwords.

To make it more accessible and easier to use, I plugged the fingerprint sensor into a USB extension cable and then attached that cable to the back of my keyboard with a little hot glue.  Now, whenever I need to login or enter a password, I just move my hand to the left side of my keyboard and give the sensor a quick touch.

It's quite surprising how fast it is, and it's much, much faster than typing my password.  In fact, I don't even have to press a key on my keyboard.  From the Windows lock screen, I can just touch the sensor and login.

Once I'm in Windows, when I need to unlock RoboForm, it's just a quick touch to the sensor. and it's unlocked.

If you aren't using fingerprint sensors on every device you own, I highly recommend it.  I now use fingerprints on my iPhone, iPad, laptop, and desktop and it's a huge convenience.  You don't realize what a hassle passwords are until you start using your fingerprint to authenticate.

It's taken me several years to use fingerprints on all of my devices, but I'm finally there and it's glorious.

Steve Endow is a Microsoft MVP 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 Twitter, YouTube, and Google+

http://www.precipioservices.com

Tales of Dynamics GP backups and ransomware

By Steve Endow

At the excellent Dynamics GP Tech 2017 Conference this week, I heard a few interesting stories about ransomware at Dynamics GP customers.

One partner told me a very interesting story about ransomware at a customer that encrypted everything, including the customer's Dynamics GP database backups.  The Dynamics GP partner was called in and he assessed the catastrophe.  Nothing was recoverable.

But he noticed something strange.  Dynamics GP was still working.  He logged into the SQL Server, and he saw that the Dynamics GP databases were still intact and were not encrypted.  He speculated that because SQL Server tenaciously locks the MDF and LDF files, the ransomware was apparently unable to encrypt the live database files.

He was able to stop the SQL Service, quickly copy all of the database files, and attach them on a clean SQL Server.  Luckily, that copy process worked and the ransomware was either inactive at that point, or it didn't have time to encrypt the unlocked database files.  In hindsight, I think I would probably first try doing full backups of all of the databases to ensure the MDF and LDF files remained locked, but saving the backup files to a clean location that can't be accessed by the ransomware would probably still be tricky.


Next, during her "Microsoft Azure: Infrastructure, Disaster Recovery, and Backups", Windi Epperson shared some harrowing stories about tornadoes in Oklahoma.

Some of Windi's customers have had entire buildings vaporized by a tornado, so even the best on-site backup would have been insufficient.  Windi discussed the Azure Backup service, which I didn't even know about, as a flexible and economical way to get all types of backups off site.  She also demonstrated the Dynamics GP backup to Azure feature that she recommends for small customers who don't have the IT staff to handle off site backups.

https://azure.microsoft.com/en-us/services/backup/


I currently have a lot of my data backed up on Backblaze S2 storage through my Synology NAS device, but that is only through a connected sync process, and is not a true archive backup.  I've been looking for a more traditional disconnected off site backup storage service that is reasonably priced, so I'm going to look into Azure backup and see if I can setup a process that can automatically backup what I need.

Steve Endow is a Microsoft MVP 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 Twitter, YouTube, and Google+

http://www.precipioservices.com

Adding or dropping SQL indexes temporarily on production database tables

By Steve Endow

During one of my presentations on Optimizing SQL Scripting at the GP Tech 2017 Conference this week at the Microsoft campus in Fargo, one of the attendees asked an interesting question:

Suppose you need to run a complex query just a few times, and you find that the query would benefit greatly from adding new indexes to one or more tables.  Rather than adding 'permanent' indexes, would it be prudent to temporarily add the indexes so that you could run the query faster, and then remove the indexes when you no longer need them?


I think it is great question. I immediately thought of one likely real world scenario for this, and coincidentally, I shared a Lyft back to the airport with a consultant who described a second scenario where a slightly different index management process was required.


Scenario 1:  Imagine that at the end of each financial quarter, dozens of large, complex financial and sales analysis reports are run against dozens of Dynamics GP company databases.  If some reports take a minute to run, and a few indexes can be added to reduce the report run times to a few seconds, that time savings could really add up.  I could definitely see the value of adding indexes to speed up this process.

But is it worth adding permanent indexes to tables to support the quarterly reports?  Or is it better to add the indexes once per quarter, run the reports, and then remove the indexes?

I don't currently know how to assess the actual costs vs. benefits of those situations, but given that Dynamics GP is already drowning in SQL indexes, and given that the indexes may be dropped during a GP upgrade (and it's easy to forget to recreate them), I think that creating the indexes temporarily seems like a reasonable solution for this hypothetical example.

The one concern I expressed about the solution was the potential for the CREATE INDEX process to lock the tables as they were being built.

I did some research, and confirmed my concern that a table will be locked and inaccessible during the CREATE INDEX process.

This is mentioned in the "Performance Considerations" section of this Books On Line page:

https://technet.microsoft.com/en-us/library/ms190197(v=sql.105).aspx

Most Dynamics GP customers use SQL Server Standard Edition, so indexes are created "offline", and the table is locked until the create index operation completes.

SQL Server Enterprise edition does have an "online" indexing option, but from what I have been able to find, even that feature doesn't provide 100% accessibility of the table during the indexing operation, so there may be some challenges in very high volume environments.

If the temporary indexes make sense, my recommendation would be to add the indexes during a maintenance window, such as late at night, and then run the queries the next day (or next few days), and then remove the index when they are no longer needed.


Scenario 2:  A Dynamics GP consultant told me a story about a prior job where he had to bulk load millions of records into a table on a regular basis.  The bulk load had a very limited time window, so the import had to be completed as quickly as possible.

In order to speed up the import process, they dropped the indexes on the table, imported the millions of additional records into the table, and then added the indexes back to the table.  I hadn't considered that scenario before, but he explained it worked very well.

I was able to find this Books Online article about it and recommendations on when to drop or not drop indexes for bulk load operations.  It provides recommendations depending on whether the table is empty or not, and how much new data is being imported.

https://technet.microsoft.com/en-us/library/ms177445(v=sql.105).aspx


So I learned a few interesting things myself during my session.  Hope this was helpful!

Steve Endow is a Microsoft MVP 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 Twitter, YouTube, and Google+

http://www.precipioservices.com