CODEWITHSUNDEEP
sqlsql-server-2008sql-job
oshirowanen
oshirowanen

Reputation: 15925

Can't run SQL query via SQL job

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.

Upvotes: 0

Views: 407

Answers (3)

Subholina Deb
Subholina Deb

Reputation: 1

  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.

Upvotes: 0

jean
jean

Reputation: 4350

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

Upvotes: 1

onurpro
onurpro

Reputation: 13

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.

Upvotes: 0

Related Questions