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
1025 Views

Similar Articles

[PageSpeed] 33

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:

Similar Artilces:

MS SQL Server 2000 and MS JDBC SQL Server driver problems. (Connection timed out, followed by Error establishing socket)
I have downloaded the MS SQL Server 2000 Enterprise Evaluation edition, and the MS SQL 2000 Server JDBC driver. I have installed and configured the MS SQL Server 2000 onto two computers, which reside on the same network. (In fact, these two computers comprise the entire network). The network server (computer name=SUNCDS1), and the lone node (SUNCDS2), each have their own SQL Server 2000 instance. The SQL Server 2000 Enterprise Manager console on SUNCDS1 shows both SQL Server instances, the (local) instance, which is SUNCDS1, and also SUNCDS2. It may be worthwhile to mention that the SUNCDS2 instance on the Enterprise console does not show the SQL Server Agent as being started, but I can navigate to the SUNCDS2 databases, and display the table information. If I try to start the SQL Server Agent for SUNCDS2, I receive the message "Access is Denied". The enterprise console on the SUNCDS2 machine indeed shows the SQL Server Agent as being started. I have written a sample Java application that uses both the MS SQL Server ODBC driver and a JDBC type 4 driver (MS SQL Server 2000 JDBC) to establish a connection to the SUNCDS2 instance. This application resides on the SUNCDS1 network server. The ODBC driver will connect to SUNCDS2 instance wihout a problem. The JDBC driver does not connect to SUNCDS2. The JDBC driver will, however, connect to the local SQL Server instance that resides on SUNCDS1. Following are the code snippet to est...

SQL Server "Linked Server" to MS Access Database
I have a very frustrating problem. I have a situation that works fine on some computers, but not on others. I have SQL Server Express 2005 and Access 2003 loaded on 4 computers. I have set up a "Linked Server" in SQL Server linked to my Access database. I am running queries on t-SQL against the Access tables through the linked server. On all of the computers, I can do select queries and they all work great but what I need to do is run Update, Insert, Delete queries as well. On a couple of the computers, it works fine, locally and across the LAN, and a VPN as far as that goes. But on other computers I can do Select queries (also across the network) but no update queries. I get the error message "OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "securedjetls" returned message "Operation must use an updateable query." where securedjetls is the name of the linked server. The Access database on the computer that is not working is a copy of the database from the computer that is working and any of the tables that I'm trying to update have primary keys, so that's not the problem. ...

Using MS SQL Server 2005 Enterprise Manager with MS SQL Server 2000
Hi Everyone, Most of our MS SQL Servers are still running on SQL 2000, but being I will soon be upgrading my workstation to Vista Business I'd like to install MS SQL 2005 Enterprise Manager to manage them all. When I tried to install MS SQL 2000 Enterprise Manager on Vista, it gave me a message saying it's not compatible, so I'll have to use 2005 I guess. We have LOTS of DTS scripts on our SQL 2000 server, and will 2005 EM work with these okay? Also I want to make sure SQL 2005 Enterprise Manager won't be adding to, changing, or 'upgrading' the DTS scripts or anythi...

Database link to a MS SQL Server 2000
Hi NG, we have to replikate some data between an oracle DB and an application with MS SQL Server. There is no possibility to load and push the data on the client mashine. We have to replicate the data on the server mashines directly. Is there a possibility to create a DB link from oracle to MS SQL Server or otherwise? thanx abbas On Thu, 9 Jun 2005 00:13:02 +0200, "Abbas" <ar.newsgroup@web.de> wrote: >Hi NG, > >we have to replikate some data between an oracle DB and an application with MS SQL Server. > >There is no possibility to load and push the data on the client mashine. We have to replicate the data on the server mashines >directly. > >Is there a possibility to create a DB link from oracle to MS SQL Server or otherwise? > >thanx >abbas > Look into using Heterogeneous Services in your Net administrators manual. -- Sybrand Bakker, Senior Oracle DBA Are you willing to install a bridge program to create this "link"? If so, then try www.examinstudio.com Do as Sybrand told you, look at the Heterogenous services of Oracle. Oracle even supports natively DRDA towards a DB2 z/OS mainframe, and they also support SQL Server. They all end-up like a database link with a totally quallified names in the "from" clause. Be aware of that distributed joins between different DBMS systems might cause huge performance problems, keep the functionality when dealing with multiple DBMS systems simple, no dist...

