ODBC Schema error

[Chris198]

Good day all. I hope you can help me out with the following :

For our program/project processes there is a need to get certain data from Service Now with regards to Changes / Incidents, Problems or Requests using a tool I've written myself, and for an interface (one way to my Db) I'm going to write.

This Interface will get certain details based on the

INC         Incident /  REQ        Request /  PRB        Problem or RITM     Requested Item, and related Catalog Tasks :
-SCTASK               Catalog Tasks, that will be entered manually in our tool after creation in Service now. That will be the link and trigger for the interface to retrieve initial values and keep track of the state until the specific number is closed in Service Now.

That's for some background. I can connect successfully using the ODBC connection. I have been granted the ODBC, soap_query rest and api_explorer roles.

My Issue is that with every query I run I get the following error :

The following error information describes the failure

ODBC Call     = SQLPrepareW()

SQL State     = HY000

Native error  = 1050(41A)

Error Message = [SN][ODBC ServiceNow driver][OpenAccess SDK SQL Engine]Cannot cr

eate schema.Cannot retrieve a DB schema. Please run <instance>?SCHEMA in your br

owser and try again. Also make sure that the table descriptor cache can hold all

your tables and DB views. You can check the table descriptor stats runing <inst

ance>/xmlstats.do in your browser.

Elapsed time 1470 ms.

Elapsed time: Prepare 0 ms.      Execute 0 ms.   Fetch results 1470 ms.

ISQL>

Running Instance?SCHEMA gives me back a correct XML,

Test query used : in ISQL :  SELECT "incident"."number" FROM "SCHEMA"."OAUSER"."incident" WHERE number='INC0000000';

Does this behavior sound familiar, and more important what is the solution ? 🙂

Thanks in advance,

Chris

[Bill_Ymr_61]

We recently had this issue in our instance.  The issue ended up being that the instance name had not been updated in the ServiceNow Management Console; which is installed with the driver.

Try the instructions at the link below:

https://docs.servicenow.com/bundle/jakarta-application-development/page/integrate/odbc-driver/task/t...

[Greg45]

I also saw similar problems when I was trying to run the 64bit drivers on 32bit java.

[pavelmuller]

Hi Chris, 

IMHO, connecting live to your ServiceNow instance using ODBC is not the best idea from the architecture point of view. Imagine that it was not ServiceNow but an Internet Banking system. Would you run reporting against a production IB?

I think the best practice is to build a copy of the data in some kind of a reporting database and to connect your tools there without affecting your production environment.

You can use SnowMirror for example to build such a reporting database.

http://www.snow-mirror.com/

Kind Regards,
Pavel