Niels writes "We've setup a transactional replication between SQL 2000 Server and MySQL 4 using MyODBC, to replicate data from our ERP system. The replication works great. But there's one thing that we are not able to solve.When the initial snapshot is transfered to MySQL, numeric(28,12) columns on the SQL Server side are converted to varchar columns on MySQL side.In the snapshot directory the file ADDRESS.sch contains the following query:CREATE TABLE [ADDRESS] ( [DATASET] [varchar] (3) NOT NULL , [ROWNUMBER] [int] NOT NULL , [LASTCHANGED] [datetime] NOT NULL , [ADDRFILEID] [int] NOT NULL , [ADDRRECID] [int] NOT NULL , [LINENO_] [numeric](28, 12) NOT NULL , [TYPE] [int] NOT NULL , [CODE] [varchar] (10) NOT NULL , [NAME] [varchar] (40) NOT NULL , [ADDRESS1] [varchar] (40) NOT NULL , [ADDRESS2] [varchar] (40) NOT NULL , [ADDRESS3] [varchar] (40) NOT NULL , [ATTENTION] [varchar] (30) NOT NULL , [PHONE] [varchar] (20) NOT NULL , [FAX] [varchar] (20) NOT NULL , [COUNTRY] [varchar] (30) NOT NULL , [SWIFTNUMBER] [varchar] (20) NOT NULL , [ZIP] [varchar] (10) NOT NULL , [REGION] [varchar] (30) NOT NULL , [VATGROUP] [varchar] (10) NOT NULL )GO
When running this query on MySQL (remove all the brackets first), the query succesfully creates a correct table. Column LINENO_ has been created as a Decimal(28,12) column. But when SQL Server distributes this table to MySQL, the column is created as a Varchar(30) column.The following query is save in the MySQL log:CREATE TABLE `ADDRESS_TEST` ( `DATASET` varchar (3) NOT NULL, `ROWNUMBER` integer NOT NULL, `LASTCHANGED` char (23) NOT NULL, `ADDRFILEID` integer NOT NULL, `ADDRRECID` integer NOT NULL, `LINENO_` char (30) NOT NULL, `TYPE` integer NOT NULL, `CODE` varchar (10) NOT NULL, `NAME` varchar (40) NOT NULL, `ADDRESS1` varchar (40) NOT NULL, `ADDRESS2` varchar (40) NOT NULL, `ADDRESS3` varchar (40) NOT NULL, `ATTENTION` varchar (30) NOT NULL, `PHONE` varchar (20) NOT NULL, `FAX` varchar (20) NOT NULL, `COUNTRY` varchar (30) NOT NULL, `SWIFTNUMBER` varchar (20) NOT NULL, `ZIP` varchar (10) NOT NULL, `REGION` varchar (30) NOT NULL, `VATGROUP` varchar (10) NOT NULL);
So now I'm wondering what is to blame. It could be SQL Server not tranfering the correct datatypes. Or it could be the MyODBC driver that is translating query and the datatypes.I would like to hear you opinion.Thanks."
SQL Replication to MySQL: Decimal column is changed 
Posted - 2006-02-20 : 08:18:37
