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..Seems to me you should use select vchDescription collate Finnish_Swedish if you want the data in Finnish_Swedish collation.|||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.|||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..|||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)..|||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?sqlsql