Can't run SQL query via SQL job

Question

I have a query which looks something like this:

select tb1.col1, tb1.col2, tb2.col4, tb2.col7
from server_1.database_a.dbo.table_1 tbl1
inner join server_2.database_c.dbo.table_2 tbl2 on tbl1.col_id = tbl2.col_id

This query runs fine (I had to create an link on the sql server to link the 2 servers for the query to work) when I manually execute it from within Microsoft SQL Server Management Studio. But when I try to create an sql job which executes this query once every day, I get the following error message and the query does not execute

Executed as user: NT AUTHORITY\SYSTEM. The object name 'server_1.database_a.dbo.table_1' contains more than the maximum number of prefixes. The maximum is 2. [SQLSTATE 42000] (Error 117). The step failed.

Answer 1

Try it:

use database_a;
select tb1.col1, tb1.col2, tb2.col4, tb2.col7
from dbo.table_1 tbl1
inner join server_2.database_c.dbo.table_2 tbl2 on tbl1.col_id = tbl2.col_id

Answer 2

server_1.database_1.dbo.table_1 this is not the same with you write first. Its: server_1.database_a.dbo.table_1 may be the reason is this.

Answer 3

1. Put NT AUTHORITY\SYSTEM user in sysadmin server role.
   

2. Add this user into linked server login mappings with remote user name and pwd in security tab of linked server properties.