Search code examples
sqlsql-server-2008sql-job

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.

Solution

  • 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