After changing IP-addresses on a subnet, I can no longer run transactions between my two SQL servers. The first server (pluto) runs Win2008 with MS SQL 2008. The remote server (mars) runs WinXPpro with MS SQL 2000.
Mars runs a stored procedure on a regular basis. This script starts with BEGIN TRANSACTION, and runs some SELECT's before an UPDATE. This update fails with the following error message:
OLEDB provider "SQLNCLI10" for lined server "MARS" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 6
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "MARS" was unable to begin a distributed transaction.
The script works perfectly if I remove "BEGIN TRANSACTION" (and commit/rollback), and some other simple testing indicate that the problem indeed is the transaction part.
Since I think the problem started after changing IP-addresses, I've been looking for connectivity problems, but everything seems OK. Maybe someone have some good tip where we might have specified a IP-address? :)
* Both computers can ping each other with both IP and FQDN (reported by SELECT @@SERVERNAME)
* DTC service is up and running
* No firewall between computers
* DTCPing (http://support.microsoft.com/kb/918331
) tested positive from both servers
* Restarted both machines as well :)
* tried "SET REMOTE_PROC_TRANSACTIONS OFF" and "SET XACT_ABORT ON" before "BEGIN TRANSACTION"
Any good ideas?