MS SQL Express Advanced vs MS SQL full version vs MySQL
I'm planning to transport a desktop application to the web. A spin-off of this application has already been put on the web by another programmer. He used ColdFusion with MS SQL, Access, VC, and Java. It is faster than the desktop application (written in VFP). 1. Can I get the same results using MS SQL Express Advanced and Access for the internet version if used with .net? 2. Are the select statements limited in power, accuracy, size, etc. for MS SQL Express Advanced when compared to SQL? 3. If Access will perform poorly, can VFP be used instead with .net? I already know it can't be used with ColdFusion. 4. If MS Express Advanced will not sufficiently replace SQL, will MySQL do the job better? Thanks On Jul 22, 5:31 pm, <wipeou...@hotmail.com> wrote: > I'm planning to transport a desktop application to the web. A spin-off of > this application has already been put on the web by another programmer. He > used ColdFusion with MS SQL, Access, VC, and Java. It is faster than the > desktop application (written in VFP). > 1. Can I get the same results using MS SQL Express Advanced and Access for > the internet version if used with .net? > 2. Are the select statements limited in power, accuracy, size, etc. for MS > SQL Express Advanced when compared to SQL? > 3. If Access will perform poorly, can VFP be used instead with .net? I > already know it can't be used with ColdFusion. 4. If MS Express Advanced > will not suffic...

Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible? If I migrate the MS Access to SQL Server, would the queries, back-end VBA, macro, tables and forms be affected? Do I need to change the MS Access data type to SQL server supported data type? Which tool I can use to do the migration? Upsizing wizard or exporting...

Permissions Issue when linking to MS SQL 2000 DB from MS Access XP
Hello, We have a database in MS SQL 2000 and I'm trying to link to it from MS Access XP. I've created a user with full access to one specific table in the MS SQL database, but Access won't write to it. I can open this table in MS Query Analyzer and log-in as the user, and I can do update, delete, and select queries just fine... but when I link to the table from MS Access using the same MS SQL username/password, I can only read the data... no write or delete. Since I can update data fine in MS Query Analyzer, I assume permissions are setup properly. Anyone know why I can not ed...

Running MS Access db as a Linked Server on SQL Server
Hi, Looking for a quick and dirty on running an Access database as a linked server in SQL Server. Basically, the majority of my stuff is in SQL Server, but there is one lingering nightmare-of-an-Access-database no one wants to touch. I just want to create a linked server to use the Access db. So far I have not found a way to connect (under Security -> Link Servers in SQL Server), though I tried all kinds of drivers, connection strings, etc. What do I put for Product Name, Data Source, Provider String, (Location, Catalog)? Thanks a bunch. This might help; note the table about halfway...

insert fails
Hi, I configured a linked server in MS SQL Server v8 pointing to a remote AS/400 DB2 database. I used Client Access ODBC driver (v7), installed it from a Client Access Express v4.5. Select/update/delete seems to be working fine, I just cannot execute an insert statement. Here's the error I'm getting: update as400..U1.TB1 set a = 4 select * from as400..U1.TB1 insert into as400..U1.TB1 ( a ) values (1) ------------------------------------------------------ Server: Msg 7399, Level 16, State 1, Line 4 OLE DB provider 'MSDASQL' reported an error. The provider did not give any inf...

