ODBC Driver

From ServiceNow Wiki
Home > Integrate > ODBC Driver > ODBC Driver
Jump to: navigation, search
Web Services
Related Topics
ECC Queue
Knowledge.gif Get the Book

1 Overview

The ServiceNow ODBC driver is compliant to version 3.52 of the Microsoft ODBC specification and allows any ODBC client to connect to the ServiceNow platform for reporting. The ServiceNow ODBC driver uses the ServiceNow web services support for a query-only interface. Because it uses the web services interface, platform-wide access control (ACL) is enforced and data security is in place.

Odbc-driver-splash-high-res.png

Note
Note:
  • The ODBC driver supports only SELECT statements or read-only functions, and does not modify your instance data.
  • There is currently no supported way to consume the ODBC driver from a Java application.
  • ServiceNow does not suport the ODBC driver for use with a Java JDBC-ODBC bridge.
  • For ODBC driver troubleshooting information, search the ServiceNow knowledge base for ODBC Troubleshooting Guide.


2 Checking the ODBC Driver Version

To check the build date and time of the ODBC driver, use CheckVersion located in the Service-Now\ODBC\ip\tools folder. This is an executable Windows host script which reports the build date and time of the current ODBC driver. Use it to assist ServiceNow Technical Support to determine which build of the ODBC driver is running. If the CheckVersion tool is absent, the ODBC driver is out of date and should be uninstalled and reinstalled.

Note
Note: The ODBC installation also has a Service-Now\ODBC\tools folder, which is not the correct path.


3 Current Release

The current release is 1.0.5 and is recommended for all versions of the ServiceNow platform. For more information, see the ODBC Release Notes.

4 Requirements


Category Requirement
Operating System The ServiceNow ODBC driver supports installation on the following operating systems:
  • Windows XP
  • Windows 2003
  • Windows 2008
  • Windows Vista
  • Windows 7
  • Windows 8
Hardware
  • RAM: 1 GB minimum
  • Disk space: 135 MB for installation. 200 MB for writing cache files during usage.
Account The Windows account used for the installation must have local Administrator rights to install an ODBC driver.
Networking During usage, the ODBC driver requires HTTPS (port 443) connectivity to the ServiceNow instance. The communication between the ODBC driver and ServiceNow uses standard SOAP web services.
ServiceNow SOAP User The user account you specify for ODBC must meet the following role requirements:
  • The user must have the soap role if the instance uses the glide.soap.strict_security high security setting.
  • The user must have the roles specific for the tables that it is querying. See Using Access Control Rules to determine what roles and other permissions a table requires.

You can secure the SOAP connection with basic authentication.

Warning
Warning: Do not enable WS-Security for all SOAP requests by setting the glide.soap.require_ws_security system property. It is incompatible with the ODBC driver. Enabling this setting blocks both ODBC driver and MID Server connections. Instead, Use basic authentication.
Do not require WS-Security for ODBC connections

End User License Agreement Read the End User License Agreement for the ServiceNow ODBC driver.

5 Installation

If your system is running a 32-bit version of Windows, install the 32-bit version of the ODBC driver. If your system is running a 64-bit version of Windows, then your choice depends on the reporting application that you will be using. Most reporting applications are 32-bit and therefore require the 32-bit ODBC driver. A 64-bit ODBC driver is only required for 64-bit reporting applications. To determine if the installation machine is 32-bit or 64-bit, right-click My Computer, and then select Properties. Further OS specific instructions can also be found in this Microsoft knowledge article: http://support.microsoft.com/kb/827218.

To begin installation of the ServiceNow ODBC driver, download and save the appropriate self-extracting executable file:

If this is the first time the driver is installed, the installer will be in First Time Installation mode and prompt for the driver to be installed. If the ODBC driver was previously installed, the installer will be in Upgrade mode and prompt for removal of the driver first.

5.1 First Time Installation

To install the ODBC driver for the first time:

  1. Double-click the executable to launch the installer.
    You are presented with the following InstallShield dialog box.

    odbc_step1.png

  2. Click Next.
  3. Read and accept the End User License Agreement.
  4. Select the target directory for installing the ServiceNow ODBC driver.
    The default directory is C:\Program Files\Service-now\ODBC.
  5. Specify the following parameters, which are required to create an ODBC data source that can be used to create a DSN.
    Data Source Name: a short name to identify this data source.
    Description: a short description of the driver. The driver's version number is appended at the end of this value.
    Service Name: the name that can be selected in the Service Name field of the ODBC Administrator.
    Service Data Source: the name that can be selected in the Service Data Source field of the ODBC Administrator.
    Usually the default values are appropriate.

    odbc_step5.png

  6. Select the Program Folder to create links for the driver. This is the program folder that appears under the Start menu.

    odbc_step6.png

  7. The installation creates the following links in the menu.

    odbc_program_menu.png

  8. The driver code is copied to the target folder.

    odbc_step7.png

    A progress bar appears.
  9. When prompted, click Finish to complete the installation.

