Execute Link server SP via SQL Job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Execute Link server SP via SQL Job

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2015-04-21 : 05:39:10

Hello,

Good Morning.

I just want to ask, How can I execute an Link Server SP via SQL JOB.

My SP works like this



Create Procedure [TEST_Proc]
 @RowCount INT OUTPUT

As 

Begin

Set NCOUNT ON;

SET XACT_ABORT ON;

Begin Transaction

Insert Into Openquery(LINKSVR, 'Select Col1, Col2, Col3 from Link_Table')
select ColA, ColB, ColC from Local_Table


END
COMMIT TRANSACTION
END

When I manually execute the SP it is OK but when it is executed via SQL JOB it says that

"Execute as User: Networklocalsystem. LOgIN Failed for user Networklocalsystem [SQLSTATE 28000] (Error 18456). The step failed"

I have tried to change the SQL Server Log on into the Domain Admin but I still get the same error.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-22 : 01:31:00

Can you check if the target server has the security set up allowing the user you're using to connect. If not, you'll need to create the correct security.
Generally I use AD Accounts or Groups for security. I never assign a local and/or group/AD Account security to remote login.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Kristen
Test

22859 Posts

Posted - 2015-04-22 : 07:11:46

Just in case it helps:

When the linked server was created there was an option to either:

Require the user to be logged in to both local and remote (linked) servers
or
All queries on the Remote / Linked Server to run with a specific User ID and Password.

The first is probably what you have, so the local user has to have same credentials (and permission etc) on the remote server.

(I think I've got that correct, but I'm sure someone here will correct me if not!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources