0

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.

THEn
  • 137
  • 2
  • 8
  • 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

3 Answers3

1

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'
djangofan
  • 4,172
  • 10
  • 45
  • 59
  • 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
1

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

1

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.