Post reply

Linked Server Query with Collate Clause Problem

  • June 26, 2007 at 8:02 am

    #174702

    Hi,

    Firstly the title may be a red herring!

    I'm trying to run the following query:

    select * from #accounts

    where id not in

    (select account collate SQL_Latin1_General_CP1_CI_AI

    from dfin001.psreporting.dbo.PS_ACCOUNTS)

    Note that I'm having to use the collate clause in the subquery, which is in itself querying a linked server; if run the subquery on its own it runs fine, so the collate clause will work with the linked server. However if I run the above statement without the collate clause I get a cannot resolve collation error.

    Anyway when I run the above query in its entirety I get the error message:

    OLE DB provider "SQLNCLI" for linked server "dfin001" returned message "Cannot create new connection because in manual or distributed transaction mode.".

    Msg 7320, Level 16, State 2, Line 1

    Cannot execute the query "SELECT TOP 1 1 FROM "psreporting"."dbo"."PS_ACCOUNTS" "Tbl1006"" against OLE DB provider "SQLNCLI" for linked server "dfin001".

    I sure I could work around this but would like to understand what's going on here, can anyone help me out?

    Thanks in advance,

    Iain

    ps. The server I'm running the query from is SQL 2005 Enterprise, the linked server is 2000 Enterprise (if this makes any difference).

  • July 3, 2007 at 4:59 am

    #716825

    i have the excact same problem, please let me know when you know why this is happening

    Regards

    Gert

  • July 3, 2007 at 7:24 am

    #716868

    Someone far more clever than me will explain why you're having problems using a collation directive (?) in a remote query, in the meantime here's a workaround - change the collation sequence of the column "your side", and change it back when you're done...

    -- change it to match the column in the remote table...

    ALTER TABLE StagingHospitalVendors ALTER COLUMN [HospitalCode] CHAR(4) COLLATE Icelandic_CS_AS

    -- run your query, then change it back when you're done...

    ALTER TABLE StagingHospitalVendors ALTER COLUMN [HospitalCode] CHAR(4) COLLATE Latin1_General_BIN

    HTH

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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