ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

[RichM]

I'm getting the following error when I run this SAS code.  The backend database is SQL Server (13.0.4422.0) connecting via ODBC.  They used a restricted keyword as a column name, which is causing the error.  I'm not sure the implications of changing the column name.  Is there a way to modify my sas so I can keep the keyword column name.  BTW: This runs under 9.2.  I'm porting the code to 9.4. Also, I tried enclosing constraint with backets [constraint]

proc sql noprint;

                               select distinct constraint into :PBD_binary_constraints separated by " " from warehse.cmmn_opt_assetbounds

                                                where event_multiplier = 0 or event_multiplier = .;

quit;

ERROR: CLI describe error: [DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near the

        keyword 'constraint'.

[kiranv_]

I am not very sure about this. but try

options NODBIDIRECTEXEC;

You can create a sas table in explicit pass through with a new column name and then try the query below with SAS table

[ChrisNZ · Accepted solution]

It's Microsoft's own ODBC driver that chokes it seems.

Maybe try

  select distinct constraint as CONS

or

  select distinct 'constraint'n as CONS

Otherwise explicit passhtrough should work:

proc sql;

 connect using warehse;

 select * from connection to warehse (select distinct constraint as CONS..);

[RichM]

Please note adding the modifying the sql didn;t work.  I was getting the same error

Maybe try

  select distinct constraint as CONS

or

  select distinct 'constraint'n as CONS

The explicit passhtrough worked but I had to pull back the whole table (luckily, not a big table)

proc sql;

 connect using warehse;

 select * from connection to warehse (select distinct constraint as CONS..);

[Kurt_Bremser]

So someone used a variable name in MS SQL server that is invalid there and causes the MS ODBC connection to crash.

The correct remedy is to not use the keyword "constraint" as a variable name, which is sound programming practice in the first place.

Have the mistake fixed on the SQL server side in the database.