MySQL Forums
Forum List  »  Microsoft SQL Server

Insert into MySQL from MS SQL Server
Posted by: Par S
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
GO


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?

Options: ReplyQuote


Subject
Written By
Posted
Insert into MySQL from MS SQL Server
September 03, 2010 10:35AM


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.