?
Solved

SQL - SQL Server Agent dealing with LinkedServer

Posted on 2012-09-19
13
Medium Priority
?
363 Views
Last Modified: 2012-09-20
I am create a job for the following SQL script

Select * from [LINKED_SERVER].[DB1].[dbo].[Table1]

It fail.  Can I use SQL Server Agent to run such a script.  I know I can execute the above statement correctly within SSMS.

Any help will be appreciated. I have no clue what to do.
0
Comment
Question by:tommym121
  • 7
  • 6
13 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 38413807
Can you post the error message you get when it runs?
0
 

Author Comment

by:tommym121
ID: 38413871
Here is the error message
Where I can get the History log?
JobError.JPG
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38413941
Check in job history. right click the job and click View History.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:tommym121
ID: 38414002
Below is the error

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
09/19/2012 10:44:26,Select LINKEDServer Test,Error,0,SQLTEST,Select LINKEDServer Test,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\usr1.  The last step to run was step 1 (Select from LinkedServer).,00:00:00,0,0,,,,0
09/19/2012 10:44:26,Select LINKEDServer Test,Error,1,SQLTEST,Select LINKEDServer Test,Select from LinkedServer,,Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'MyDomain\usr1'. [SQLSTATE 28000] (Error 18456).  The step failed.,00:00:00,14,18456,,,,0



Look like I have failed in login.  How do I resolve this error?
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38414039
Is it working when you execute the query is outside job?
0
 

Author Comment

by:tommym121
ID: 38414053
It is working when I do outside the job.  right within SSMS.  I login to SSMS using window authentication.
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38414098
Is that you mean your linked server is working properly? You can test your linked server by right click and select test connection.
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38414113
If linked server is working fine, go to job, edit step and add EXECUTE AS LOGIN = '<user that has permission to run linked server>'
0
 

Author Comment

by:tommym121
ID: 38414234
Same error but the linked server connection is good.  
This is my step in my job
EXECUTE AS LOGIN = 'MyDomain\usr1'
Select * from [LINKED_SERVER].[DB1].[dbo].[Table1]

History log

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
09/19/2012 11:15:30,Select LINKEDServer Test,Error,0,SQLTEST,Select LINKEDServer Test,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\usr1.  The last step to run was step 1 (Select from LinkedServer).,00:00:00,0,0,,,,0
09/19/2012 11:15:30,Select LINKEDServer Test,Error,1,SQLTEST,Select LINKEDServer Test,Select from LinkedServer,,Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'MyDomain\usr1'. [SQLSTATE 28000] (Error 18456).  The step failed.,00:00:00,14,18456,,,,0
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38414317
can you change the job owner to sa or any other admin account and try?
0
 

Author Comment

by:tommym121
ID: 38414351
I try no luck.  What kind of server role do I need?
0
 
LVL 15

Accepted Solution

by:
Anuj earned 2000 total points
ID: 38414373
Can you check this KB, similar problem explains here.
0
 

Author Closing Comment

by:tommym121
ID: 38418496
Thanks.  The problem is that the SQL server agent is not set as the permission as SSMS. Hence, SSMS can run the sql script without problem but not SQL Server agent
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question