|Get the Book|
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.
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: 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.
|Operating System|| The ServiceNow ODBC driver supports installation on the following operating systems:
|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:
You can secure the SOAP connection with basic authentication.
|End User License Agreement||Read the End User License Agreement for the ServiceNow ODBC driver.|
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:
- Double-click the executable to launch the installer.
- Click Next.
- Read and accept the End User License Agreement.
- Select the target directory for installing the ServiceNow ODBC driver.
- The default directory is C:\Program Files\Service-now\ODBC.
- 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.
- Select the Program Folder to create links for the driver. This is the program folder that appears under the Start menu.
- The installation creates the following links in the menu.
- Interactive SQL (ODBC): an interactive SQL command window for directly testing SQL statement.
- Management Console: a Microsoft MMC snap in for configuring default properties for the ODBC driver.
- ODBC Administrator: a Microsoft ODBC Administrator program.
- The driver code is copied to the target folder.
- When prompted, click Finish to complete the installation.
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.
- Double-click the executable.
- 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:
- After removing the previous ODBC driver, double-click on the executable again to run the installer and follow the steps in First Time Installation.
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.
- Configure the global default used by all newly created DSNs.
- Configure each new DSN with its own connection.
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:
- In Windows, navigate to Start > Programs > ServiceNow ODBC > Management Console.
- Expand the Console Root tree using the following path:
- Double-click the DataSourceIPProperties parameter for the ServiceNow data source setting to open the Properties dialog box.
- Change the value to the URL of your instance, using the following format, and then click OK:
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.
- In Windows, navigate to Start > Programs > Service-now ODBC > ODBC Administrator.
- To create a system DSN, select the System DSN tab, and then click Add.
- Select Service-now ODBC Driver 32-bit from the list, and then click Finish.
- Configure the driver and its connection URL by specifying the url= parameter value in the Custom Properties field. For example:
- Click OK.
- You can now use the new driver.
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.
|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.
6.3.2 ODBC Management Console
These properties are accessed from the Management Console available in the Windows Start menu: ServiceNow ODBC > Management Console.
(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|
(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.
|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:
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:
- Increase the maximum length in the dictionary entry for the field in question.
- Reconnect the ODBC driver to pick up the change.
|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: 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.
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.
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.
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: 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.
The following custom properties configure the ODBC proxy server
|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.||*****|
To test your connection, run the ODBC Administrator program.
- In Windows, navigate to Start > Programs > ServiceNow ODBC > ODBC Administrator.
- The ServiceNow ODBC data source is installed as a system data source.
- Select the System DSN tab, and then select the ServiceNow data source.
- Click Configure.
- Click Test Connect in the ODBC Driver Setup dialog box.
- Enter the login credentials.
- Click OK to log in to the data source.
- 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.
- Running Interactive SQL (ODBC)
- Using the ODBC Driver in Excel 2010
- Using the ODBC Driver in Crystal Reports 2008
- Using ODBC Driver in SQL Server 2008
For troubleshooting information, see Knowledge Base article [KB0534968].
- 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.