Monday, April 8, 2013

Dynamics GP 2013 Service Pack error


The Problem
Here’s the scenario. You have just installed a service pack. When you launch GP 2013, you are told you need to run Utilities. You launch Utilities, but oddly, it doesn’t find an existing instance of GP 2013. So, you just hit next and it moves along. Suddenly, the error pop’s up telling you:
“A system database name was not supplied. Run a repair of the Microsoft Dynamics GP installation”

system database not found

The fact that it was trying to create a new system database makes a little more sense now. Next, you dutifully launch Control Panel and begin to run the Repair routine. Right at the beginning of the Repair process you get another error:

           “Object reference not set to an instance of an object”

object reference not set

Fabulous, you’ve seen this error before and it’s never pretty. We used to see it using Integration Manager and it was sometimes hard to fix. You’ll be delighted to know that this is a known error with an easy fix.

This situation comes up if you install the same service pack twice. The first time you install it, everything works perfectly, when you re-install, it you have the above problems.

The Solution
The fix for this is explained by Sara Purdy on the Microsoft Dynamics GP Support and Services Blog. Always go there first if you have any problems in Dynamics GP, there you will find some really exceptional advice.

The actual posting is called (deep breath here) Microsoft Dynamics GP 2013 .MSP Patch Files Removing Information from Dex.ini and Registry.
All of the meaty solution information below was lifted directly from Sara’s  post. I’ve added some editorial comments for those of us (like me) who may need just a little more information to pull this off.

The culprit, as identified by Sara Purdy.

We have identified an issue with Microsoft Dynamics GP 2013 where the .msp files are removing registry entries and part of the Pathname= line in the Dex.ini when you RE-APPLY a patch. The first install of the patch works fine. The issue comes into play if you reapply the patch again on either a single instance or multi-instance. The Country and System Database Name get blanked out in the registry. The Pathname= line in the Dex.ini gets modified.

Here's the registry path:

HKLM\SOFTWARE\Wow6432Node\Microsoft\Business Solutions\Great Plains\v 12.0\1033\DEFAULT\SETUP

Bad: The broken Registry looks like this:
registry

Good: The Fixed Registry looks like this:
fixed registry

Bad: The Dex.ini file looks like this:
dexini

Good: The fixed Dex.ini file looks like this:
fixed ini

Of course, you will use the name of your system database, whatever it is. Yours will look like this: Pathname=your_system_database/dbo/

After you fix the registry and your Dex.ini file, you will need to re-apply the service pack. This, you have to do from the command line (what fun!).

The Second part of the Solution:
These are the instructions from Sarah Purdy’s post:

The current workaround when re-applying the .msp to a specific instance of GP 2013 is to do the following:

1. Click Start, click Run, type regedit, and then click OK.

2. Locate and then click the following registry subkey:
Microsoft Dynamics GP 10.0 and later versions

32-bit environment

    HKEY_LOCAL_MACHINE\Software\Microsoft\Business Solutions\Great Plains\V12.0\1033\your_instance_name\SETUP

64-bit environment

   HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\
Business Solutions\Great Plains\V12.0\1033\your_instance_name\SETUP

3. Expand the folder for the instance of Microsoft Dynamics GP you want to patch.

Note The DEFAULT folder is for the default installation. Replace V12.0 with your version of GP. The Inst01 folder is for the second instance of Microsoft Dynamics GP. For example, if you want to patch the second installation, expand the Inst01 folder.

4. Click the SETUP folder, double-click the Product Code, and then copy the Value data field’s value.

5. Exit Registry Editor.

6. Open up a Command Prompt by right-clicking on it and selecting Run As Administrator.

7. To install the .MSP file, type the following, and then hit Enter.


C:\updatename.msp /n {E979C594-95F9-4E3A-985D-A1DFDF403227}

(You can ‘Paste’ in the command line screen by right clicking next to where you want the data, and then choose ‘Paste’)

Note: Replace "C:\updatename.msp" with the path to the .MSP file and the name of the .MSP patch file. Replace "{E979C594-95F9-4E3A-985D-A1DFDF403227}" with the ProductCode value that you copied in step 4.

The registry will look like this for GP2013:

registry for gp2013 service pack error

The current workaround when re-applying the .msp to all instances of GP 2013 on a machine is to do the following:

1. Open up a Command Prompt by right-clicking on it and selecting Run As Administrator, type in the following, and then hit Enter.


    msiexec.exe /p C:\updatename.msp


Note: Replace C:\updatename.msp with the path to your .MSP file and the name of the .MSP patch file.

Example:
msiexec.exe /p C:\ MicrosoftDynamicsgp12-kb2799678-ENU.msp


2. Click OK.

Until next post!

Leslie

Thursday, April 4, 2013

DynUtils.exe is Missing

This is a first for me.

I had a client call wanting to set up a new company that had the same structure as an existing company. They had not set up a new database before so we were going to walk through it over the phone.

The first step, as you know, is to launch Dynamics Utilities. Surprise! Dynamics Utilities was gone and had left no forwarding address. Specifically, the DynUtils.exe file was missing. These folks were serious about not wanting anybody to run this program.

Out of curiosity, I deleted the DynUtils.exe from my workstation, and sure enough, Dynamics GP launched without a problem.

The moral of this story is to show you another clever way to put a little more security at the workstation. I would go farther though and take away the DynUtils.set file too! I wouldn’t bother with the ‘DU’ dictionaries, that’s only because I’m a bit disinclined to go to through the effort.

