BookmarkSubscribeRSS Feed
Quentin
Super User

I'm running an explicit pass-through query against a MS SQL Server database from SAS 9.4 on Linux, and getting intermittent connection errors:

 

ERROR: CLI error trying to establish connection: [SAS][ODBC 20101 driver]252 : [SAS][ODBC 
       20101 driver]1243

 

When this happens I can resubmit the code immediately, and it runs fine.

 

I suspect something is timing out in the handshake between SAS and SQL server, so I want to extend the SQL Server connection timeout limit.

 

My usual CONNECT statement uses an AuthDomain to keep userid / password out of the code. It looks like:

Connect to sqlsvr as MyDB (datasrc=MyDB AUTHDOMAIN="MyDomain");

 

It looks like I can add the timeout limit by passing the full connection string:

  Connect to sqlsvr as MyDB (required="dsn=MyDB; uid=me; password=mypassword; Connect Timeout=30");

 

But I don't want to put the user name and password into my code.  Is there any way I can add the Connect Timeout option to my connection string while at the same time using the authdomain to pass the userid and password?

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
1 REPLY 1
JohnSAScom
Quartz | Level 8

Hi Quentin,

 

I've noticed that I got the "CLI error" when the file I was trying to access was already open in another process, and so needed to be closed first before I tried to access it again.

 

John

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1351 views
  • 0 likes
  • 2 in conversation