Page 1 of 1
SalesForce ODBC - SQL Error - SAS
Posted: Tue 07 May 2019 11:01
by rhunter
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.
Re: SalesForce ODBC - SQL Error - SAS
Posted: Tue 07 May 2019 22:37
by rhunter
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
Re: SalesForce ODBC - SQL Error - SAS
Posted: Wed 08 May 2019 13:06
by MaximG
Indeed, you have to put quotes around object names if they match the reserved keywords.
Re: SalesForce ODBC - SQL Error - SAS
Posted: Thu 31 Oct 2019 10:14
by ReCorn066
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
Re: SalesForce ODBC - SQL Error - SAS
Posted: Thu 31 Oct 2019 15:39
by MaximG
Try executing the needed query using next method :
Code: Select all
SELECT * FROM OPENQUERY([salesforce_prod], 'SELECT * FROM "Case"')
Re: SalesForce ODBC - SQL Error - SAS
Posted: Thu 31 Oct 2019 16:00
by ReCorn066
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
Re: SalesForce ODBC - SQL Error - SAS
Posted: Mon 04 Nov 2019 15:48
by MaximG
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.