x

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?
more ▼

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

parshef gravatar image

parshef
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:

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
more ▼

answered Sep 02, 2010 at 08:44 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

+1 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x108
x68
x26

asked: Sep 02, 2010 at 04:31 PM

Seen: 4068 times

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