2

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.

Improve this question
3
  • @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.
    – Craig Efrein
    Oct 16, 2015 at 13:37
  • @kin, yes it does, if you post the comment as an answer, I'll check it off.
    – Craig Efrein
    Oct 16, 2015 at 13:53
  • I have converted my comments to answer with some additional info.
    – Kin Shah
    Oct 16, 2015 at 14:18

1 Answer 1

Reset to default
2

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?

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.