Tuesday, November 3, 2009

Creating a Dynamics GP DSN on 64-bit Windows

aka "Why isn't my DSN showing up in GP? It's right there in the Data Sources window!"

This is one of those things that I am familiar with, but since I haven't run into it in a long time, I had to do some digging to find specifics of the solution. I'll post it here in case anyone else has the issue for the first time, and for myself when I forget again.

So far, I have used 32-bit Windows for my development virtual servers, but Windows 2008 R2 is only available in 64-bit, so I'll probably be using 64-bit Windows going forward for any new virtual servers.

As you know, Dynamics GP requires an ODBC Data Source Name (DSN) to be setup on the Windows machine where it is installed. It uses this to connect to SQL Server, and is usually very simple to setup.

But, if you install GP on a 64-bit version of Windows and manually setup a DSN, when you try and launch GP or GP Utilities for the first time, the "Server" drop down list will be blank. You'll then go back and make sure you aren't imagining things and confirm that you did setup the DSN.

If you setup the DSN by navigating to Administrative Tools --> Data Sources, you actually went into the 64-bit ODBC Administrator tool. GP is only able to 'see' 32-bit System DSNs, and will not see the 64-bit System DSNs.

In order to setup the DSN for GP, you will need to launch the 32-bit ODBC Administrator tool. The two windows appear to be indistinguishable, with no visible differences between them.

Here is a Microsoft Support KB article discussing the two versions.

From what I have found, you have to launch it by navigating to:

%systemdrive%\Windows\SysWoW64\odbcad32.exe

Confusingly, the 64-bit and 32-bit versions of the tool have the same file name, which includes "32". Yet the 32-bit version is in the SysWoW64 folder. Crystal clear, right?

Once you locate the file, I would recommend creating a shortcut to it on the desktop or start menu, or somewhere else where it will be pretty visible. You should also consider naming it something like "32-bit Data Sources" to differentiate it, and might also want to rename the Data Sources icon under Administrative tools to "64-bit Data Sources" to further reduce confusion.

Once you launch the 32-bit Administrator tool and setup your DSN, it should show up in GP and GP Utilities.

And for those that are wondering why I don't just use the DSN setup option that is part of the GP 10 installation, I tried using it when GP 10 was first released, but it didn't work for some reason and gave me errors, so I haven't tried it again since. Since it only takes a few seconds to set up a DSN, I just continue to do it manually, which also gives me the opportunity to test it and make sure it is working properly and that I didn't mistype anything.

Lastly, as the MS KB article suggests, if you need to use both 64-bit and 32-bit DSNs, consider naming them with 32 or 64 in the name to differentiate them.

2 comments:

Tim said...

Arrrgh, that was the exact problem I was having, I was about to bust my GP terminal server down to 2003 x86 (having started at 2008 R2, then having to bust it down to 2008 x64, I wasn't relishing yet another rebuild... not to mention the 4GB RAM limit)

Thank you !

hyou91 said...

Thank you, you help me a lot