ERROR: [Microsoft SQL Server]The batch could not be analyzed because of compile errors.

[FK1]

Hi Folks,

I do have a problem with the datasource (driver ???):

when firing this SAS code:

LIBNAME DATAMART sqlsvr schema='SAS_DATAMART' INSERTBUFF=32767 READBUFF=32767 DBCOMMIT=0 Datasrc=SAS_ACCESS_TREIBER;

proc freq data=DATAMART.CIContact;
table  CompanyCode;
run;

I get the following error:

ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'SAS_DATAMART'.
ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile errors.

However, when using the other datasource in the Libname Statement, everything works fine:

LIBNAME TSQL sqlsvr schema='SAS_DATAMART' INSERTBUFF=32767 READBUFF=32767 DBCOMMIT=0 Datasrc=MICROSOFT_SQL_TREIBER;


proc freq data=TSQL.CIContact;
table CompanyCode;
run;

The reason, I have two similar datasources defined is, that we are using SAS Marketing Automation and we found out, that the performance on the database is much better when using SAS Access Driver from Data Direct (s0sqls27.dll) rather than using ODBC Driver 17 for SQL Server from Microsoft.

Unfortunately, it seems as if SAS Access driver causes troubles when using "outside" of SAS Marketing Automation context!

Could I change something regarding the datasourve definition via Windows ODBC Admin Client in order to make the whole thing work?

[ChrisNZ]

Maybe try removing options insertbuff etc, as they may not be supported by the MA connection.

[FK1]

Hi Chris,

tried it out. The are supported. It works fine...

[Patrick]

I was in the believe that the SAS provided driver is based on Data Direct. Is that the one which doesn't provide you with sufficient performance?

From the docu here

SAS/ACCESS Interface to Microsoft SQL Server
Base SAS is required for the installation of SAS/ACCESS Interface to Microsoft SQL Server.
SAS/ACCESS Interface to Microsoft SQL Server includes the required ODBC Driver

May be worth to get in contact with SAS Tech Support.

[FK1]

Hi Patrick,

no, the Data Direct Driver is the one, which performs better, i.e. faster write and read figures from and to the Database. But this one causes the Error message when accessing Database tables within EG by using procedures like "PROC FREQ"....

I already got in contact with Tech Support.

[FK1 · Accepted solution]

Hooray, Tech Support gave the decisive hint:

Within the Windows ODBC Datasource Administrator on the "Advanced" Tab you have to set the tick of the tick box "Enable Quoted Identifiers"