When you use SAS/ACCESS® Interface to Microsoft SQL Server against tables with reserved names such as GROUP or ORDER, the following error can occur:
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near the keyword 'group'.
The keyword in the error message varies depending on the reserved name of the table. The problem does not happen with SAS/ACCESS® Interface to ODBC.
Here is example code that can cause the error:
libname test2 sqlsvr dsn=data-source-name;
proc sql;
select * from test2.group;
quit;
The problem occurs because the ODBC driver by default does not use quoting for table names. To circumvent the problem, do one of the following:
- Enable EnableQuotedIdentifiers in the ODBC driver entry. If you are on a UNIX system, you can add EnableQuotedIdentifiers=1 to the odbc.ini file. If you are on a Windows system, select Enable Quoted Identifiers on the Advanced tab of your ODBC driver entry as shown here:
- Avoid reserved words such as GROUP and ORDER when naming your Microsoft SQL Server tables.