SalesForce ODBC - SQL Error - SAS

[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.

[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

[MaximG]

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

[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

[MaximG]

Try executing the needed query using next method :

Code: Select all

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

[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

[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.