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

[krpurcel]

David,

Thanks for your reply.   Per the Wiki article Using Interactive SQL With ODBC,

"Make sure to include the semicolon at the end of your query statement. You will be presented with a 'Cont>' prompt otherwise."

I have tried it both ways.   Without the semicolon you receive the Cont> prompt as explained in the article.   You get the Cont> prompt, and there is nothing after it.   With the semi colon you get the message that you are not connected to any database.

Thanks,

Kevin

[dhoffman]

Kevin Purcell wrote:



David,



Thanks for your reply.   Per the Wiki article Using Interactive SQL With ODBC, 



"Make sure to include the semicolon at the end of your query statement. You will be presented with a 'Cont>' prompt otherwise."



I have tried it both ways.   Without the semicolon you receive the Cont> prompt as explained in the article.   You get the Cont> prompt, and there is nothing after it.   With the semi colon you get the message that you are not connected to any database.



Thanks,

Kevin

Right, you're still in the connect statement (which doesn't need a semicolon).   The SQL query statement (begins with select) does need a semicolon.

At any rate, it looks like the username you're using has a period in it, which probably confuses the Interactive SQL application (it's very barebones most likely).

From: Testing the ODBC Driver

If the username or password contains special characters, wrap the value in quotes, such as "odbc.user"*"p@ssword"@ServiceNow

I bet all you need is some quotation marks and iSQL should work.

[krpurcel]

David,

Thank you.   We are making progress.   The missing quotation marks was the problem.   Once that was resolved I did get results when trying to connect using ISQL.   I've attached the full ODBC results. I executed the SELECT statement.   Those results are at the very bottom of the attachment.  

The error message says, "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"

I ran both the instance SCHEMA and the instance XMLStats.   I've attached both in case they are useful, but I still don't understand where the problem is. Can you tell me what the next step would be?

Thanks,

Kevin

[tony_barratt]

Hi Kevin,

I have been away for a few days.

This message "Cannot create schema. Cannot retrieve a DB schema.. " is a bit puzzling.

Can you check that the requirements are met for the operating systems as per :

Installing the ODBC Driver - ServiceNow Wiki

What version is the ODBC Driver you have installed? 1.0.9 is the latest.

It might be worthwhile trying the ISQL as an admin user to eliminate permissions of the user.

Best Regards

Tony

[vinothkumar]

Hi David,

I am also getting the same error message, which you have mentioned. Could you please help me to proceed further on this?

The error message says, "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"