topic Re: ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0) in SAS Programming

ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

RichM — Mon, 19 Jun 2017 20:28:10 GMT

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

Re: ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

kiranv_ — Tue, 20 Jun 2017 01:01:51 GMT

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

Re: ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

ChrisNZ — Tue, 20 Jun 2017 02:51:14 GMT

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

Maybe try

select distinct constraint as CONS

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..);

Re: ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

Kurt_Bremser — Tue, 20 Jun 2017 06:16:46 GMT

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.

Re: ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

RichM — Tue, 20 Jun 2017 21:39:58 GMT

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

Maybe try

select distinct constraint as CONS

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..);