Using ODBC Driver in SQL Server

From ServiceNow Wiki
Home > Integrate > ODBC Driver > Using ODBC Driver in SQL Server (Redirected from Using ODBC Driver in SQL Server 2008 and 2012)
Jump to: navigation, search
Note: This article applies to Fuji and earlier releases. For more current information, see OCBC Driver in SQL Server at

The ServiceNow Wiki is no longer being updated. Visit for the latest product documentation.

Web Services

1 Overview

Use the ServiceNow ODBC driver in SQL Server as a Linked Server. This allows SQL Server to query tables from a ServiceNow instance directly via the ODBC Driver. Use these procedures only with a supported version of SQL Server. Other versions of SQL Server may cause unexpected behavior. If you encounter unexpected behavior, refer to the troubleshooting linked server knowledge article.

2 Video Tutorials

Configure Microsoft SQL Linked Server Troubleshooting Linked Server Permissions

3 Required Permissions

Additional information on the required permissions for SQL Server Linked Servers can be found on the MSDN blog.

Note: Review this information if you encounter permission errors with SQL Server.

4 Configuring SQL Server

The following example configuration was performed on SQL Server 2008, installed on Windows Server 2008.

  1. Right-click the SQL Server Management Studio application and select as Run as Administrator.
  2. Log in to the database to which you want to link.
  3. Right-click on Server Objects > Linked Servers.
  4. ClickNew Linked Server.
  5. Enter the following values in the dialog.
    • Linked server: SERVICENOW. This is the name of the Linked Server.
    • Provider: Microsoft OLE DB Provider for ODBC Drivers
    • Product name: ServiceNow. This is an identifier. Enter any value that is appropriate.
    • Data source: ServiceNow. This is the name of your DSN.
    New linked server.png
  6. Select Security from the Select a page list, and then enter the following security values:
    a. For a login connection, select Be made using this security context.
    b. Enter the user name and password for connecting to the ServiceNow instance.
    c. Click OK.
    Odbc sqlserver security.png
  7. Navigate to Server Objects > Linked Server > Providers and double-click Microsoft OLE DB Provider for ODBC Drivers.
  8. Select the following options.
    • Nested Queries
    • Level zero only
    • Support 'Like' operator
    Tip: ServiceNow recommends running the third-party provider in the out-of-process mode setting (AllowInProcess=FALSE). If you run the provider in-process (within the same process as SQL Server), then any issues with the provider can affect the SQL Server process, which in turn could result in crashing SQL server.
    Sqlserver provider options.jpg
  9. Test your connection by selecting the newly created linked server SERVICENOW and selecting Test connection.
  10. Execute the following query in a query builder window to retrieve some results.
    Odbc sqlserver query.png

4.1 SQL Server Connection String

To use the ODBC driver directly in SQL Server 2008, specify the connection string in the following format.

Note: The latest SQL Server 2008 patches are required for the ability to specify a connection string in the user interface, via the SQL import wizard

4.2 Using sp_addlinkedserver

The following example creates a linked server named "ServiceNow ODBC" that uses the Microsoft OLE DB Provider for ODBC (MSDASQL) and the data_source parameter

EXEC sp_addlinkedserver 
   @server = N'ServiceNow ODBC', 
   @srvproduct = N'',
   @provider = N'MSDASQL', 
   @datasrc = N'ServiceNow';

After creating the linked server, you must update its properties to specify the login credentials.

5 Troubleshooting Linked Server

You may encounter an error when configuring a linked server with the ODBC Driver. Solutions to common errors are available.

Comprehensive troubleshooting resources are available in the HI knowledge base.

5.1 Access Denied Errors

You may encounter a permissions error when running a query.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "SERVICENOW" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "SERVICENOW".

To resolve this error, configured the linked server to run with an out-of-process provider.

5.2 Number Precision Errors

You may encounter precision errors querying for decimal or number field values using the OPENQUERY syntax with the ODBC driver. In this case, use the Cast syntax to convert the precision. For example:

select * from OPENQUERY(SERVICENOW, 'select Cast(sys_mod_count as Decimal(38,0)), number, short_description from incident')
Was this article helpful?
Yes, I found what I needed
No, I need more assistance