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.

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

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?

Four-part distributed Query issue from 2014 to 2008R2

1 Answers1