Tip for solving strange MSSQL distributed transaction problem

This is a solved problem. Posting here only for future reference (and perheps save time from poor souls experiencing the same problem later).

Description:

In certain disaster recovery procedure involving 2 database instances, we found that certain functions that uses distributed transaction to update data across the DB instance fails. By finding the log for jobs done, we found that the job has been a re-run, and the same exact task has run success before.

So we try rerun the task, and found that no matter how many time it runs, it fails with the following error:

Could not find server '<remote_server_name>\<instance_name>' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
OLE DB provider "SQLNCLI" for linked server "<remote_server_name>\<instance_name>" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

So we run "select * from sys.servers", and found <remote_server_name>\<instance_name> just sit there ok.

And strangely, in "SQL management studio", no matter how many times we run the commands with "begin distributed transaction" and "commit transaction", all SQL statements run successfully.

We restore the same tape to another DB instance pairs, and found the "first time success, second or later times fails" pattern readily reproducetable.

Solution:

It turns out that it's not the "<remote_server_name>\<instance_name>" entry have error, it's the "<local_server_name>\<instance_name>" entry remain to be "the pair for production database". Use 'sp_dropserver' and 'sp_addserver' with local fixes it.

Suggestion (Rant?):

The error message returned in SQL 2005 is misleading. If anyone know someone in SQL server team, please help me ask them to fix it. (Perheps they should add a check to see whether local instance name match that in DB server metadata used for sys.servers before returning the error is a good idea)