February 16, 2016 at 8:03 am
We just upgraded our dev SQL Servers from 2008R2 to 2012 SP3 and our linked servers stopped working. The two servers in question were both updated and rebooted several times throughout the process. I've validated DTC is up and running on both servers. I've regenerated Master Keys on both servers. I've dropped and recreated the Linked Server both before and after regenerating the keys. R-click, Test Connection on the linked server "succeeded" but when I run a simple SELECT..FROM I get "Msg 8522, Level 16, State 3, Line 3 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction." Any ideas?
Thanks,
Nate
_____________________________________________________________________
- Nate
February 16, 2016 at 9:11 am
Permissions still active on the sqlJDBCXAUser role?
February 16, 2016 at 9:32 am
daniel.hughes 66721 (2/16/2016)
Permissions still active on the sqlJDBCXAUser role?
That role didn't/doesn't exist since XA transactions aren't enabled on either server. Is this something that needs to enabled in 2012 which did not in 2008R2?
_____________________________________________________________________
- Nate
February 16, 2016 at 9:54 am
OK, it works if I wrap it in a distributed transaction. Is there something new to 2012 I'm missing that would let me configure this?
This works:
BEGIN DISTRIBUTED TRAN;
SELECT SomeColumn
FROM SomeServer.SomeDB.SomeSchema.SomeTable
WHERE SomeOtherColumn = 1;
ROLLBACK TRAN;
This doesn't:
SELECT SomeColumn
FROM SomeServer.SomeDB.SomeSchema.SomeTable
WHERE SomeOtherColumn = 1;
_____________________________________________________________________
- Nate
February 16, 2016 at 2:00 pm
OK, threw enough crap at the wall I finally got something to stick: drop and recreate linked server using SQLNCLI11 driver instead of SQLNCLI10.
However, would still like to know why 'BEGIN DISTRIBUTED TRANSACTION' worked and 'SELECT..FROM' did not.
_____________________________________________________________________
- Nate
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply