f



MS SQL Linked Servers(Linking MySQL to MS SQL) Collation problem.

Hello,
I have a problem. I've linked MySql server to MsSql, in MySql I have a
table with Latvian data(character set is ucs2, ucs2_general_ci) and
the problem is that when I use openquery to read data from MySQL
server, some characters are not translated correctly! I receive
question symbols instead of Latvian special characters.
Maybe someone had this kind of problem with collation?

0
6/21/2007 11:16:00 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

2 Replies
1123 Views

Similar Articles

[PageSpeed] 14

P?vels Mihailovs (iceravenlv@gmail.com) writes:
> I have a problem. I've linked MySql server to MsSql, in MySql I have a
> table with Latvian data(character set is ucs2, ucs2_general_ci) and
> the problem is that when I use openquery to read data from MySQL
> server, some characters are not translated correctly! I receive
> question symbols instead of Latvian special characters.
> Maybe someone had this kind of problem with collation?

Since I don't work with MySQL myself, it's difficult to say what is 
going on. But if MySQL has a notion of collation, and the collation is
ucs2_general_ci, it sounds like MySQL returns Unicode data. One would
expect that to arrive unscathed. What is you collation on the SQL
Server side?

The stored procedure sp_serveroption permits you to set options for
linked servers, and two of them relates to collations. You could play
around with these and see what happens. Please look in Books Online
for details.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
esquel (7068)
6/21/2007 10:16:11 PM
On Jun 22, 1:16 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> P?vels Mihailovs (icerave...@gmail.com) writes:
> > I have a problem. I've linked MySql server to MsSql, in MySql I have a
> > table with Latvian data(character set is ucs2, ucs2_general_ci) and
> > the problem is that when I use openquery to read data from MySQL
> > server, some characters are not translated correctly! I receive
> > question symbols instead of Latvian special characters.
> > Maybe someone had this kind of problem with collation?
>
> Since I don't work with MySQL myself, it's difficult to say what is
> going on. But if MySQL has a notion of collation, and the collation is
> ucs2_general_ci, it sounds like MySQL returns Unicode data. One would
> expect that to arrive unscathed. What is you collation on the SQL
> Server side?
>
> The stored procedure sp_serveroption permits you to set options for
> linked servers, and two of them relates to collations. You could play
> around with these and see what happens. Please look in Books Online
> for details.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

on the MS SQL server i'm using Latvian_CI_AI, but some databases are
set to Latvian_CI_AS collation (sorry db admin can't explain why they
are using it).
I've tried using different collation option, I've checked all 1257 and
other collation, but result was almost the same. :/

0
6/25/2007 6:48:31 AM
Reply: