“Excel” with ServiceNow Advanced Reports - part 2

Krishna Koneru - 8th August 2013

In my previous blog, I wrote about ODBC drivers and how to use them.  Here I will show the first part of the installation; how to install and configure the ServiceNow ODBC driver on your system.  Make sure you have read the other blog first.

Before you download the driver we need to decide which version (32-bit or 64-bit) of the ODBC Driver to download. This based upon your system and the reporting Application (in my example this is Excel) that you are going to use for building reports.
 

Choosing the right ServiceNow ODBC Driver

ServiceNow offers 32-bit and 64-bit ServiceNow ODBC executable files. Use the below matrix table to know which bit of ServiceNow ODBC driver is suitable for you.

 

32-bit ServiceNow ODBC Driver

64-bit ServiceNow ODBC Driver

32-bit Windows system and 32-bit Reporting Application

64-bit Windows system and 64-bit Reporting Application

64-bit Windows system and 32-bit Reporting Application

Generally, most of the systems will be having a 32-bit type Excel, even on 64-bit Windows systems, so you will most likely need the 32-bit ServiceNow ODBC Driver.
 

Download and Install ServiceNow ODBC Driver:

Go to the page http://wiki.servicenow.com/index.php?title=ODBC_Driver and look at Section 6: Installation. There you will have a link to download the 32-bit ServiceNow ODBC Driver executable file.

Before installing the downloaded ODBC executable file make sure you login to the system as an Administrator. Then just follow the steps that are mentioned under Section 6.1 in ServiceNow Wiki ODBC page (http://wiki.servicenow.com/index.php?title=ODBC_Driver).
 

Configure ServiceNow ODBC Driver for your instance

Update DSN

In Windows, navigate to Start > Programs > Service-now ODBC > ODBC Administrator > go to ‘System DSN’ tab. Select ‘ServiceNow’ from System Data Sources and click on Configure button.

In the ‘DataDirect OpenAccess SDK Local ODBC Driver Setup’ window, enter ‘url= for Custom properties filed > Apply

To do a connection test, click on Test Connect button. Logon windows will open where you need to provide ServiceNow Username, Password and click on OK.

If you get the successful message as shown in the below screenshot then the details you entered are correct.

On some systems (32-bit ServiceNow ODBC Driver installed on a 64-bit system) you might encounter an error  

  • "The program can't start because MSVCR71.dll is missing from your computer. Try reinstalling the program to fix this problem."when you try to make use of the ODBC in for instance MS Excel
  • "[DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK Client]Failed to initialize the Service component." when you try to connect using iSQL

The solution for this is to create an environmental variable on your system to let it find the missed MSVCR71.dll file from the ServiceNow ODBC Driver installation folder.

Then add the path for example if you installed it in C drive and in Program files (86) folder the path that you need to add ";C:\Program Files (x86)\ServiceNow\ODBC\ip\Java\jre\bin" at the end of the ‘Variable Value’ field.

Click OK and save the changes. Another common error is “OpenAccess SDK Error:1001”, which is related to a proxy server. 

To fix this, you’ll need to know your proxy server details, and enter these in the IP Parameters. Open IE Browser > Tools > Internet Options > Connections Tab > LAN Settings, and find the  Address field and Port field values.
 

Change the global default

Changing the global default is less tricky than updating the DSN; just follow the steps that are mentioned in Section 7.1 on the ServiceNow WIKI ODBC page http://wiki.servicenow.com/index.php?title=ODBC_Driver.