Sql-server – Four-part distributed Query issue from 2014 to 2008R2

distributed-transactionslinked-serversql server

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.

Best Answer

Ideally, you should be using the SQLNCLI11 and not SQLNCLI10. SQLNCLI11 since that connects back to SQL 2008R2/2008/2005 versions.

There is a known bug with SQLNCLI10 that is worth knowing (even though shows that there is a problem when connecting from SQL Server 2008R2 to 2000, but it shows that you are encountering it from SQL Server 2014 to 2008R2).

Also, since you are using linked servers - refer to : Which one is more efficient: select from linked server or insert into linked server?

Related Question