February 13, 2012 at 5:18 am
We have linked server created on sql 2000 db which is pointing to sql 2008 server. In linked server we have configured sql remote account which is db-owner on target server db.
I tried to select on table from target db through linked server and its accessible.
We have created a job on our sql 2000 db, which is using following statement -
OSQL -S abcServer -d abcDB -U RemoteSql -P password -Q "Exec SPName"
Above SPName is stored procedure which is exists in sql 2000 db (under database "abcDB") which is fetching data through linked server (from sql 2008 server).
When I execute our job its throwing following error message -
Executed as user: PROD\sqlservice. Msg 18456, Level 14, State 1, Server RemoteServer, Line 1 Login failed for user 'RemoteSql'. Process Exit Code 0. The step succeeded.:crazy:
please let me know if anyone know solution on this...
February 13, 2012 at 6:09 am
Where does RemoteSql login come from? Is this the login used by the linked server security or the login that runs the Agent job?
-- Gianluca Sartori
February 13, 2012 at 6:49 am
RemoteSql is sql login used by linked server security and it has full/admin access on remote server db.
PS: I had wrongly mentioned OSQL statement and error message above which is corrected now. plz check once
February 13, 2012 at 7:19 am
I don't understand what OSQL does here.
I would connect to SQL 2000 with query analyzer as "RemoteSql" and try to query the linked server.
Does this work?
-- Gianluca Sartori
February 13, 2012 at 7:27 am
OSQL -S abcServer -d abcDB -U RemoteSql -P password -Q "Exec SPName"
It should be local server login and internally mapped to Linked Server Logins.
OR
OSQL -S abcServer -d abcDB -U RemoteSql -P password -Q "Exec SPName"
It should be remote server name.
February 13, 2012 at 10:37 am
I don't understand what OSQL does here.
I would connect to SQL 2000 with query analyzer as "RemoteSql" and try to query the linked server
we want to execute mentioned proc daily and hence scheduled in job.
If we schedule proc directly in job step, it get fail on any error even though @@error check is used in proc for error handling(as try-catch is not available in sql 2000).
So to aviod this we have used OSQL command.
It should be local server login and internally mapped to Linked Server Logins.
Yes Dev, its local server login mapped to linked server login.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply