SAS batch job getting hung up for hours trying to query SQL table/view

[Mike_B]

I am using SAS 9.4m7 on Windows Server 2012 r2. I have a scheduled task on the server that runs a SAS program every hour for 12 hours. The SAS program's job is to read in a list of routine SAS reports and figure out which, if any, need to run at that particular time and then run them. It generates batch commands to run the SAS programs for those reports and executes the commands via call execute.

/*these lines will run once per SAS program*/
data batchcommands;
  length line $8191;
  set QUEUE;
  where 'SAS'=substrn(upcase(filename),length(filename)-2);
  log_string=cats('-LOG "',logpath,'\',report_name,"_&RefDateFormatted","_&RefTimeFormatted",'.log','"');
  list_string=cats('-PRINT "',logpath,'\',report_name,"_&RefDateFormatted","_&RefTimeFormatted",'.lst','"');
  prog_string=cats('-SYSIN "',program_filepath,'\',program_filename,'"');
  line=tranwrd(cats('"\\server\SASHome\SASFoundation\9.4\sas.exe" -CONFIG "\\server\SASHome\SASFoundation\9.4\sasv9.cfg" -AUTOEXEC "\\server\SASHome\SASFoundation\9.4\autoexec.sas"@',log_string,'@-NOSPLASH@',list_string,'@',prog_string,'"'),'@',' ');
run;

A few of the routine report programs access SQL servers via OLEDB. A database on one of those servers has been having intermittent issues that cause SAS to get stuck and just kind of hang in limbo for hours until either the program errors out or the scheduled task times out. I think sometimes it gets hung up while establishing the OLEDB connection and other times it gets hung up when querying a table or view, but it's hard to say because some of the logs cut off before any error message.

Example errors from different programs:

• ERROR: Open cursor error: ICommand::Execute failed. : Cannot continue the execution because the session is in the kill state.
• ERROR: Error trying to establish connection: Unable to Initialize: [DBNETLIB][ConnectionRead (recv()).]General network error. Check
  your network documentation.: [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
  ERROR: Error in the LIBNAME statement.

I have a couple questions:

1. Is there a way to identify that a particular SQL db or table/view is going to be an issue prior to SAS getting hung up?
2. As a fallback, is there a way to get a SAS procedure, program or batch job to timeout so the remaining ones can run before the scheduled task times out?

[SASKiwi]

An interesting challenge. To me the key issue is that your OLEDB database connections go into a hung state if there's a problem rather than just erroring out so your SAS programs can just carry on. We have a similar environment to you and do a lot of SQL Server database reads. However we use ODBC database connections via SAS/ACCESS to ODBC. If there's a problem with these connections, the SAS log usually reports a CLI-type error and the SAS program continues on.

Are you in a position to try ODBC instead of OLEDB? If that isn't an option then I'd suggest you open a track with Tech Support so they can check out your OLEDB configuration to see if anything can be done to fix the hung state issue.