question Re: Unable to query sys_choice table via ODBC in Developer forum

Unable to query sys_choice table via ODBC

hemant10 — Tue, 24 Nov 2015 11:05:52 GMT

Hi,

I am relatively new to SNOW. I was trying to connect to SNOW database via ODBC. The idea is to pull data into a local SQL Server database.

I was successfully able to create a linked server and connect to the SNOW db. I am also able to query the Incident and few other tables successfully.

However when I try to query the sys_choice table, I get NULL data for most of the columns.

Can someone help here? IS there anything special about the sys_choice table?

Re: Unable to query sys_choice table via ODBC

tony_barratt — Tue, 24 Nov 2015 12:40:35 GMT

Hi Hemant,

You could try a couple of things to get some insight.

If you log in to the instance and carry out a sys_choice.list, do you see data in all the columns?

If you run an ISQL query against sys_choice what do you see?

Finally what version of ODBC driver do you have deployed?

Best Regards

Tony

Re: Unable to query sys_choice table via ODBC

hemant10 — Wed, 25 Nov 2015 08:15:56 GMT

Hi Tony,

When I login to SNOW using the account I created specifically for ODBC (role soap_query was given to this account), I am not able to see sys_choice.list table at all.

When I login to SNOW using other admin account I see data in all the columns in sys_Choice.list.

The ODBC driver version is : 1.0.9 64-bit

I had never used ISQL query before. I am using SQL Server Management Studio (SSMS) to run my queries. I tried using ISQL today. But it doesn't work even for the Incident table (Incident table works for me when I query via SSMS). In ISQL it says 'You are not connected to any database.' I am using the correct connect syntax I believe: connect username*password@Service

Re: Unable to query sys_choice table via ODBC

tony_barratt — Wed, 25 Nov 2015 08:34:20 GMT

Hi Hemant,

It seems likely your user cannot read the sys_choice table.

ISQL is another issue.

Check this out to fix the "your user cannot read the sys_choice table" issue.

Getting Started with ODBC - ServiceNow Wiki

4.1 Set User Permissions

Since the ODBC driver communicates with the instance using SOAP, the user account must have the soap_query role. The soap role also provides query access through SOAP, but allows additional actions that are not available through ODBC, such as insert or update.

The user account must also be able to read records on the tables you want to query. For example, to query incident records, the ODBC user must have read permission for the Incident table. You can create a new role, for example a role called ODBC, grant that role to the ODBC user, and configure ACL rules to allow users with that role to query the Incident table.

To create the ODBC role and grant the ODBC user the required roles:

Navigate to User Administration > Roles.
Click New.
In the Name field, enter ODBC.
Click Submit.
Navigate to User Administration > Users.
Select the ODBC user account you created.
In the Roles related list, click Edit.
Use the slushbucket to add the ODBC and soap_query roles.

To define ACL rules for the ODBC role:

Elevate the session permissions so you can create ACL rules.
Navigate to System Security > Access Controls (ACL).
Click New.
From the Operation choice list, select read.
From the Name choice list, select Incident [incident].
Leave the second Name choice list as None.
Right-click the form header and select Save.
In the Requires role related list, click Edit.
Use the slushbucket to add the ODBC role.

Best Regards

Tony

Re: Unable to query sys_choice table via ODBC

hemant10 — Wed, 25 Nov 2015 09:29:02 GMT

Hi Tony,

I did follow all the steps defined above.

Here is the query I am using:

select * from OPENQUERY(SERVICENOW,'select [label],element,dependent_value,[hint],language,cast(sequence as int),sys_id,value from sys_choice order by [label] asc')

I am getting correct data for only 'label' column. All other columns show up as NULL, though they have data in them.

Re: Unable to query sys_choice table via ODBC

hemant10 — Wed, 25 Nov 2015 09:35:23 GMT

Hi Tony,

I created a ACL on column 'element' and I was able to pull data for that column. Will try to create ACLs for other columns as well and see if it works. But not sure why I have to create at column level when I already have one at table level.

Thanks,

Hemant

Re: Unable to query sys_choice table via ODBC

tony_barratt — Wed, 25 Nov 2015 10:06:37 GMT

Hi Hemant,

Good news!

You can OK all the fields at the same time with a sys_choice.*

Best Regards

Tony

Re: Unable to query sys_choice table via ODBC

hemant10 — Wed, 25 Nov 2015 10:21:17 GMT

Thanks Tony. sys_choice.* works well..

Cheers!

Re: Unable to query sys_choice table via ODBC

tony_barratt — Wed, 25 Nov 2015 11:28:36 GMT

Hi Hemant,

Excellent news!

All good now? If not, can you update with further questions.

If yes consider marking the question as answered, and marking replies as helpful as appropriate.

Best Regards

Tony