Linked server returns different rows between sql server 2000 and sql server 2014

  • Differences in  linked server query results  after migration
    Note: Remote server is the same.

    Upgraded Version: SQL SERVER 2000 X64 windows Server 2012 release 2

    select count(*) from openquery(MO_ODS,'select * from legal_entity_table')
    (1 row(s) affected)

    Mistens 

    Former Version: Microsoft Windows 2000  server 

    select count(*) from openquery(MO_ODS,'select * from legal_entity_table')

    (7 row(s) affected)

    The only difference that I noticed during recreation of the linked server is that 

    In SQL Server Version 8(2000)  whenever the linked server was created 

    Under the   General TAB,  only the provider string was populated and the login 

    was made without using a security context.

    In Sqlserver 2014 

    The product name and Data source had to be provided and a connection by providing a username and password.

    This is holding back the migration..

    Thanks for the responses 

    MNT

  • Microsoft OLEDB Driver for ODBC is the Provider in both environments. 
    Thanks 
    Mistens

  • Mistens - Friday, March 17, 2017 8:27 AM

    Differences in  linked server query results  after migration
    Note: Remote server is the same.

    Upgraded Version: SQL SERVER 2000 X64 windows Server 2012 release 2

    select count(*) from openquery(MO_ODS,'select * from legal_entity_table')
    (1 row(s) affected)

    Mistens 

    Former Version: Microsoft Windows 2000  server 

    select count(*) from openquery(MO_ODS,'select * from legal_entity_table')

    (7 row(s) affected)

    The only difference that I noticed during recreation of the linked server is that 

    In SQL Server Version 8(2000)  whenever the linked server was created 

    Under the   General TAB,  only the provider string was populated and the login 

    was made without using a security context.

    In Sqlserver 2014 

    The product name and Data source had to be provided and a connection by providing a username and password.

    This is holding back the migration..

    Thanks for the responses 

    MNT

    Looking at the queries I would expect both to return a single row as both are just asking for a count of the number of rows in the result set.  If that is all that is needed, I would do the count on the remote server and return jus the count, a one row result set.

    Not sure what could be causing the difference.

  • Thanks for the response. Not sure either.

    Misten

  • Mistens - Friday, March 17, 2017 9:31 AM

    Thanks for the response. Not sure either.

    Misten

    I'd take a look at the versions of the drivers on each server.
    I'm curious though - what kind of result set do you get back on the count that has 7 rows?

    Sue

  • Get the expected results back when there are 7 rows returned .

  • Mistens - Friday, March 17, 2017 11:46 AM

    Get the expected results back when there are 7 rows returned .

    How does a count return 7 rows? That's why I was asking about what was returned.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply