It Pays to Know a SQL Expert, Especially if you are not!

As consultants, we all have aspects of our work that are more frustrating than others. Migration of data is one of those tasks that I prefer to do alone...I tend to swear a lot and it isn't pretty! Last week I had a payroll migration that was one of the most hairy to date and I have been doing this for years.

It started out very vanilla. After all, it doesn't matter the quantity of data...it is all about the quality, right? I only needed current year employee payroll transactions. My methodology is very tried and true. I Download the data into Excel, clean it up, save it, map it to Integration Manager and after a few hit and miss tries...the data has been successfully integrated. I can move on to something more exciting.

My client runs about 2,500 payroll checks a week and there is a massive amount of employee turnover. In the legacy system (which is housed in a separate facility from the new implementation) employees were not inactivated and, after several years, there were literally hundreds of thousands of employee master records.

As in Dynamics GP, the data that I needed was in several different tables, and had to be exported. No problem I think, and dump out the historical payroll transactions via SQL table export. I will do the same thing with the entire employee master record table, Do a VLook-up to parse out the employees that haven't been paid this year...and there you go... I have what I need to import into my new GP database.

Who knew there was a limit to how much data Excel could handle. Yes, I know, there is a max. amount of rows - and Excel 2007 can hold a lot! Apparently, not enough for my employee master and state tax file. After several attempts (and a lot of swearing) I have the data in separate workbooks. Now to get rid of the employees I do not need. Not so easy. The calculation speed was painfully slow and while I could import data to all 1M rows, Excel couldn't run the formula due to memory constraints (so I am told.) 12 hours later I am completely frustrated and at my witts end.

This is where the story gets good. My fairy godmother (a.k.a. my boss) tells me to quit pulling my hair out and call Tom Celvi. He is a SQL wizard! I am a bean-counter and application consultant. I have always thought of SQL as something to be respected and a bit feared. After all, it is the "house" for my GP data.

Silly me! There is a whole other world out there that I owe myself to learn more about. Tom has shown me that SQL is a very powerful tool and can be used for other things besides housing my precious GP databases.

Tom created a database within the clients SQL 2008 environment, imported all my raw, ugly data and through a series events that I can only explain as pure alchemy...he managed to move that cleaned up data into my GP database and I had my data in all the correct payroll tables along with only the 12,000 current employees!

This took Tom 10 hours in total and I bet he didn't swear once! I was then able to successfully integrate the current year payroll transactions via Integration Manager and my my client is live and processing payroll.

Under lessons learned:

1. SQL doesn't have to be feared, it can be your friend (but should be respected)


2. Quality over Quantity is over rated - strive for both because Excel does have its limits.


3. hang out with a SQL guru, it will save on hair dye

MSDE file full error Dynamics GP

Had a client using MSDE and was getting space errors whenever trying to process any transactions. Couldn't post at all, just print reports. We looked at the size of their database and found it was 2 GBs. As you may know there is a 2 GB space limit on MSDE so I suggested updating to SQL Server Express Edition 2005 which has a 4 GB restriction. It is free and would double their storage capacity.

The client thanked me profusely and said they never heard of Microsoft giving anything away for free. I agreed but told them it's not exactly free as I would charge them for my time which ended up being 3 hours when all was said and done.

Here is the process I used to update the client from MSDE to SQL Server Express Edition 2005:

1. Call MBS Sales Ops ((800) 456-0025) and get SQL keys for GP. These should be free but if you don't have them GP will give you nasty error messages. Do this first. I actually did this last and it took a day to get the stupid keys. When you have the keys go to step 2.


2. Download SQL Server Express Management Studio 2005


3. Install the SQL Server Express Management Studio (Do this first if you want to use it to create your backups.)


4. Make a backup of the company and Dynamics databases


5. Run Capture login script on current install of MSDE. See Tech doc 878449. Script is found here.


6. Download SQL Server Express Edition 2005


