469,926 Members | 2,229 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,926 developers. It's quick & easy.

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?

Jun 21 '07 #1
2 6817
P?vels Mihailovs (ic********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 21 '07 #2
On Jun 22, 1:16 am, Erland Sommarskog <esq...@sommarskog.sewrote:
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. :/

Jun 25 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Eric J Owens | last post: by
2 posts views Thread by Jeff Pritchard | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.