SQL Server 2008 R2 JOB Step Linked Server Login Failed

Question

I need to transfer a table daily. Table name is changing every day, so i should use script to get the tableName.

There is a job step which transfers data from sourceTable to destinationTable created by using T-SQL query. sourceTable and destinationTable are in different servers. Linked Server works fine.

If i execute the same query in sql serer management studio, it works fine. But when job executed at this step i see this message:

Executed as user: NT AUTHORITY\SYSTEM. Login Failed for user "NT AUTHORITY\ANONYMOUS LOGON". The step failed.

Both sql servers are 2008 R2 version and linked to each other.

You may advise some other methods instead using T-SQL.

Any help would be appreciated.

Answer 1

I couldn't solve this problem with linked servers.

Instead of linked servers i used these steps:

1. rename x to x_temp
2. use SSIS package (which is much faster than linked server query) to move all data from x_temp to other server.
3. rename x_temp to x_dd_mm_yyyy