5.2 Upgrade

If you have previously installed the ODBC driver, click on the executable to uninstall the previous version, and then run the installer again to upgrade.

  1. Double-click the executable.
    You are given the option to un-install or remove the current driver, which is required for the upgrade. Click OK.

    odbc_remove.png

  2. A list appears, displaying the existing ODBC DSN names that you have previously created. You are given the option to delete them.
    Select Yes to remove all previous DSNs or No to keep them for use with the upgraded driver.
    An ODBC DSN is a connection "handle" to use the ODBC driver in an application. For more information from Microsoft, see:

    odbc_remove_dsn.png

  3. After removing the previous ODBC driver, double-click on the executable again to run the installer and follow the steps in First Time Installation.

6 Configuration

After the driver is installed, configure it for your instance. The driver is pre-configured to connect to https://demoodbc.service-now.com using the DSN ServiceNow. There are two ways to configure connectivity for the driver.

6.1 Global Default

A default DSN is preloaded with the ODBC driver installation ServiceNow data source. This preloaded DSN connects using the default connection URL, which is set to https://demo.service-now.com. To change the global default for the instance URL:

  1. In Windows, navigate to Start > Programs > ServiceNow ODBC > Management Console.
  2. Expand the Console Root tree using the following path:
    OpenAccess SDK 6.0 Manager\<installation location>\Services\ServiceNow_ODBC\Data Source Settings\ServiceNow\IP Parameters

    Odbc management console.png

  3. Double-click the DataSourceIPProperties parameter for the ServiceNow data source setting to open the Properties dialog box.
  4. Change the value to the URL of your instance, using the following format, and then click OK:
    https://<your instance>.service-now.com

    Odbc management console2.png

6.2 Creating a New DSN

You can create an unlimited number of DSNs using the ODBC driver and the ServiceNow data source, configured to connect with different instance URLs. This allows the flexibility of selecting the target instance for your ODBC connection by DSN name. As an option during installation or upgrade, you can elect to keep the DSNs when you uninstall.

  1. In Windows, navigate to Start > Programs > Service-now ODBC > ODBC Administrator.

    odbc_dsn.png

  2. To create a system DSN, select the System DSN tab, and then click Add.

    odbc_dsn_add.png

  3. Select Service-now ODBC Driver 32-bit from the list, and then click Finish.
  4. Configure the driver and its connection URL by specifying the url= parameter value in the Custom Properties field. For example:
    url=https://myinstance.service-now.com

    odbc_dsn_conf_driver.png

  5. Click OK.
    You can now use the new driver.

6.3 Properties

The following properties customize connectivity and optimize the query behavior of the ODBC driver.

6.3.1 ODBC Administrator

These properties are specified in the ODBC Data Source Administrator for the DSN or in the Custom Properties field of the login dialog box.

Property Name Description Default
BatchSize During fetching of results from the instance, this batch size configures the number of records to fetch for every request. Typically, the default is an optimal number for normal sized rows. If an error occurs during fetching of records that indicates this value should be lowered, you can modify it to optimize memory usage versus performance. 2000
url This is the ServiceNow instance URL or endpoint. It should indicate the URL to the ServiceNow instance you want to connect to. https://demo.service-now.com
EnablePassThrough During processing of aggregate functions, enabling pass through mode allows directly calling Aggregate Web Service for optimized and speedy response. Whenever possible, this mode should be left enabled. true
debug By default, debugging messages are not produced. Set debug to true when you operate the ODBC driver from the ISQL console window to write all HTTP-related network communication traffic to the console window. When using this option, set gzip to false so that data is not compressed. Otherwise, the data is unreadable. false
gzip By default, data sent over the network is compressed. Set gzip to false when using the debug parameter to write network communication to the ISQL console so that data is not compressed. true
timeout Specifies the socket inactivity timeout value in seconds. 175
retries Number of times to retry the failing request in the event of a socket timeout error. 0

If you need to use more than one of these properties in your connection, concatenate the settings with a semicolon (;) delimiter.

For example, the following string sets the URL to a specific instance and changes the Batch Size to 200 records.

url=https://demo1234.service-now.com;BatchSize=200

6.3.2 ODBC Management Console

These properties are accessed from the Management Console available in the Windows Start menu: ServiceNow ODBC > Management Console.

Property name Description Default
ServiceJVMOptions

(Services\Service Settings\IP Parameters)
JVM command line properties and option. For example, to change the maximum Java heap size, modify the -Xmx150m parameter. -Xms64m -Xmx150m
DataSourceIPProperties

(OpenAccess SDK 6.0 Manager\<installation location>\Services\ServiceNow_ODBC\Data Source Settings\ServiceNow\IP Parameters)
Global default of the instance URL for all ODBC connections. For more flexibility, you may also create new DSNs with default URL configurations. See Properties \ ODBC Administrator. https://demo.service-now.com

6.3.3 ServiceNow Instance

