SAS Sybase ASE, single quote, double quotes

[Shrimp]

Bonjour Community, I'm new to SAS and we're currently in a migration from older version to 9.4.

This is something that used to work, we have hundreds of scripts like this one but for some reason, what seems to work before refuse to work today, and it has something to do with quote and double-quotes.

filename h pipe 'hostname';
data _null_; infile h; input; put _infile_; run;

PROC SQL;
CONNECT TO SYBASE  (INTERFACE = "/sybase/ase/interfaces"
SERVER    = myserver
DATABASE  = mydatabase
USER      = MYUSER
PASSWORD  = MYPASSWORD);

CREATE TABLE donnees AS
SELECT * FROM CONNECTION TO SYBASE
(select *
from somedatabase..sometable
where somecolumn = "SOMETHING");

I get the following error :

ERROR: Error Code: -207 Invalid column name 'SOMETHING'.

If I replace the "SOMETHING" with 'SOMETHING', it works.

I need to make it work WITHOUT having to recode hundreds (and more) of those SAS scripts.

In Sybase, quote and double-quote, same fight, same result (most of the time).

[r_behata]

Try the below Debugging option, this would provide more details of interaction between SAS and the  Relational Database in your log.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

[Shrimp]

sastrace didn't revealed anything and I got the same error message that previously, without the trace :

ERROR: Error Code: -207 Invalid column name 'QTLA'.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL a utilis▒ (Dur▒e totale du traitement) :
real time 0.02 seconds
cpu time 0.01 seconds

As for the 

from somedatabase..sometable

The syntax is database.owner.tablename but owner can be ignored and will use the default owner.  Again this used to work perfectly and still do as long as you do not use double-quote.

[Tom]

Did you get a new version of SYBASE at the same time?  If not then it looks like you are probably using a different SYBASE engine because of the new version of SAS and there are probably some differences in the default SYBASE options being set by the driver. You could search on SYBASE forums to see what settings would cause changes in what types of quote characters are valid around column names.

Do you still have the old version of SAS running so you can test both with the trace of generated SQL on?  Perhaps the old version of SAS was automatically converting dquote to squote?  But I doubt that SAS ever does that.

You should raise a support ticket with SAS and they can help you debug the issue.

[Shrimp]

Bonjour Tom,

   no, only SAS has been upgraded.  I'm using an up to date Sybase Open Client but still the same version.  I have tested, unsing that same client, to connect onto the database and I'm having no issue at all using quote or double-quotes as it should be.

To me, the problem occur when sas is parsing the request, before it is sent to the database :

"NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements."

We're are still loking everywhere at this moment, environement variables, SAS global configuration, client configuration.

[Tom]

The SAS engine is changed (since you are using a new version of SAS) even if whatever external driver it connects to does not change. Although since you are using SAS's SYBASE engine and not ODBC it would suspect that SAS has sent you a new driver as part of the SAS/Access to Sybase.

[ballardw]

Not familiar with Sybase at all but the double periods in this line looks suspicious:

from somedatabase..sometable

So perhaps that structure is imposing a different behavior in the connection.

[Shrimp · Accepted solution]

From the start I knew it wasn't an OpenClient issue, from the server, ISQL was working as it should, never had this issue on anything else before and I've been working with Sybase for quite some time now!

So, our SAS admin got through the config files to figure out what was missing and finally found it :

export SYB_SAS_V6PASSTHRU=1

Unfortunately, I'm still waiting on info on what exactly this line does! so if you know the answer, please, share?

With this in the config files, quotes and doulbe quotes can be used interchangeably.

Thanks for you help and hope this will help someone inreturn!