ODBC connection issue

[krpurcel]

I'm trying to create an ODBC connection to ServiceNow per these two resources:

https://hi.service-now.com/kb_view.do?sysparm_article=KB0540707 — Allows you to download and install the 32 bit or 64 bit drivers for the ServiceNow ODBC connection and automatically created the System DNS.

http://wiki.servicenow.com/index.php?title=Using_ODBC_Driver_in_SQL_Server - shows how to set up your connection in SQL Server.

I installed the 32 bit ServiceNow driver.   Once that was done I was able to connect to my local database in SQL Server. When I try and follow the instructions to use the ODBC driver, I get to step 6. Select Security from the Select a page list, and enter the following security values. When I click OK at the end of step 6, I get the following error message. (See ServiceNow 32Bit error message attachment).

The error seems to indicate I am using the incorrect driver.   So after receiving it I uninstalled the 32 bit driver and installed the 64 bit driver. When installing the 64 bit driver, I get an error at step 6 that says "the server was not found or was not accessible". (See ServiceNow 64Bit error message attachment), and I am then unable to connect to my local database in SQL Server.

So I am unable to proceed past step 6 in the article.

Can anyone shed some light on what is happening and what I need to do to correct it?

Thanks!

Kevin

[jleon]

Kevin

I had the same issue that you described:

"Error Message = [SN]{[ODBC ServiceNow driver][OpenAccess SDK SQL Engine] Cannot create schema. Cannot retrieve a DB schema.   Please run <instance>?SCHEMA in your browser and try again. Also make sure that the table desciptor cache can hold all your tables and DB views.   You can check the table descriptor stats running <instance>/xmlstats.do"

using the ServiceNow ODBC 64-bit_1.0.11 installed at a 64 bit Windows AWS virtual machine connecting to a ServiceNow Helsinki DB

I found the solution downgrading the ODBC version. I had to uninstall it, and install the:

ServiceNow ODBC 64-bit_1.0.7.3

I hope this can help you

Regards

Jesus

[dhoffman]

Regarding your 32-bit error message "architecture mismatch" error, I got that as well when using 64-bit SQL Server Management Studio and 64-bit SQL Server Reporting Services (SSRS).   I simply switched to the 64-bit ServiceNow ODBC driver.

...

and promptly got a new set of errors.

[tony_barratt]

Hi David,

If you get a error message   containing "architecture mismatch" you can assume your ODBC Driver is the wrong bitness.

When you have an installed ODBC Driver of 32 or 64 bit as appropriate, IMO the the isql query mentioned above should be run.

Also. just to help a bit with the "bitness":

ServiceNow KB: Troubleshooting Linked Server (KB0538992)

..

6. Are you trying to configure Microsoft SQL Server Integration Services (SSIS) to work with ODBC?

Please ensure that you are using the 32-bit version of the ODBC driver regardless of the OS bitness.

..

Best Regards

Tony

[dhoffman]

A colleague successfully created a Linked Server on his Win7 laptop with SQL Server Management Studio 2012 (64-bit) using the ServiceNow ODBC Driver 1.0.9 (64-bit).

When trying to view the database schema (expanding the Tables or run a query), Error 7399 happens:

Our ultimate goals are to be able to query ServiceNow using:

• SQL Server Reporting Services (SSRS) 2012; and
• SQL Server Management Studio 2012

[tony_barratt]

Hi David,

Error 7399?

Using ODBC Driver in SQL Server - ServiceNow Wiki

..

4.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.

Permissions needed to set up linked server with out-of-process provider - Microsoft BI & Data Access...

Is "Allow in process" unticked?

Using ODBC Driver in SQL Server - ServiceNow Wiki

Best Regards

Tony