MS SQL Server 2005
I am currently running SQL Server 2005 on a stand alone (not a domain member) Windows Server 2003, this server was installed during setup using local user accounts as service accounts. As far as I recall at the time everything, including the SQL agent functioned normally. At this time, the server itself still seems to be running fine, however the SQL Agent service will not start. I can not be sure as up until recently we haven't really been using the SQL Agent, however I believe the problem would have started around the time that service pack 1 was installed. The following is the event log error message that is generated everytime the SQL Agent service attempts to start: ### Event Type: Error Event Source: SQLSERVERAGENT Event Category: Service Control Event ID: 103 Date: 7/24/2007 Time: 3:55:22 PM User: N/A Computer: DBSERVER Description: SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role). For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. ### Please note that where the error message should indicate the name of the service account (svc_sqlagent) it instead indicates "Unknown". Also that SQL Server database engine running under the its own local user service account (svc_sqlserver) works fine. I have since tried recreating and reassigning a new local user service account for the SQL Agen...

Re: Accessing legacy data via MS SQL Server linked servers
Chuck, You might talk to MBFoster. They may have a solution for you. Imagesql has odbclink/se but I'm not sure if this will do the job for you. Imagesql is available to 7.0 and 7.5 users. I have found in some cases that MSAccess does a quick job of access legacy data with odbclink/se...(but that is going thur Imagesql). JDBC, java, perl may be another solution. These have classes for image. Hope this helps Cathlene Mc Rae HP Sr Response Center Engineer. * To join/leave the list, search archives, change list settings, * * etc., please visit http://raven.utc.edu/arc...

Unidata linked server with MS SQL 2000
Hi I am trying to connect to UniData using linked server from MS SQL. I tried using the following details, but could not get to view the tables. It comes with an error that "UVI connections to non Universe database not allowed. Details entered for creating a linked server: product name: Unidata data source: c:\ibm\ud60\demo provider string: Ardent.UniOLEDB location: myserver (my unidata server name) Can somebody help me out please, Thanks, ...

Linking MPE Cobol to MS Sql Server
We are a split shop... half COBOL/Image and half Microsoft (.NET, SQL, etc.) We have an MPE=20 application that needs real-time communication with an MS SQL Server database. We need for=20 the program to be able to insert a record into the SQL database and to be able to retrieve an updated copy of the record at a later point in time. The insertion of the record fires a trigger to instigate a MS web-services call that will update the record. We have both the web-services call and the COBOL=20 application already in hand, the only missing piece would be the communications between ...

How to connect MS Access form to MS SQL Server
Hello all I created an application which use MS Access 2000 as user interface and MS SQL Server 2000 as backend. If I want to get data from MS SQL Sever 2000 and display them on the MS Access 2000 form, where do I set the database connection to the form (i.e. tell the form to get data from MS SQL server database)? Thanks Bon In Windows control panel, admin tools, data sources (ODBC), create a system DSN of type SQL server and fill in the parameters for your SQL database. In Access, select file / get external data, files of type ODBC. Select machine data source, select the DSN you created and the table(s) you need. ...

MS Access linked to SQL Create View Problem
I have an MS Access ADP project linked to a SQL Server database. When a particular user tries to create a view she gets an error saying 'ADO error 'create view' does not allow specifying the database name as a prefix to the object name' Username = Jenny.Taggart I ran a profiler trace to see what the error was. The SQL that is called CREATE VIEW Jenny.Taggart.JENNYTEST AS SELECT * From dbo.Accident Correct syntax would be CREATE VIEW [Jenny.Taggart].JENNYTEST AS SELECT * From dbo.Accident Is there anyway to force access to put the square brackets around the username? Thanks Dermot French ...

