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
has any one had this problem? and if so what did they do?
(comments are locked)
|
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:
Oleg
Sep 03, 2010 at 01:38 AM
sp_lock
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?
Sep 03, 2010 at 08:52 AM
parshef
@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.
Sep 03, 2010 at 09:01 AM
Oleg
(comments are locked)
|