SalesForce ODBC - SQL Error - SAS

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
rhunter
Posts: 2
Joined: Tue 07 May 2019 10:43

SalesForce ODBC - SQL Error - SAS

Post by rhunter » Tue 07 May 2019 11:01

I'm using SAS to extract data from SalesForce.

Having a problem with getting data from a SalesForce table called Case.
The error is as below.
ERROR: CLI describe error: [Devart][ODBC][Salesforce]You have an error in your SQL syntax at line 1, column 15: Unexpected symbol 'case'
This happens when I try to set the table or submit a query with a pass-through.

Code: Select all

libname sforce odbc dsn='saleforce' user = 'username' password = 'password';

data case;
set sforce.case;
run;

proc sql;
connect to odbc as salesforce (dsn='salesforce' user = 'username' password = 'password');
create table t01 as select * from connection to salesforce(
	select * from Case;
disconnect from salesforce;
quit;
Can anyone help me with this or suggest a work-around?
I believe it occurs because 'case' is an sql keyword.

rhunter
Posts: 2
Joined: Tue 07 May 2019 10:43

Re: SalesForce ODBC - SQL Error - SAS

Post by rhunter » Tue 07 May 2019 22:37

I found a solution for the pass-through method which is to wrap the table name in double quotes "table_name" or square brackets [table_name].

Code: Select all

proc sql;
connect to odbc as salesforce (dsn='SalesForce' user = 'username' );
create table t01 as select * from connection to salesforce(
	select * from "Case");
disconnect from salesforce;
quit; 
** Note password and security token is missing from the connection string as they are saved in the System DSN on the SAS Server

MaximG
Devart Team
Posts: 1395
Joined: Mon 06 Jul 2015 11:34

Re: SalesForce ODBC - SQL Error - SAS

Post by MaximG » Wed 08 May 2019 13:06

Indeed, you have to put quotes around object names if they match the reserved keywords.

Post Reply