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

 SELECT * FROM dbo.SQL_Table

has any one had this problem? and if so what did they do?

more ▼

asked Sep 02, 2010 at 04:31 PM in Default

avatar image

1 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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:

     col1, col2, -- etc
     cast(trouble_column as decimal(19, 4)) as trouble_column, -- other columns
     FROM dbo.SQL_Table;


more ▼

answered Sep 02, 2010 at 08:44 PM

avatar image

20.6k 3 7 29

  • Good practice!

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 02, 2010 at 04:31 PM

Seen: 5547 times

Last Updated: Sep 03, 2010 at 08:58 AM

Copyright 2018 Redgate Software. Privacy Policy