03-12-2015 12:06 PM
Our SQL Server DBA's have identified a number of unexpected and failed connections which we have tracked down to our use of Pre-Assigned libraries - the main way to solve this seems to be for us to use the Defer=Yes option in libnames or the 'Whether to defer a connection until needed' to Yes in library settings in SAS management console.
DEFER=LIBNAME option SAS note is here: SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition
The question we have about this is are there any reasons why we shouldn't use this as default? (testing has show that this will remove un-needed SQL Server connections and any failed connections due to users not having permissions for certain databases)
This is the option in question in SAS MC (which we've set to Yes in our test environment)...
Thanks for any responses in advance,
03-12-2015 12:20 PM
As you are also needing authentication the option of "defer=yes" is very profitable by avoiding failed attempts. It will only open up the library and do the authentication (login) when the data is needed.
For minimizing connections use the option for that eg Oracle SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition
The benefit is the options of using temporary tables.
Why these are not the default settings. Probably SAS build the interface in the days one connection with a shared group account was the standard way of doing it.
Not acceptable these days but a change of default is hard to get accepted.
03-12-2015 04:25 PM
We have made DEFER = YES our default set up for SQL Server libraries and we haven't had any problems or issues with that. If should speed up connecting to your SAS server as well via EG etc.
03-13-2015 05:56 AM
Thank you both for you responses - based on our testing and your responses.
We are going to deploy the use of defer=yes to our production environment.