Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have MSSQL 2005 server and I have MySQL database in intranet. I want to replicate some tables from MSSQL database to MySQL database.

I have created ODBC licnked server and have successfully created transactional publication and I have manually created exactly same tables in MySQL and replication seems to be working.

The reason why I have manually created tables in MySQL is because there was a problem creating keys and indexes from within replication from MSSQL. What happens is when replication runs it converts varchar(200) field into MySQL varchar which this field becomes mediumtext in MySQL Table and show an error when trying to create a primary key or index. How should I configure the publication to create exact copy of table fields with correct length in MySQL?

I have MySQL ODBC 5.1 driver installed on server.

EDIT:

When I see MySQL.Log file it shows query

CREATE TABLE `Inventory`(
    `SKU`   long varchar NOT NULL,

which becomes mediumtext in MySQL database afterward. That show error something l ike

BLOB/TEXT column 'SKU' used in key specification without a key length

in MSSQL replication job history. When I look into table in MySQL the Field type for 'SKU' is mediumtext.

share|improve this question
    
Please clarify "varchar(200) field into MySQL varchar which this field becomes mediumtext in MySQL". I think that one of the "MySQL" should read "MSSQL". Still, I wouldn't use varchar, I'd create an exact length match. –  John Gardeniers Jan 11 '10 at 18:42
add comment

3 Answers

You can probably use SQuirreL SQL client (in Java) to export tables and then import them into MySQL via JDBC . I doubt there is a ODBC method of doing what you need.

Also, you could export the SQL database to a CSV file and then import to MySQL using the command:

LOAD DATA LOCAL INFILE C:\test.csv
INTO TABLE tbl_temp_data 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
share|improve this answer
    
We do similar procedure as current replication solution. We want to upgrade the replication procedure as our database grows... –  THEn Jan 11 '10 at 18:36
add comment

Set @schema_option = 0x000000000000000 for all articles so that SQL Server won't touch anything.

share|improve this answer
add comment

Also, don't use GUI when creating the publication, adding articles and creating subscriptions as it screws up everything. Do everything manually using SQL script.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.