April 5, 2008 at 4:21 am
I have two servers which one is SQL Server 2005 and the othe one is Oracle 10g.
A linke servre is stablished between these tow systems , ofcourse the linked server is defined at SQL 2005 side for one of SQL local logins.
There is an SP which must run at the SQL side , and its duty is updating a table in SQL by using a table in Oracle side.
When the valid user ( in linked server ) runs this SP , it runs succesfully. But when this SP is scheduled as a job , it failed to run.
All the SQL agents' Log on Account is a local account in the windows like USERA. And because USERA is not permitted to use the linked servre , when the job attemp to run with USERA, it fails.
It must be mentioned that USERA must not be added to linekd server for the security reasons.
Is there any way to schedule this SP tha could be run?
April 5, 2008 at 9:18 pm
It is failing because USERA does not have permission to access Oracle table. you need to type in userid and password in security tab of linked server. To do this:
Select last radio button "Be made using this secuity context" to do this. or you may map Local Server login to remote server login in security tab.
As far as running SP is concern so you need turn on RPC under Server Options
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 19, 2008 at 4:05 am
So many thanks
as i`ve told the USERA can not be paticipate in linked server because of security reasons.
May be i have to use SSIS to solve the problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply