DATA Step, Macro, Functions and more

ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

Accepted Solution Solved
Reply
Highlighted
New Contributor
Posts: 3
Accepted Solution

ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

 

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

 

 


Accepted Solutions
Solution
‎06-20-2017 05:37 PM
PROC Star
Posts: 1,633

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

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

View solution in original post


All Replies
PROC Star
Posts: 272

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

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

Solution
‎06-20-2017 05:37 PM
PROC Star
Posts: 1,633

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

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

New Contributor
Posts: 3

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

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

 

 

 

Super User
Posts: 7,110

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 210 views
  • 2 likes
  • 4 in conversation