BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jlochoa
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Jlochoa
Obsidian | Level 7

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

 

View solution in original post

11 REPLIES 11
LinusH
Tourmaline | Level 20
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
Jlochoa
Obsidian | Level 7

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

Jlochoa
Obsidian | Level 7

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

 

Thanks.

Kurt_Bremser
Super User

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

Jlochoa
Obsidian | Level 7

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

Kurt_Bremser
Super User

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.

Kurt_Bremser
Super User

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.

 

Jlochoa
Obsidian | Level 7

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. 

SASKiwi
PROC Star

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?

Jlochoa
Obsidian | Level 7

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

 

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 Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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