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?
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)))"
task Scheduler has the option to select user to run task. I have myself as the user. I will change to another user
Looks like your scheduled job runs in a different context (other user? missing/not executed init file?)
Not sure what is meant be "missing/not executed init file".
Thanks.
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".
Are the environment variables set as part of the LIBNAME statement as options and/or property settings?
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.
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.
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.
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?
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)))"
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.