copy data from sql server 2005 instance to other sql server 2008 instance

Im using code like below:

EXEC sp_addlinkedserver 'MyServer';

GO

exec sp_addlinkedsrvlogin 'MyServer', 'true';

GO

but then when I try to select sth from that server and insert it into server from which im executin开发者_StackOverflowg query there is:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Does anyone know where the problem is ?

It fails because it's trying to impersonate the local login when it goes to the remote server, and the local login (NT AUTHORITY\ANONYMOUS LOGON) isn't valid on the remote server.

Instead of impersonating the local login, I would create a login on the remote server using SQL Server Authentication, that has the appropriate permissions for what you're trying to do, and then explicitly specify that username and password for the linked server.

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'MyServer',
    @useself = false,
    @rmtuser = 'YourRemoteUsername',
    @rmtpassword = 'YourRemotePassword'