SAS pass through query to SQL Server

I have some troubles with passing through query to SQL Server. OS is RedHat 6. In SAS Enterprise Guide I try to execute this code:

LIBNAME CISCO SQLSVR  DBLIBINIT='SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING ON'  Datasrc="nikolaev.ivan.ru"  SCHEMA=cc  USER=vorob2  PASSWORD="{SAS002}9930904131DC199B130A9E7D42A49B5D" ;
proc sql;
insert into CISCO.vContact_I (FirstName, MidName, LastName, Address, Phone, MobilePhone, EMail, ClientCode, AddInfo, Sex, TOPIC_ID, CUSTOMER_RK, RESPONSE_TRACK_CD, QuestFlag, CriticalMessage, RegularMessage) 
     values ('N/A','AO Lomon', 'Воронин Саша', 'N/A/N/A/N/A мес/платеж N/A','N/A', 'N/A','N/A','01039602','',1,.,'01039602','26670201',0,'N/A','N/A');
quit;

but I faced with error:

ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Statement(s) could not be prepared.

If I try to put QUOTED_IDENTIFIER option into execute statement it works properly:

proc sql;
connect to sqlsvr as sql 
(Datasrc="nikolaev.ivan.ru"  USER=vorob2  PASSWORD="{SAS002}9930914010DC199B130A9E7D42A49B5D");
execute(
SET QUOTED_IDENTIFIER ON;
insert into cc.vContact_I (FirstName, MidName, LastName, Address, Phone, MobilePhone, EMail, ClientCode, AddInfo, Sex, TOPIC_ID, CUSTOMER_RK, RESPONSE_TRACK_CD, QuestFlag, CriticalMessage, RegularMessage) 
     values ('N/A','AO Lomon', 'Воронин Саша', 'N/A/N/A/N/A мес/платеж N/A','N/A', 'N/A','N/A','01039602','',1,'','01039602','26670201',0,'N/A','N/A');
) by sql;

Are there any opportunities to put QUOTED_IDENTIFIER into LIBNAME STATEMENT using SQLSVR? SAS/ACCESS components for ODBC and OLEDB are not licensed.

Answer

@Jdzel What type of connection do you use?

Try to add parameter CONNECTION=SHAREDREAD.
Libname stetement will be like:

LIBNAME CISCO SQLSVR  
DBLIBINIT='SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING ON' 
CONNECTION=SHAREDREAD  Datasrc="nikolaev.ivan.ru"  SCHEMA=cc  
USER=vorob2  PASSWORD="{SAS002}9930904131DC199B130A9E7D42A49B5D" ;

Advertisement

Answer