Re: Executing Oracle Scheduler Job from SQL Server .

That error is saying that it does not know how to connect to the oracle database. I dont know the exact syntax for your connection method, but you will need host, port, and sid or service name to connect to the oracle database. Normally the port is 1521, but that can be changed. The error message is coming from the client side, not the server side, so you are not getting to your database server.

On Wed, Mar 25, 2020 at 3:25 PM Zahir Mohideen <zahir.dba_at_gmail.com> 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
>
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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