Insert into openquery MySQL

Question

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?

Answer 1

0

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

answered Sep 02, 2010 at 08:44 PM

Oleg
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)

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

Insert into openquery MySQL

Follow this question

Related Questions