Re: Executing Oracle Scheduler Job from SQL Server .

Hi Andrew / Tim -

Thank you for your responses.

I was able to connect to the oracle server with no issues. And I was able to setup Linked Server ( from MS SQL 2016 to Oracle 12cR2 ) with the standard oracle user .
Tested the linked server connection successfully .

I was able to run the oracle scheduler job with no issue by using Windows Login ID.
But , only with SQL Server Login ID , I am running into issues.

Thanks for your help

Zahir Mohideen

On Wed, Mar 25, 2020 at 6:04 PM Tim Gorman <tim.evdbt_at_gmail.com> wrote:

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

--
http://www.freelists.org/webpage/oracle-l