Re: Executing Oracle Scheduler Job from SQL Server .

  • From: Tim Gorman <tim.evdbt@xxxxxxxxx>
  • To: zahir.dba@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 25 Mar 2020 15:04:25 -0700

Zahir,

ORA-12154 indicates that you are having basic TNS connectivity problems, such as being unable to access the host or port specified in the TNS name definition.  Google it for more information.

Use "ping" to prove that you have basic connectivity to the host first, then next try "curl -v telnet://<IP-address>:<port-nbr>" to prove that you have connectivity to the host and port, then finally use "tnsping" to prove that you have connectivity to the host, port, and ORACLE_SID.

Then perhaps try this operation again?

Hope this helps,

-Tim


On 3/25/2020 1:23 PM, Zahir Mohideen wrote:

I am sorry , if it is a wrong forum to post this question.

I have an oracle scheduler job that takes the data from MS SQL Server.
This process is dependent on the pre-processing of the SQL server data.
Therefore , I have created SQL Server Agent Job with step 1 for the pre-process and step 2 for execute oracle scheduler job.

This step 2 works fine  with my windows login ( I am the superuser having sysadmin , serveradmin privileges).
But , if I execute the step 2  with SQL server login ( even with database owner privilege) , it errors out with ORA-12154.
What other privileges does this user need to execute Oracle Scheduer job ?

Thanks in advance.

*Windows ID*

C:\>sqlcmd -S coursenc02,1460 -W
1> Execute ( 'call dbms_scheduler.run_job(''ORA_DATA_REFRESH'')') AT ora_dwcs;
2> go

(0 rows affected)

*SQL Server ID*

C:\>sqlcmd -S coursenc02,1460 -U orausr -P pass1234
1> Execute ( 'call dbms_scheduler.run_job(''ORA_DATA_REFRESH'')') AT ora_dwcs;
2> go
Msg 7303, Level 16, State 1, Server coursenc02, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ora_dwcs".
OLE DB provider "OraOLEDB.Oracle" for linked server "ora_dwcs" returned message "Error while trying to retrieve text for error ORA-12154".
1> exit




Other related posts: