Four-part distributed Query issue from 2014 to 2008R2

Question

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.

• user4659: @Kin, you are right, but what I didn't realize was that it was necessary to use SQLNCLI11 even when talking to previous server versions.
• user4659: @kin, yes it does, if you post the comment as an answer, I'll check it off.
• Kin Shah: I have converted my comments to answer with some additional info.

Answer 1

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?