Re: Executing Oracle Scheduler Job from SQL Server .

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Wed, 25 Mar 2020 17:31:48 -0500
Message-Id: <44E42751-6214-4344-BAC5-72EC030B3330_at_gmail.com>



that probably means that the oracle connection information is configured and owned by the windows user ID only, and not accessible to the SQL Server login. The error message indicates a connection problem on the SQL Server side.

Sent from my iPhone

> On Mar 25, 2020, at 5:24 PM, Zahir Mohideen <zahir.dba_at_gmail.com> wrote:
> 
> 
> 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
Received on Wed Mar 25 2020 - 23:31:48 CET

Original text of this message