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: 1430
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.

ReCorn066
Posts: 3
Joined: Fri 26 Jul 2019 11:21
Location: The Hague, The Netherlands
Contact:

Re: SalesForce ODBC - SQL Error - SAS

Post by ReCorn066 » Thu 31 Oct 2019 10:14

I'm get same type of error message, with this SQL statement in SSMS
select * from salesforce_prod...[Case]
or
select * from salesforce_prod..."Case"
errormessage
OLE DB provider "MSDASQL" for linked server "salesforce_prod" returned message "[Devart][ODBC][Salesforce]
You have an error in your SQL syntax at line 1, column 15: Unexpected symbol 'Case'".
Msg 7306, Level 16, State 2, Line 4
Cannot open the table "Case" from OLE DB provider "MSDASQL" for linked server "salesforce_prod".

with this
select * from salesforce_prod...'Case'
error
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'Case'.

Please what is the correct syntax, for reserved key words?
Thanks

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

Re: SalesForce ODBC - SQL Error - SAS

Post by MaximG » Thu 31 Oct 2019 15:39

Try executing the needed query using next method :

Code: Select all

SELECT * FROM OPENQUERY([salesforce_prod], 'SELECT * FROM "Case"')

ReCorn066
Posts: 3
Joined: Fri 26 Jul 2019 11:21
Location: The Hague, The Netherlands
Contact:

Re: SalesForce ODBC - SQL Error - SAS

Post by ReCorn066 » Thu 31 Oct 2019 16:00

Hello, I already tried with this statement
select * from openquery ( [salesforce_PROD] , 'select * from [Case]')
and that works

But my question is why are "standard" SQL statements like this
select * from salesforce_prod...[Case]
not accepted? despite the reserved keyword "Case"
from SSMS, the use of brackets [] is sufficient, to get it working

Thanks en kind regards,
Ronald

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

Re: SalesForce ODBC - SQL Error - SAS

Post by MaximG » Mon 04 Nov 2019 15:48

You are absolutely right, but some aspects of how SQL Server Management Studio works don't allow our driver to execute the query in question. We will consider adding the possibility of overcoming this restriction in the future.

Post Reply