Insert into MySQL from MS SQL Server
Date: September 03, 2010 10:35AM
I have got a SQL Server with a linked server to a MySQL DB.
I'm using an openquery to insert into the MySQL DB everything works fine except that any MySQL Decimal(19,4) that is getting data from SQL Server end up being 10,000 times bigger
ex. SQL 123.45 > MySQL 1234500.0000
INSERT INTO OPENQUERY (MYSQL, 'SELECT * FROM MySQL_Table')
SELECT * FROM dbo.SQL_Table
I have tried
INSERT INTO OPENQUERY (MYSQL, 'SELECT * FROM MySQL_Table'
SELECT ..., cast(trouble_column as decimal(19, 4)), ..... FROM dbo.SQL_Table
and have had the same result
I have also tried to make the MySQL a Decimal (19,2) and then I get this as a result 123.45 > 12345.00
I have also tried to change the MySQL table to a collation of latin1_general_ci from latin1_swedish_ci and have had the same results
The one solution that I do have is to update the MySQL tables decimal(19, 4) after the insert.
UPDATE MySQL_Table SET trouble_column = trouble_column / 10,000
But I do not feel that this is a practical solution.
Is there any one that does not have this problem?
If so what versions of SQL Server, MySQL and MySQL connector are you running?
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.