#Deleted In All Fields When Linking SQL Server Table In MS Access
I have a set of tables in SQL Server, which, when I link in Access, I get all the fields displayed as #Deleted when I look in table view or the output of a query. However, when I walk the recordset in code, it is just fine, and it displays correctly in Enterprise manager and Query analyzer. Looking back in the archives there is some indication of a problem with respect to autonumber verses IDENTITY fields, however, even if I do SELECT * FROM Table it shows the same output, or if I make it purely read only, such as SELECT Name & ProductID as Info FROM Products, I still get the same. The code that walks the table correctly is: Dim db As Database Dim rs As Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("dbo_Products") Do Until rs.EOF Debug.Print rs!ProductId, rs!Name rs.MoveNext Loop rs.Close I'd appreciate any suggestions or recommendations to fix this problem. Thanks in advance. ...

Php and DataBase Abstraction for mySql, Sql Server, and MS Access?
Is there a good DataBase Abstraction layer that will handle the mySql, Sql Server, and MS Access engines? I do not wish to write the same functions with the three different PHP db functions. I see that php_dba is listed as an extension, but I determine which DBs are supported. However, I think that it does not support the ones I need. Thanks David wrote: > Is there a good DataBase Abstraction layer that will handle the > mySql, Sql Server, and MS Access engines? I do not wish to write the > same functions with the three different PHP db functions. > > I see that php_...

Problem Using 2 tables from Diferent DataBases (Ms-SQL Server)
Hy People i have this problem. I'm working with c55h and MS-SQL 7 and i have 2 databases Like These Database1 -> Tables: Customeres,Products Database2 -> Table: Inovoices When i do a Browse Over the Invoice Table, everything works Great the problem begins when i put fields from a table in other Database In this Case, The Customer's Name. (Database1) The browse behavior is extrange. it shows duplicated Records and extrange things. i resolved that problem using a VIEW of the customer table instead of the table. (the view is saved in the DataBase2) i gess if i would do that...