7. Install SQL Server Express Edition. Mixed mode. (You may need to allow remote connection in SQL setup if it doesn't default). You can install SQL in one of 2 ways. Upgrade the existing instance of MSDE. (I tried this and it failed on the upgrade). Or install a new instance (Default instance is SQLEXPRESS, call it whatever you like). You will then need to restore the Dynamics and Company databases to the new instance.


8. Run the results of step 5 against the new instance of SQL to recreate your users.


9. Put in new reg keys into Dynamics that you got in Step 1. Dyn utilities on any version pre 10.0. In version 10.0 you will need to run this script (delete sy003500) then enter reg keys in application. See tech doc 943965


10. Update ODBC to point to new instance of SQL

This is similar to the procedure of moving SQL to a new server.

How do you install a large number of workstation after an upgrade efficiently?

Recently we have discussed best practices about upgrades in our company. One of the big challenges in an upgrade is installing a large number of Dynamics workstations at the appropriate time with minimal user interruption as possible. Installing Dynamics has always been a pain. MBS has approached fixing this issue with their mass deployment tools but they never work and it usually ends up easier to do the installation manually. Depending on the installation you could spend 15 mins - 40 mins per workstation. Launching the installs simultaniously (3 at a time in the same area for example) may cut time a bit but still a huge pain.


This led to a series of responses by our technical team. Here is a summary from one of our team member Jason Young. Summary of response below:


One of the tricks I have used many times to minimize downtime for large rollouts is to leverage imaging tools like Acronis True Image. This tool lets you take a picture of a server or servers and redeploy them in a virtualized environment or dissimilar physical hardware. In essence, you can take the customers environment back to the office for the test upgrade without risk of downtime or impacting day to day production of the customer.

Imaging gives you some big advantages:

1. You don’t change the state of the customers environment until testing is successful and you are ready for the production rollout. This gives you huge kudos in the eyes of local IT staff.


2. You always have a clean rollback image.


3. You don’t have to bother local IT staff because you are working with an image offsite.


4. You can couple this type of test upgrade with a Terminal Server (which we have and is easy to build) and now you can do your user acceptance testing via web. Again, no customer impact.

To do the production rollout it is always optimal to do a parallel installation, but the obvious drawback is the cost of hardware. For customer without additional server resources, there is another trick that can minimize downtime and customer impact. For customers with a single backend server (single SQL server), you install a name instance of SQL and restore production data to the new named instance.

Here are the advantages;

1. A named instance of SQL means you have to use a different DSN name than the default instance. This means you can pre-install workstations with the new version and point them at the new instance without worry of an accidental upgrade.
2. The current state is not affected so you have a rollback in the event of an upgrade failure. You don’t have to do any type of restore because you never changed the data in the default instance of SQL.
3. You are basically taking a single server and doing a parallel application upgrade. So, it’s an in-place upgrade in terms of hardware but parallel in terms of the application and data.

End of summary.

Discussion: I like the seperate instance of SQL approach. I do this with different versions of Dynamics. I have 9.0 on one instance and 10.0 on another. You'd have to be careful that users do not launch and continue working in the older version instead of the newer version but that can be overcome with training or simply stopping the older instance of SQL when the time is right (probably need to set the older instance to manually start in case of a server reboot).

Anything you do to make this task easier?

AP Trial Balance by vendor class

Just had someone call and want to know how much they owed for all their merchanising vendors. They did not have the vendors assigned by class. I used SQL to look up the vendors and found each vendor had some default accounts listed on the card. I used 3 queries to get what they needed after backing up the database in case I messed up. Queries are as follow:

• Select * from GL00100 - this shows what the account index number is needed to interpret the PM00200 table accounts
• Select * from pm00200 where pmprchix = '61' or pmprchix = '66' - 61 and 66 were the account index's listed in the first query.
• Update pm00200 set vndclsid = 'MERCH' where pmprchix = '61' or pmprchix = '66' - Updated each vendor to be included in the MERCH class I set up in GP under Tools>>setup>>purchasing>>vendor class. In all we updated 400 vendors in a matter of mins.

The bolded red fields would need to be changed to fit your needs.

Once this work was done we could run the AP trial balance report with a class ID restriction for MERCH vendors only.

Relatively easy solution to get the customer what they needed.

Identify Items That Don't Sell

I initially came across this gem on the Dynamics GP public newsgroup.

Mohammad Daoud provides a nice SQL query to identify inventory items that have not been selling, i.e. slow moving items. My experience is that this is information that many businesses could use. His post is here.

Mass Modify and Account Description

The mass modify tool in Dynamics GP is a good way to add a new set of GL accounts for a particular segment of your chart of accounts. For example, you could copy a set of accounts for one location and add a new location with the appropriate account number. (I.E. 01-4100-00 can be copied and 02-4100-00 can be added). Mass modify can be found at Cards>>financial>>mass modify.

However, the accounts that were copied will have the same description as the older location. So if you identify the location in the description (phone expense - San Diego) the new accounts will have the old description. If you want to update the new description you can run a query in SQL to update the description. Below is a sample update statement:


UPDATE GL00100 set actdescr = replace(actdescr, 'San Diego', 'Los Angeles') where actnumbr_1 = '2' and actdescr like '%San Diego%'

In non technical terms this query says replace San Diego with Los Angeles where ever my new location (2) has the words San Diego in it. You will have to replace the bolded red fields with your information. My location was listed at the beginning of the GL account (actnumbr_1) but you can change that to be whatever segment you have updated.

I would recommend running a select statement on the GL00100 table first and verify you want to replace everything found in the results. Here's a sample query to do that.


Select * from GL00100 where actnumbr_1 = '2' and actdescr like '%San Diego%'

Once again replace the bolded red fields with your information.

Doing the above procedure saved a client from having to update around 100 accounts manually.

Can you think of any other find and replace uses for SQL?

Moving SQL to a new server

Just moved MSDE 2000 to a new server with MSDE 2005. Well worth the update as MSDE 2005 is a free download as well and is way better than the admin consol. Other than not having a backup agent MSDE 2005 looks and feels very much like good old SQL 2005.

Here is the link to get to MSDE 2005 express: http://www.microsoft.com/express/2005/sql/default.aspx

Here's an article to set up a backup routine for MSDE:
http://www.sqldbatips.com/showarticle.asp?ID=27

Process I did to move to a new server:

1. Make backup of DYNAMICS and company databases
2. Run capture login scripts found at: https://mbs.microsoft.com/fileexchange/?fileID=879594a6-60ee-421f-b6ab-4d77da3aba22
3. Install MSDE on new server (Mixed mode)
4. Install GP on new server
5. Copy output from #2 and run on new server (will automatically run against master database)
6. Restore DYNAMICS database and company database to new server (All tasks>>restore>>type in database name exactly as it was on previous server)
7. Change ODBC connections on workstations to point to new server
8. Update Dynamics.set file on all workstations to point to new shared directory with new reports.dic file location

Process could also be used for regular SQL installs.