DATA Step, Macro, Functions and more

SAS & Windows Task Scheduler Error

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

SAS & Windows Task Scheduler Error

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?


Accepted Solutions
Solution
‎02-22-2016 09:12 AM
Occasional Contributor
Posts: 17

Re: SAS & Windows Task Scheduler Error

[ Edited ]

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: //hostSmiley Tongueort/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)))"

 

View solution in original post


All Replies
Super User
Posts: 5,257

Re: SAS & Windows Task Scheduler Error

What user runs the task?
If system, test with another account.
If you have UAC activated try using a non admin account.
Data never sleeps
Occasional Contributor
Posts: 17

Re: SAS & Windows Task Scheduler Error

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

Super User
Posts: 6,942

Re: SAS & Windows Task Scheduler Error

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 17

Re: SAS & Windows Task Scheduler Error

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

 

Thanks.

Super User
Posts: 6,942

Re: SAS & Windows Task Scheduler Error

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".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 17

Re: SAS & Windows Task Scheduler Error

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

Super User
Posts: 6,942

Re: SAS & Windows Task Scheduler Error

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 6,942

Re: SAS & Windows Task Scheduler Error

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 17

Re: SAS & Windows Task Scheduler Error

[ Edited ]

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. 

Super User
Posts: 3,110

Re: SAS & Windows Task Scheduler Error

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?

Solution
‎02-22-2016 09:12 AM
Occasional Contributor
Posts: 17

Re: SAS & Windows Task Scheduler Error

[ Edited ]

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: //hostSmiley Tongueort/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)))"

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 482 views
  • 2 likes
  • 4 in conversation