473,327 Members | 2,055 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 7050
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Max | last post by:
Hi, I have SqlServer 2000 as back end and Access 2000 as front-end. All tables from Sqlserver are linked to Access 2000. I am having write conflict problem with one of my form which is bound to...
0
by: Max | last post by:
Hi, I am having problem with missing default values in Linked Sql Server 2000 table. When I open table defination in Access it doesn't show the default value. And they don't show in bound form...
0
by: Eric J Owens | last post by:
I have an A2k mdb that uses a linked SQL 2k view. In SQL Server, the underlying table uses datatype 'nvarchar'. When I try to view the data in Access (SQL server works fine for the view), I get:...
2
by: Jeff Pritchard | last post by:
Some time ago I am sure I came across something that said this was possible, though it doesn't seem to work. A client wants to replace an Access back-end with SQL Server tables. We have tried...
4
by: Rich | last post by:
I have a access form that is connected to linked sql table via odbc. I have some fields that I dont want to allow nulls when data is entered via the form. I can set the null checking on the SQL...
6
by: BrianDP | last post by:
I have a table that has always been in my back-end of this application. The table is getting quite large, and, on top of that, we lose records out of this table. They just dissappear! No rhyme or...
1
by: itisjitin | last post by:
Hi All, 1. Created table in SQL Server 2K with Primary key as int Identity 2. Link to table in MS Access 2K with child table 3. Using form in MSAccess to update the both master and child linked...
8
by: brucedodds | last post by:
I've inherited an A2003 application with linked SQL Server 2000 tables in the back end, using the Microsoft SQL Server ODBC driver. We've set up a test SQL Server database. I'd like to automate...
5
by: cobolguy | last post by:
I have a simple report that I need to share with some other users. The report is generated in Access from a Linked SQL Server Database. I would like some simple ideas on the best way to get this...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.