How To Create An ODBC For Microsoft Dynamics GP 2010

Microsoft Dynamics GPI’ve recently had the need to document the install process for Microsoft Dynamics GP 2010 for a new client so I thought I might as well finish off the task and but turning it into a small series of blog posts, the first of which will cover the manual creation of an ODBC connection.

To connect the Microsoft Dynamics GP client to the databases on SQL Server, an ODBC connection needs to be setup. In an x86 version of Windows this can be done using the ODBC Control Panel applet, but in an x64 version you need to use the x86 version of the ODBC Data Source Administrator.

The easiest way of accessing this is by using the Run window and opening %windir%\SysWOW64\odbcad32.exe;

Run

In the ODBC Data Source Administrator (32-bit), select the System DSN tab and click Add;

ODBC Data Source Administartor (32-bit)

For Microsoft Dynamics GP 2010, the SQL Server Native Client 11.0 (or SQL Server Native Client 10.0 if you’re using SQL Server 2008) must be selected;

Create New Data Source

Give the new data source a Name and Desription (these can be anything you want but I’d recommend being consistent across clients) and define the Server running the instance of SQL Server used by Microsoft Dynamics GP. In this example, I’m connecting to a machine called AZURECURVE running a named instance of SQL Server called GP;

Create a New Data Source to SQL Server

Microsoft Dynamics GP required the data source to be defined using SQL Server authentication, so select this option and enter a username and password of a valid SQL Server user (I typically use the sa account);

Create a New Data Source to SQL Server

Uncheck Use ANSI quoted identifiers and Use ANSI nulls, paddings and warnings;

Create a New Data Source to SQL Server

Uncheck Perform translation for character data;

Create a New Data Source to SQL Server

Review the configuration of the ODBC which will be created;

ODBC MIcrosoft SQL Server Setup

Click the Test Data Source button to verify the connection settings;

SQL Server ODBC Data Source Test

Once you’ve finished the process of creating the new ODBC it’ll be shown in the list of System Data Sources ready for selection when running the Microsoft Dynamics GP client;

ODBC Data Source Administartor (32-bit)

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

10 thoughts on “How To Create An ODBC For Microsoft Dynamics GP 2010

  1. Carl says:

    Have you ever came across a way to script this? I’ve tried before (to save myself time) and haven’t been able to quite figure it out.

    1. Ian Grieve says:

      Hi Carl,
      It’s not something I have ever tried to script.
      Ian

  2. Joe Howar says:

    Hi,

    I have done this once in the past and have to do it again and completely forgot the running order:
    Install Sql Server
    Install Gp
    Set up gp utilities
    set up odbc

    Is that correct

    1. Ian Grieve says:

      No, you would install SQL Server, install GP (which can create the ODBC for you), create the ODBC if you didn’t allow GP to do so and then run GP Utilities.

    2. Ian Grieve says:

      Hi Joe,
      No the order would be:

      1. Install SQL Server
      2. Install GP (where you can chose to have the ODBC automatically created
      3. Setup ODBC (if you didn’t create automatically in the last step)
      4. Run GP Utilities

Leave a Reply to Ian Grieve Cancel reply

Your email address will not be published. Required fields are marked *