These properties are configured by adding a property or modifying an existing one in your ServiceNow instance.

Property name Description Default
glide.db.max.aggregates The maximum amount of rows returned by aggregate functions. 100000
glide.db.max_view_records The maximum amount of rows returned by a database view . 10000

7 Aggregate Functions

The following aggregate functions are supported by the ODBC driver:

  • COUNT
  • SUM
  • MIN
  • MAX
  • AVG

The ODBC driver supports aggregation functions by utilizing the Aggregate Web Service feature of the platform. Enabling the plugin results in an increase in performance when a query on a single table that involves aggregate functions is called. If the plugin is not enabled, the ODBC driver attempts to download the data and apply the aggregate functions locally. The ability to call an aggregate function with the ODBC driver depends on the client application that is using the driver, as well. For example, if you are using the Microsoft Excel query builder, the application attempts to download data into the client before executing aggregate functions itself rather than passing the query to ServiceNow.

8 Date Time Values

Date time values returned by the ODBC driver are in the local timezone of the application using the driver.

9 Field Lengths in SQL Queries

The ODBC driver limits the field length in SQL queries to the maximum length defined by the ServiceNow dictionary entry. If the data coming from the ODBC source exceeds the field size of the dictionary entry, ServiceNow truncates the query output to fit the field size.

To increase the field size of ODBC output:

  1. Increase the maximum length in the dictionary entry for the field in question.
  2. Reconnect the ODBC driver to pick up the change.
Note
Note: By default, the ODBC driver uses the VARCHAR data type to store query string output. When strings become excessively large (roughly 16000 characters), the ODBC driver uses the LONGVARCHAR data type instead. It is important to keep in mind, however, that the LONGVARCHAR data type has a more limited set of SQL commands that can be executed on it. For example, it does not support queries using scalar data.


10 Display Values

When querying a column of type Choice or Reference, an additional column with the prefix dv_ is available that contains the display value.

Note
Note: To change the display value of a reference field, see Changing the display value.


For example, select dv_caller_id to return the sys_user.name display value of the reference field from an incident record without making another request to the sys_user table.


odbc_display_value.png


Display values can also be used in a filter condition. The ODBC driver optimizes the query condition and processes the filter on the server, for example, querying on the display value of sys_user for the caller_id field of incident by using the dv_caller_id field name.


odbc_display_value_q.png


Aggregate queries can also take advantage of display values if you specify them in the group by or where clause, for example, grouping on the caller_id field of an incident, as well as specifying a filter for it. The query is optimized by passing through to the server.


odbc_display_value_a.png

11 Proxy Servers

The ODBC driver can be configured to route its HTTP SOAP requests via an HTTP proxy server. Setting up a proxy server gives you the option to control access to ServiceNow from the proxy server as well as potentially allowing for a network configuration that can monitor usage statistics. However, because the proxy server intercepts the ODBC driver's requests to your ServiceNow instance, it will degrade the performance of the driver.

Note
Note: This feature is recommended for use with ODBC driver builds dated 7/15/2011, or later.


To enable the use of proxy servers, the custom properties for proxy server settings must be defined first for the data source. After that, these properties can be overridden by specific ODBC DSNs. To do this, run the ODBC Management Console.

Odbc proxy.jpg

The following custom properties configure the ODBC proxy server

Property name Description Example
proxy_host The proxy server's host name or IP address. proxy.company.com
proxy_port The proxy server's port number. 8080
proxy_user_name The proxy server's user name or id, used in an authenticating proxy configuration. odbc_user
proxy_user_password The proxy server's user password, used with the proxy_name value in an authenticating proxy configuration. *****

12 Testing

To test your connection, run the ODBC Administrator program.

  1. In Windows, navigate to Start > Programs > ServiceNow ODBC > ODBC Administrator.
    The ServiceNow ODBC data source is installed as a system data source.
  2. Select the System DSN tab, and then select the ServiceNow data source.
  3. Click Configure.

    Odbc administrator1.png

  4. Click Test Connect in the ODBC Driver Setup dialog box.

    Odbc administrator2.png

  5. Enter the login credentials.
    These are your normal ServiceNow login credentials.

    Odbc administrator3.png

  6. Click OK to log in to the data source.
  7. Click OK again when the success message appears.

13 Examples using the ODBC driver

See the following pages for examples of how to use the ODBC driver to create data sources from other applications.

14 Troubleshooting

For troubleshooting information, see Knowledge Base article [KB0534968].

15 Enhancements

15.1 Dublin

  • When export query strings become large enough to impact performance, the ODBC driver converts the data type from VARCHAR to the LONGVARCHAR data type.
  • The ODBC driver respects character limits set in the dictionary.

15.2 February 2012

  • Additional trace messages for support purposes
  • New ODBC connection parameters:
    • timeout: Socket timeout value, in seconds.
    • retries: Number of times to retry a request if a socket timeout occurs.
Was this article helpful?
Yes, I found what I needed
No, I need more assistance
Views
Personal tools