What i have done is irrelevant, but i'll post it anyway.
-
Ensure
Distributed Transaction Coordinator
service is running on both machies: -
Disable all MSDTC security on both machines:
-
Turn on random options on the linked server:
-
Cursed and swore.
-
Smashed things.
-
Checked that a
SELECT
can use the linked server:SELECT * FROM CMSLive.CashierManagementSystem.dbo.Users .... (763 row(s) affected)
-
Checked that client server can
ping
the remote server:C:\Documents and Settings\avatar>ping wclcmstest.windsor.wcl Pinging wclcmstest.windsor.wcl [10.180.12.40] with 32 bytes of data: Reply from 10.180.12.40: bytes=32 time<1ms TTL=128 Reply from 10.180.12.40: bytes=32 time<1ms TTL=128 Reply from 10.180.12.40: bytes=32 time<1ms TTL=128 Reply from 10.180.12.40: bytes=32 time<1ms TTL=128 Ping statistics for 10.180.12.40: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms
-
Checked that the remote server can commnicate back, by name, to the initiating server:
C:\Documents and Settings\avatar>ping wcltestserver.windsor.wcl Pinging wcltestserver.windsor.wcl [10.180.12.22] with 32 bytes of data: Reply from 10.180.12.22: bytes=32 time<1ms TTL=128 Reply from 10.180.12.22: bytes=32 time<1ms TTL=128 Reply from 10.180.12.22: bytes=32 time<1ms TTL=128 Reply from 10.180.12.22: bytes=32 time<1ms TTL=128 Ping statistics for 10.180.12.22: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms
-
Checked that
@@SERVERNAME
matches the server name on both servers:SELECT @@SERVERNAME, SERVERPROPERTY('MachineName') ------------- ------------- WCLTESTSERVER WCLTESTSERVER
and
SELECT @@SERVERNAME, SERVERPROPERTY('MachineName') ---------- ---------- WCLCMSTEST WCLCMSTEST
-
Screamed
-
Issued
SET XACT_ABORT ON
before issuing my query:SET XACT_ABORT ON GO BEGIN DISTRIBUTED TRANSACTION SELECT TOP 1 * FROM Sessions
-
Granted
Everyone
Full Control
to:HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
on both servers.