topic Re: SAS & Windows Task Scheduler Error in SAS Programming

SAS & Windows Task Scheduler Error

Jlochoa — Tue, 16 Feb 2016 23:00:28 GMT

I have an interesting situation with SAS and Task Scheduler.

OS:	win7
Product:	Base SAS
Release:	9.4 TS1M2

I created a SAS program that establishes a LIBNAME connection using the following statement

libname ship oledb user='xyz' password='abc' datasource='RDBP' provider='OraOLEDB.Oracle.1' schema='VAL_DW';

The problem appears when I use Task Scheduler to start the SAS program via a batch file. The error is "Error trying to establish connection: Unable to Initialize: ORA-12154: TNS:could not resolve the connect identifier specified. Error in LIBNAME Statement.

I've checked the Task Scheduler configurations are correct. I have admin rights on the PC.

Why are there no errors when I run the SAS program via the batch file, yet there are errors when Task Scheduler runs the batch file/SASprogram?

Re: SAS & Windows Task Scheduler Error

LinusH — Wed, 17 Feb 2016 06:54:01 GMT

What user runs the task?
If system, test with another account.
If you have UAC activated try using a non admin account.

Re: SAS & Windows Task Scheduler Error

Kurt_Bremser — Wed, 17 Feb 2016 07:35:24 GMT

Looks like your scheduled job runs in a different context (other user? missing/not executed init file?)

Re: SAS & Windows Task Scheduler Error

Jlochoa — Wed, 17 Feb 2016 12:26:52 GMT

task Scheduler has the option to select user to run task. I have myself as the user. I will change to another user

Re: SAS & Windows Task Scheduler Error

Jlochoa — Wed, 17 Feb 2016 12:28:03 GMT

Not sure what is meant be "missing/not executed init file".

Thanks.

Re: SAS & Windows Task Scheduler Error

Kurt_Bremser — Wed, 17 Feb 2016 12:33:53 GMT

Access to a RDBMS usually requires a lot of specifications. In DB/2 (for ex), this is done via "instances", where each instance holds the configuration for a specific database. This instance needs to be activated (by running a script or batch that sets environmnent variables) before the DB becomes accessible.

This is what I meant by "missing/not executed init file".

Re: SAS & Windows Task Scheduler Error

Jlochoa — Wed, 17 Feb 2016 13:34:58 GMT

Are the environment variables set as part of the LIBNAME statement as options and/or property settings?

Re: SAS & Windows Task Scheduler Error

Kurt_Bremser — Wed, 17 Feb 2016 13:52:46 GMT

I can only speak from my experience with DB/2 (on a Linux server) and SAS on an AIX server.

I needed to install the DB/2 client on AIX

Then I needed to create an "instance" (which is actually a userid that has the config files in its home directory)

Then I had to set the same environment variables that were set in the "instance" in the shell script that starts SAS. Now, when SAS calls the external DB/2 client, the client determines the connection parameters from the environment variables that were gleaned from the "instance".

Sounds complicated, is complicated, and is the reason why data transfer is handled via database unloads into flat files over here.

Apart from the fact that it spares license costs.

Re: SAS & Windows Task Scheduler Error

Kurt_Bremser — Wed, 17 Feb 2016 13:54:54 GMT

I guess (and it is a wild guess) that when you are working frrom the desktop, the connection parameters are present in your users's environment. When you try to run the same thing from the scheduler, some of that is missing, and the DB connection fails.

Re: SAS & Windows Task Scheduler Error

Jlochoa — Wed, 17 Feb 2016 17:01:27 GMT

I figured out a work around by changing the Libname statement.

Old versions that did not work with Task Scheduler

libname ship oledb OLEDB_SERVICES=NO REQUIRED=YES COMPLETE=YES init_string="Provider=OraOLEDB.Oracle.1;Password=xxxxx;Persist Security Info=True;User ID=yyyyy;Data Source=RDBP" schema=VAL_DW;
 
libname ship oledb user='xxxxxx' password='yyyyy' datasource='RDBP' provider='OraOLEDB.Oracle.1' schema='VAL_DW';
 
LIBNAME ship OLEDB
PROVIDER="OraOLEDB.Oracle.1"
PROPERTIES=(
"user id"=xxxxx
password=yyyyy
"data source"=RDBP
schema=VAL_DW

New version that works with Task Scheduler

libname ship oledb INIT_STRING="PROVIDER=OraOLEDB.Oracle.1;Data Source=(DESCRIPTION=(SDU=16384)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=DBP.com)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=RDBP.com))); Password=xxxxx;User ID=yyyyy;Persist Security Info=True" SCHEMA=VAL_DW  dbmax_text=12000;

I still curious why the old versions don’t work with Task Scheduler.

Re: SAS & Windows Task Scheduler Error

SASKiwi — Wed, 17 Feb 2016 18:35:28 GMT

My guess is the datasource is not being picked up for some reason hence the need to specify and initstring to replace it.

Where is the datasource RDBP defined?

Re: SAS & Windows Task Scheduler Error

Jlochoa — Wed, 17 Feb 2016 21:48:53 GMT

Found another LIBNAME statement workaround. Interesting how there are multiple ways of creating LIBNAME statements.

libname ship oledb INIT_STRING="PROVIDER=OraOLEDB.Oracle.1;Data Source=//RDBP.com:1521/RDBP.na.com; Password=zzzzzz;User ID=yyyyy;Persist Security Info=True" SCHEMA=VAL_DW  dbmax_text=12000;

Source: Oracle Call Interface Programmer's Guide

Database Connection Strings for OCI Instant Client The OCI Instant Client can make remote database connections in all the ways that ordinary SQL clients can. However because the Instant Client does not have the ORACLE_HOME environment and directory structure some of the database naming methods will require additional configuration steps.

All Oracle net naming methods that do not require use of ORACLE_HOME or TNS_ADMIN (to locate configuration files such as tnsnames.ora or sqlnet.ora) work in the Instant Client mode. In particular, the connect_identifier in the OCIServerAttach() call can be specified in the following formats:

■ A SQL Connect URL string of the form: //host:port/service name such as: //dlsun242:5521/bjava21

■ As an Oracle Net connect descriptor. For example: "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=dlsun242) (PORT=5521)) (CONNECT_DATA=(SERVICE_NAME=bjava21)))"