Unable to query sys_choice table via ODBC

[hemant10]

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?

[tony_barratt]

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

[hemant10]

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

[tony_barratt]

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:

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

To define ACL rules for the ODBC role:

1. Elevate the session permissions so you can create ACL rules.
2. Navigate to System Security > Access Controls (ACL).
3. Click New.
4. From the Operation choice list, select read.
5. From the Name choice list, select Incident [incident].

   Leave the second Name choice list as None.

6. Right-click the form header and select Save.
7. In the Requires role related list, click Edit.
8. Use the slushbucket to add the ODBC role.

Best Regards

Tony

[hemant10]

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.