linked server from sql 7 to ms access 2000 ... some tables not available
Hi, I have a linked server in sql 7 that connects to a ms access 2000 database. It has been working for a while, however lately, after we imported a large amount of new data, the database fails to query a certain (large number of records) table. I can query and get responses for every other table except the largest table. select top 5 * from linkeddatabase...table I get this error message. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()). [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation. Connection B...

MS SQL Server databases
Hi, I am looking for a tool to create MS SQL Server databases. Who can help me? Valentine Download BOL, it's free and it will show you how to create SQL databases. If you don't have SQL you can buy the developer edition for $50 http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp HTH Ray Higdon MCSE, MCDBA, CCNA *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! ...

Setting up a linked server in MS SQL using IBM OLE DB PRovider for DB2
I am using DB2 UDB Application development client v8.2.5. I am trying to setup a linked server in MS SQL Enterprise Manager using the IBM OLE DB Provider for DB2 to connect to databases on DB2 for z/OS v7 and v8. The connection string I used is Provider=IBMDADB2;Data Source=xxxx and location C:\Program Files\IBM\SQLLIB\BIN. After creating the connection, when I click on the tables/views under that linked server, I get the following error.. Error 7303: Could not initialize data source object of OLE DB Provider 'IBMDADB2' OLE DB error trace[OLE/DB provider 'IBMDADB2' IDBInitialize::Initialize returned 0x8004d01c] Please help. ...

what is equivalent of format(date) function of MS Access in MS Sql Server 2000
Hi All, I am facing a problem with a sql what i used in MS Access but its not returning the same result in MS Sql Server 2000. Here i am giving the sql: SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHERE MY_ID=1 The above sql in ACCESS return me the date in below format in one column: Friday 09, 2003 But in Sql server 2000 i am not getting the same format eventhough i am using convert function, date part function etc. Please if you find the solution would be helpful for me.. Thanks Hoque Hi If you field is a datetime, you can use the CONVERT function to conver...

ODBC connection
Hello all! I have such question to all of you. I have some tables linked from MS SQL Server 2000. Is time of processing query based on these linked tables from MS SQL Server 2000, faster or slower than the time of processing the same query based on tables, which are not linked but imported to MS Access? Can you answer this question? Thank you in advance for posts Marcin from Poland. On Tue, 19 Apr 2005 17:23:48 +0200, "Kamyk" <marcinzmyslowski@poczta.onet.pl> wrote: >Hello all! > >I have such question to all of you. >I have some tables linked from MS SQL Server 2000. Is time of processing >query >based on these linked tables from MS SQL Server 2000, faster or slower than >the time of processing the same query based on tables, which are not linked >but >imported to MS Access? > >Can you answer this question? > >Thank you in advance for posts >Marcin from Poland. > That depends on a lot of things... The most obvious thing it depends on is the size of the result set. In a client/server arrangement, you want to keep result sizes down, and do aggregation and filtering on the server. Next, how many people will be sharing the back-end data. Access slows down as soon as 2 people are sharing a JET back-end, but the server won't have this limitation. On the other hand, if the server is too slow, or you're trying to use MSDE with more users than it's optimized for, the server will be slow. There are m...

MS Access conversion to MS SQL
Hi, I've been developing a database for a department in a local organisation. The database is in Microsoft Access and uses tables, queries, forms and reports. Written into the forms is vast amounts of visual basic. There are also a couple of macros. In the time I've been developing this database (a year now, as I went back to university, and then finished university and returned to finish off the DB) the organisation I've been working for have decided to start using MS SQL. The department I am working for aren't to happy as not only my database but most of theirs are in Access and no one seems to have actually used SQL or know anything much about it. Basically, I need to learn about how to convert from Access to SQL. I understand we use the upsizing wizard. I've managed to track down some of the rules I have to use in the conversion (like no spaces in names, no apostrophes etc), but I'd like to know if anyone could tell me more about the upsizing procedure. I'm also concerned about the vast amounts of forms I've made (It's a 109mb database with no data - all down to the forms!). What happens to these? Does SQL use forms, or will all of these be lost? And what about queries? Any information from someone who has knowledge of these things would be greatly appreciated! Many thanks, Stu. Hiya, An account of how I did it can be found at: http://www.cooldigitec.co.uk/NJW/15-AccessToSQLServer7.html Basically, 1. Close all database objects...

Web resources about - MS SQL Linked Servers(Linking MySQL to MS SQL) Collation problem. - comp.databases.ms-sqlserver

Collation - Wikipedia, the free encyclopedia
This article is about collation in library, information, and computer science. For other uses, see Collation (disambiguation) . Collation is ...

Realmac updates Ember image collation apps for iOS and Mac
Realmac Software on Tuesday released all-new versions of its image collecting and syncing title Ember, adding annotations to the iOS app and ...

View Collation in CouchDB
A couple of weeks ago I learned about a very neat feature of CouchDB called view collations . Basically, view collations enables us to make joins ...

Naya Rivera Speaks With Creative Collation In D.C. Ahead of Correspondent's Dinner
... will be attending the White House Correspondent’s Dinner this weekend. On Friday (April 24), Naya was on hand with the Creative Collation, ...

Bailee Madison, Naya Rivera & Troian Bellisario Dine Out With Creative Collation in D.C.
Bailee Madison channels her inner princess for The Creative Coalition 2015 Benefit Dinner held at STK Washington D.C. on Friday ...

Yokto Brings its Video-Collation Tool to Facebook
We first reviewed Yokto several months back. Just to recap, it's a free tool that lets you collect and embed videos from multiple sources and ...

Lapid: Unilateral annexations will lead collation to collapse
Lapid blames Abbas for derailing peace talks, but reiterates support for two-state solution, and slams Bennett's proposal for annexing parts ...

Elegies; and other elegies included in the Theognidean sylloge. A rev. text based on a new collation ...
Internet Archive BookReader - Elegies; and other elegies included in the Theognidean sylloge. A rev. text based on a new collation of the Mutinensis ...

Collation (disambiguation) - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Collation - Wikipedia, the free encyclopedia
This article is about collation in library, information, and computer science. For other uses, see Collation (disambiguation) . Collation is ...

Resources last updated: 3/22/2016 2:19:26 AM