Error in sqlsrv libname statement

StefDM — Mon, 24 Sep 2018 11:26:24 GMT

Dear all ,

I want to connect to a sql server db ( on another server ). I'm not using DSN names , the sql server db uses windows authentification.

Statement :

libname sdmlib SQLSVR 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=abcde;Data Source=datasourcenameab;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=wsid;Use Encryption for Data=False;Tag with column collation when possible=False';

( data source name , workstation id and catalog are example values )

Error :

ERROR: Incorrect syntax for this LIBNAME statement. This engine does not accept a physical name.
ERROR: Error in the LIBNAME statement.

Re: Error in sqlsrv libname statement

s_lassen — Mon, 24 Sep 2018 12:07:12 GMT

When you submit a statement like

libname <name> <engine> <quoted string>;

SAS expects the quoted string to be a physical file name. What you want to do is probably something like

libname <name> <engine> <engine-option>=<quoted string>;

in your actual case something like

   libname sdmlib SQLSVR complete='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=abcde;Data Source=datasourcenameab;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=wsid;Use Encryption for Data=False;Tag with column collation when possible=False';

or possibly, use the PROMPT= or NOPROMPT=. The COMPLETE option display a dialog box if some options are missing; the PROMPT options always displays options in a dialog box, the NOPROMPT never displays a dialog box (but just fails if the options are not complete).

topic Error in sqlsrv libname statement in SAS Programming

Error in sqlsrv libname statement

Re: Error in sqlsrv libname statement