Post reply

Linked Server----Use Remore Collation Issue

  • September 23, 2008 at 12:29 pm

    #127550

    The following query when run from server PANKS (SQL 2000) displays all Issuers that do not follow a standard naming in the Antares database.

    select * from dbo.tbname where UPPER(Issuer) <> Issuer;

    The same query when run from Server PANKS2005 (SQL 2005) does not return any results as we are using a linked server LINK

    select * from LINK.dbname.dbo.tbname where UPPER(Issuer) <> Issuer;

    Enabling the option to Use Remote Collation for the LINK linked server produces the expected results.

    I would like to know the reason for this. Moreover i would also like to know if there would be any Performance Impacts by Enabling the option to Use Remote Collation to TRUE.

    Any help would be appreciated.

    Thanks

  • September 24, 2008 at 10:14 am

    #876505

    I don't think it makes any performance impact.

    Its just used becasue the remote server collation isdifferent from the source one:

    Use Remote Collation:

    Determines whether the collation of a remote column or of a local server will be used.

    If true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for non-SQL Server data sources.

    If false, distributed queries will always use the default collation of the local server, while collation name and the collation of remote columns are ignored. The default is false. (The false value is compatible with the collation semantics used in SQL Server 7.0.)

    MJ

  • September 24, 2008 at 11:40 am

    #876562

    You can also specify the collation to use in the query itself:

    select * from LINK.dbname.dbo.tbname where UPPER(Issuer) <> Issuer collate SQL_Latin1_General_CP1_CI_AS

Viewing 3 posts - 1 through 3 (of 3 total)

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