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

Clustering and Linked Server

Hi,
We have a SQL 2000 cluster server. We need to run a query
on an access database. We have set up a linked server,
however we always get the error;
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error.
[OLE/DB provider returned message: Cannot open
database ''. It may not be a database that your
application recognizes, or the file may be corrupt.]
This setup always worked before we changed to clustered
servers. Does anyone know a way around this?
Thanks.
Does the SQL service account have access to the underlying file? Is the
file accessable from a UNC share that appears the same to all host nodes?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"PC" <anonymous@.discussions.microsoft.com> wrote in message
news:1febf01c4590e$36289520$a601280a@.phx.gbl...
> Hi,
> We have a SQL 2000 cluster server. We need to run a query
> on an access database. We have set up a linked server,
> however we always get the error;
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error.
> [OLE/DB provider returned message: Cannot open
> database ''. It may not be a database that your
> application recognizes, or the file may be corrupt.]
>
> This setup always worked before we changed to clustered
> servers. Does anyone know a way around this?
> Thanks.
|||I have checked the permissions on the c:\winnt and
c:\winnt\temp folders and the SQL Server service account
has Full Control over these.

>--Original Message--
>Does the SQL service account have access to the
underlying file? Is the
>file accessable from a UNC share that appears the same to
all host nodes?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"PC" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1febf01c4590e$36289520$a601280a@.phx.gbl...
query
>
>.
>
|||Storing user files in the winnt folder or subfolders is a bad idea all by
itself.
When you have a clustered instance, you need to make sure that the file is
accessable to the SQL instance no matter which node is currently hosting it.
If the file is on Host1 and the SQL instance is running on Host2, the SQL
instance will see only the disks on Host2.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"PC" <anonymous@.discussions.microsoft.com> wrote in message
news:206cd01c45936$9f74e240$a101280a@.phx.gbl...[vbcol=seagreen]
> I have checked the permissions on the c:\winnt and
> c:\winnt\temp folders and the SQL Server service account
> has Full Control over these.
> underlying file? Is the
> all host nodes?
> message
> query