Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Sweden
    Posts
    4

    Red face Unanswered: Collation and Linked servers

    Hey!
    I got my self a little problem with collations and linked servers..

    I have access to a View on a remote server and have set up a Linked server on my SQL-server..

    I get all the data I want, the only trouble I have is getting the collations to work.

    My default database collation is "Finnish_Swedish" and the remote server uses the "SQL_SwedishStd_Pref_CP1_CI_AS" collation..

    When I get my data, all swedish caracters are scrambled to crap and it feels like I have tried everything possible.

    I've tried using "select vchDescription collate SQL_SwedishStd_Pref_CP1_CI_AS", I've set the collation to "SQL_SwedishStd_Pref_CP1_CI_AS" in the table I transfer the data to on my server, I've tried "Use Remote Collation" when setting up the linked servers, man I've tried everythin but nothing seems to work.

    What am I to do? Please help me..

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Seems to me you should use select vchDescription collate Finnish_Swedish if you want the data in Finnish_Swedish collation.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Did you try to create a new test db with the same collation as the remote server and do your SELECT from there? I think there is a hidden issue that results in scrambled data.

  4. #4
    Join Date
    Aug 2003
    Location
    Sweden
    Posts
    4
    Originally posted by Paul Young
    Seems to me you should use select vchDescription collate Finnish_Swedish if you want the data in Finnish_Swedish collation.
    Yupp, I tried that to, but still no luck..

  5. #5
    Join Date
    Aug 2003
    Location
    Sweden
    Posts
    4
    Originally posted by rdjabarov
    Did you try to create a new test db with the same collation as the remote server and do your SELECT from there? I think there is a hidden issue that results in scrambled data.
    What I do is, compare the data I have on my SQL-server, with the data in the remote view. The data that has changed (ins/del/upd) gets inserted into a "temporary" table (not a #-table), thereafter I download pictures and other objects (since they need processing to fit into my system) and add them to the same table. When that's finished I move the data to the live table. The remote source has e.g. the pictures on disk (I want them in my database) with a very different format (both file type and size) than I use..

    Both my temporary- and live-table uses the same collation as the remote view, but still the text gets scrambled on transfer (as soon as it is inserted into the temp-table)..

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What kind of data do you see if you just do a SELECT from that view, without inserting into a temp table? Is it scrambled at that time too?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •