Executing Oracle Scheduler Job from SQL Server .
Date: Wed, 25 Mar 2020 16:23:26 -0400
Message-ID: <CAM5KiKpGWbm_mTwjHJOVs7-QJDMrvM9f=mNTJYDA16KPGbGi8w_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 25 2020 - 21:23:26 CET