What do you use to integrate Dynamics GP and Dynamics CRM?

By Steve Endow

Today I was asked what tool I recommended to integrate Dynamics GP and Dynamics CRM.

Unfortunately, I've never done a GP to CRM integration, so I didn't have a recommendation.

I've never searched extensively for GP to CRM integration solutions, so I've only heard of 3 options.

1. Microsoft's Connector for Microsoft Dynamics
2. Scribe and Scribe Online
3. SmartConnect

I'm vaguely familiar with the Microsoft connector, as I recall a version was created many years ago and then discontinued.  And I see that the current version is also apparently being discontinued, and will only work with CRM up to version 2016.

https://mbs.microsoft.com/customersource/northamerica/GP/downloads/service-packs/mdgp_dynamicsconnector

The appeal of the Microsoft Connector is that it is free.  Naturally that would be great for customers, if it works well and does what they need.  But if it won't support CRM releases after version 2016, that is probably not a good option going forward.

So that leaves Scribe / Scribe Online and SmartConnect that I've heard of for GP to CRM integrations.

I just saw one post about Jitterbit being an option, but I don't think I've personally come across a Dynamics GP customer that has used Jitterbit.

Are there other tools are out there for integrating Dynamics GP and Dynamics CRM that you would recommend?

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

Yup, Dynamics GP user names are case sensitive

By Steve Endow

I occasionally get a call from a customer who is having trouble logging in to Dynamics GP.  They created a new user, they know they have the password correct, but GP keeps saying the login failed.

If you encounter this issue, one of the things to check is the capitalization of the GP user ID.

My login failed when I used "testuser".  Notice that the User ID is actually "TestUser".

Unlike the rest of the software world, Dynamics GP user IDs are case sensitive.  So whenever you have a puzzling login failure, try checking the capitalization of the user ID.

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

Back to Basics: What is a SQL Server Instance Name?

By Steve Endow

I regularly work with customers who do not know what the SQL Server Instance Name is for their Dynamics GP environment.  For non-technical users, this is totally understandable--it isn't something they would normally need to know.  With IT folks, this is more common than you might think, as many system admins deal with many other technical items, but are just not familiar with SQL Server.  And to top it off, the fact that Dynamics GP still uses ODBC DSNs can further confuse things for people who aren't familiar with how all of the data access plumbing works.

Let's talk about the ODBC DSN first, as it is a very common source of confusion with Dynamics GP.

When you launch Dynamics GP, you will see the main "Welcome to Dynamics GP" login window.

The first field on this login window is labeled "Server".  This field name causes confusion because that value isn't actually a Server name at all.  It's an ODBC Data Source Name, or DSN.  A DSN is just an arbitrary name that is assigned for the connection to the SQL Server.

For typical GP installations, I recommend using the default value, which is currently Dynamics GP XXXX, with the X values representing the version number.  Because the ODBC DSN affects how passwords are encrypted and decrypted for Dynamics GP, you'll want to make sure that you use the same DSN name on all GP workstations.

So where is the ODBC DSN setup and configured?

On 64-bit versions of Windows, which are pretty much the norm these days, you need to launch the 32-bit ODBC Data Source Administrator tool to check the ODBC DSN settings.   This is the second layer that confuses a lot of people.  Since Dynamics GP is still 32-bit, it uses the 32-bit ODBC drivers, and therefore uses a 32-bit ODBC DSN.

On newer versions of windows, in Administrative Tools, there is an "ODBC Data Sources (32-bit)" entry that you can launch.

On older versions of 64-bit Windows, this separate 32-bit option is not listed, and only the 64-bit version is available.  To open the 32-bit Data Sources, you have to use Windows Explorer to open it.  It is located in the unfortunate location of:

C:\Windows\SysWOW64\odbcad32.exe

If you launch via either of the above options, you will see this ODBC Administrator 32-bit window.

Notice that the window title says 32-bit.  You can then select your Dynamics GP DSN and click Configure.

And finally, once that is open, you will see your REAL SQL Server instance name that is being used for your Dynamics GP ODBC DSN, shown in the Server field.

So that was the longer, but safer way to check the SQL Server instance name on a machine where Dynamics GP is installed.  This is safer because some customers have multiple environments, multiple SQL Servers, and multiple SQL Server instances.  Occasionally, there may be a GP machine that is pointed to a test SQL server instance, so it never hurts to double check.

If you are more technical and have access to the SQL Server or to SQL Server Management Studio, there is another way to quickly check the SQL instance name.  When you launch Management Studio and login to the GP SQL Server, you should see the instance name listed in Object Explorer.

In this example, my SQL Server is using a "default instance" and is named "GP2015", which is the same as the server name.

But you may have a server that has a "named instance", in which case you will see the server name followed by a backslash, followed by the instance name.

In this example, my server is named GPDEV2, but I have a named SQL Server Instance called "GPDEV2\GP2010".

As mentioned before, you need to be careful about getting the instance name from Management Studio because there could be multiple SQL Servers and multiple SQL instances.  Just because you see a particular instance when you log into Management Studio doesn't necessarily mean that it is the one being used by Dynamics GP.

When in doubt, just find a machine running Dynamics GP that you are sure points to the SQL Server that you want, and then check the ODBC DSN settings on that machine.

Hopefully that helps explain what a SQL Server Instance Name is and how to find it in a Dynamics GP environment.

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

Check who is accessing Dynamics GP shared dictionary files

By Steve Endow

There are lots of customers who continue to install the Dynamics GP client application on user workstations, or customers who have GP installed on multiple Terminal Servers.  As a result, many of them use "shared dictionaries", where the FORMS.DIC and REPORTS.DIC files are stored on a centralized network share.

There are some downsides to using shared dictionaries with Dynamics GP, but the convenience generally wins out, so people continue to use shared dictionaries.

One thing to remember with shared dictionaries is that when importing a package file to a shared dictionary (typically FORMS.DIC or REPORTS.DIC), you should make sure that the dictionary is not in use.  While you may be able to import package files to active shared dictionaries many times without any issue, I have seen situations where the shared dictionary was corrupted as a result of an import.

Today I was explaining this precaution to a customer, and he mentioned that he didn't know how to tell if the dictionary files were being accessed by any users.

Here is how you can check if users are accessing the GP shared dictionary files.

Open Computer Management on the server where the network share and dictionary files reside.  On the left, expand Shared Folders and then click on Open Files.

In this example, you can see that my user account is accessing the GP share, and that I am accessing the FORMS.DIC file.

Ideally, you would ask the user to close GP, which should close the connections.  If the users have left for the day but are still connected, you can select one ore more of the the open files, right click, and select Close Open File.  You can also right click in the white space below the open files and select Disconnect All Open Files.

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

What's the deal with the leading zeroes?

By Steve Endow

I received a PO from a customer this morning and this was the PO number:  00001445

Not one leading zero. Not two.  But four leading zeroes.  Why?  Is 1445 not a good enough for a PO number?

Another common one is checks.  I received a customer check numbered 000095220.  My personal and business checks are nice and simple, with no leading zeroes, so I don't understand why people feel compelled to add zeroes at the front of document numbers.

It seems like the Fabrikam / TWO sample company encourages this, and takes the leading-zero cake with 19 leading zeroes setup in the default checkbooks.  I think that would allow for several quintillion checks.  Good luck having BofA cash a check that has a 20 digit number.

But are the leading zeroes really necessary?  Do they provide any value whatsoever?  Can't we just have a check number without the leading zeroes?

Is there some secret about the leading zeroes that I'm missing?  Assuming the system is able to roll over numbers and add a new digit, like incrementing from 9,999 to 10,000 (which I believe GP does just fine properly in some situations), I'm just not seeing the value of having a bunch of zeroes in front of the document numbers.

[Update: So Jen and Roger submitted comments below, and not surprisingly, they both point out that there are modules and records where GP is not able to roll numbers over and add a digit. For those situations, yes, you're stuck with the leading zeroes.]

Many of the default document numbers in GP have alpha prefixes, so it's not quite the same, but they do have an excessive number of zeroes in them.  One of the first things I do when working with a new Fabrikam / TWO database is to change the default next document numbers to values that are usable and don't require me to count a bunch of zeroes.  The default numbers are a nightmare to test with, so I usually pare them down to no more than 6 digits after the alpha prefix.

I'm open to explanations as to why this might be an even remotely valid idea.  I can only think of one, which I'll keep to myself so as to not encourage the practice, but even that one seems like a very weak justification.

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

The Mysterious Myth of SQL Server stored procedure header comments

By Steve Endow

Based on my years of writing code, I tend to put 'header level' comments at the top of a file.  As a result, when I write stored procedures, I tend to also put the header comments at the very top of the SQL script file.

However, my understanding was that comments at the very top of a SQL Server stored procedure script, located before the CREATE PROCEDURE command, would not be saved to SQL Server.  So if you later scripted out an existing stored procedure from SQL Server, the resulting script would not include the header comments.

I was about to write a post about this situation, recommending where to place comments in stored procedures, when a weird thing happened.

I couldn't reproduce the issue.

On SQL Server 2014, I created a test stored procedure.

In this script, I put header comments at the top, before the CREATE PROCEDURE command.  I then ran the script and created the procedure.

But when I scripted out the procedure, to my surprise, the resulting script showed my header comments.

Say what???

I was very surprised, and confused.  Was I going crazy?  Had I imagined that header comments were not saved with stored procedures?

I have worked with Victoria Yudin for several years, and one of our rituals is that I will sometimes draft a procedure, forgetting about the comment position, and she would always have to move my header comments down into the body of the proc.

Coincidentally, this week I received an email from a customer who said he modified my stored procedure script to move my header comments into the body of the procedure so that they would be saved.  So that's three people who believed that SQL stored procedure header comments aren't saved.

I emailed the above screen shots to Victoria, and she was as surprised as I was.  She tested the behavior on SQL 2014, 2012, 2008 R2, and even 2008.  All versions behaved the same, with all versions retaining the stored procedure header comments.  

So, in short, this seems to be a SQL Server urban legend.  Sort of.

We're pretty sure we aren't going crazy, and that we've seen the issue in action, so Victoria and I are wondering whether the issue might have existed in SQL 2000 or SQL 2005.  Neither of us have a vintage install of SQL Server handy, so we weren't able to test it with those older versions.  Regardless, that would mean that we have been mistaken about this issue for the last 8 years.  Yikes.

The other possible explanation is that someone might be highlighting just the CREATE PROCEDURE portion of the proc scripts when creating them, therefore excluding the header comments.  This seems odd and unlikely, as we typically deliver one procedure per SQL file, and there would be no need to highlight a portion of the script.  I certainly don't highlight a portion of the script when running my proc script files.

At this point I'm puzzled, but I'm happy to know that the myth of SQL Server stored procedure header comments has been busted.  Everyone go ahead and add header comments to your heart's delight in all of your stored procedure scripts.

If there's something I missed, or if you have an explanation of why stored procedure header comments may not be saved, please post a comment and let me know.

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