From a SQL Server 2014 RTM in our DEV environement, I am executing this query using a SQL login.
select t.noshipid, y.nofolder
from distantserver.distantdatabase.dbo.sometable t (nolock)
inner join somedatabase.dbo.sometable y (nolock)
on cast(t.nobill as bigint) = y.nobill
where t.type_payment = 'CE'
group by t.noshipid, y.nofolder
distantserver is in 2008 R2.
I get back an error
Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
If I execute the same query on the production server which is 2008 R2, no errors.
1: The SQL login in question has the necessary access.
2: The provider being used to connect to the 2008 R2 server is SQLNCLI10
Questions:
1: Is it possible that upgrading the DEV server to 2014 is causing these issues?
2: Are there any know issues using a four-part identifier from 2014 to 2008 R2 ?
from distantserver.distantdatabase.dbo.sometable
instead of using openquery
FROM OPENQUERY([distantserver],
because using OPENQUERY, the query does not return errors.
3: Are there any sp_configure options or linked server options I need to configure because of 2014?
Update 14:41
If I add
BEGIN DISTRIBUTED TRANSACTION
at the top, the distributed part works fine. I just want to emphasize the fact that before upgrading, this was not necessary.