asked
Insert into openquery MySQL
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 has any one had this problem? and if so what did they do?
Rather than using **select \***, try to spell out the column names of the **dbo.SQL\_Table** and cast the problematic column as decimal(19, 4), i.e: INSERT INTO OPENQUERY (MYSQL, 'SELECT * FROM MySQL_Table') SELECT col1, col2, -- etc cast(trouble_column as decimal(19, 4)) as trouble_column, -- other columns FROM dbo.SQL_Table; GO Oleg
I have tried 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?
@parshef This starts looking like the mismatch between the number formats. Collation does not affect how the numbers are formatted, these are the box regional settings. All collation does is it defines the set of rules for string comparisons, so if the collation is latin then it knows that **A** is less than **B**, but will have no clue what to do with cyrillic **Ф** etc Please check the number and currency formatting settings on both servers to make sure that both have consistent meanings for comma and period.