Special thanks to Mark Polino for the ‘left no forwarding address phrase’.

Until next time!

Leslie

Wednesday, April 3, 2013

T-SQL 2012: CHOOSE and IIF

 

SQL_Server_2012_logo-56bc115f-4767-4e07-b243-cd61cdf89f84

We’ve been needing these two features for a long time. This post includes a couple of easy examples showing how these functions work.

CHOOSE

I’ll start with CHOOSE. By using CHOOSE you can do away with many tedious CASE statements. CASE statement opportunities are everywhere in Dynamics GP. For instance, how many times have you needed to display the document type in your report and want a word, not a number. Prior to SQL 2012, we would have written a CASE statement that would look a lot like this:

Case SOPTYPE
    when 1 then 'Quote'
    when 2 then 'Order'
    when 3 then 'Invoice'
    when 4 then 'Return'
    when 5 then 'Back Order'
    when 6 then 'Fulfillment Order'
    ELSE 'Undefined Document Type'
End as ' Doc Type'

CHOOSE will pick the value in your list according to the value of your data. It returns the expression at a specific index. So, instead of writing a case statement, you can simply write this:

CHOOSE (SOPTYPE, 'Quote', 'Order', 'Invoice', 'Return',
'Back Order', 'Fulfillment Order') AS DocType

If the value of your index is greater than the number choices, it will return NULL. It will also return NULL if the value is less than 1.

IIF

Next is IIF. Most of you probably already know how to use this and have wondered why you couldn’t do it in T-SQL. Flavors of this are in Excel formulas, Access calculations and even VBA, to name a few. It works with a Boolean expression and returns a value based on whether the expression is true or false. You set this up with three parameters. The first parameter is the expression you’re evaluating, the second parameter is the value if the expression is true, and the third parameter is the value if the expression is false. Yep, just like Excel.

So here’s what it looks like:

IIF(boolean_expression), value if true, value if false)

So much easier than CASE.

Until next post!

Leslie

T-SQL 2012: LAG & LEAD

I’ve just started playing with SQL 2012 and wanted to show you the new LAG and LEAD features.

I’ll use the report below as an example. Suppose you needed to generate a report that showed the last three years of sales. The result set would need to look something like this:

Lag and Lead

LEAD looks ahead the specified number of rows from the current row, and LAG (you guessed it!) looks behind the specified number of rows from the current row. By using the OVER clause you can specify your grouping (partitioning) and sort order.

Using SQL 2012 you can easily accomplish this using the following SELECT statement:

SELECT
   year(docdate)  AS ‘Order Year’
  ,SUM(SubTotal) AS ‘Current Yr Sales’
  ,LAG(SUM(SubTotal), 1, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Last Year Sales’
  ,LAG(SUM(SubTotal), 2, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Year Before Last’
  ,LAG(SUM(SubTotal), 3, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Three Years Ago’
  ,LEAD(SUM(SubTotal), 1, 0) OVER (ORDER BY YEAR(docdate)) AS ‘Next Year’
FROM SOP30200
GROUP BY YEAR(DOCDATE)
ORDER BY YEAR(DOCDATE);

Have fun with it!

Until next post!

Leslie

Monday, April 1, 2013

Dexterity Training in Dallas (June 17 – 21, 2013)

 

Looking for Dexterity training? Come to Dallas!

We’re running this class if only a single person signs up, no cancellations here in Dallas Smile.

I will be teaching the Dexterity Basics class in Dallas, Texas from June 17th to June 21st, providing the basic foundations any Dexterity programmer must have.

This is an opportunity for all of you VSTools programmers to learn how to build your user interface in Dexterity instead of WinForms so that your application will run with the new GP 2013 Web Client. Your existing code will hook right up to the Dexterity events, you may even like the Dexterity forms designer better than VS Tools!

Dexterity is easy to learn and easy to maintain.

For you VBA enhancers, you will learn how to write Dexterity code to deliver your solution instead of VBA. While VBA works marvelously with the rich client, it will not work for the new GP 2013 Web Client. Come to Dallas and learn how easy Dexterity really is.

Even you Technical Consultants, taking the basic Dex class with provide you with the tools you need to understand what all of those error messages really mean. You can become better consultants by learning just how this engine works! (and it’s easy).

For more information, send an email to training@conexussg.com or call 469-828-3274.

Hope to see you in Dallas!

Leslie

The basics class covers the following topics:

Dexterity I – Product Fundamentals

Synopsis:
Dexterity Product Fundamentals will teach you about the development environment used to build the Microsoft Dynamics GP family of products. This course will provide you with the foundation you need to build integrated, database-independent applications. In addition, you will learn how to provide powerful application functionality for Microsoft Dynamics GP.
By actively participating in these classes, you should learn:

Basic programming techniques used by Microsoft Dynamics GP to develop their core modules.

  • How to modify existing Dynamics GP forms and reports in order to make customizations for customers.
  • How to develop add-on applications that will interact with the Dynamics GP products to meet specific requests by customers.
  • How to package and ship your dictionary allowing Microsoft Dynamics GP to make updates to Dynamics GP without affecting the developer’s dictionary.
  • How to register your product with Microsoft Dynamics GP using a unique Product ID to ensure no duplication of add-on applications with the same name.
  • How to develop add-on applications that will “look and feel” like Dynamics GP and provide seamless interface between the applications to ensure a professional finished product.
  • How to use object triggers, which are scripts in your applications that are invoked by events in Dynamics GP, allowing several developers to deliver enhancements to the same Dynamics GP form.