2

I set up a linked server within Microsoft SQL Server. The linked server points at a MySQL database. I am trying to select data from SQL Server and insert it into the MySQL database.

I have the following query transferring pricing information from SQL Server to MySQL via the linked server:

insert into MYSQL_DB...ProductPrice(
    Price,
    ProductId
)
select
    p.Price,
    p.ProductId
from
    SQL_Server_DB.ProductPrice p

I receive the following error when I try to do this:

The OLE DB provider "MSDASQL" for linked server "MYSQL_DB" supplied invalid metadata for column "Price". The precision exceeded the allowable maximum.

The field "Price" is a decimal. In SQL Server, it is a decimal(18, 0). In MySQL, the field is decimal(65, 30).

I understand that these are different precision levels. But the error message makes it seem like the SQL Server precision exceeds the MySQL precision, which is not true from my understanding.

I'm a little confused about how to resolve this. Anyone have any suggestions?

eclaire211
  • 101
  • 2
  • 12
  • 1
    Could it be that the provider itself does not "understand" this type or alternatively it passes the data type to SQL Server verbatim and SQL Server complains as SQL Server has a maximum of 38 digits of precision? – Alex Aug 25 '17 at 00:03
  • What is your MySQL version? if you are using old MySQL try to upgrade it and see if the result is still the same and add it to your question. – ICE Aug 25 '17 at 00:07
  • @Alex - I'm not sure I understand what you mean exactly (I don't have a ton of experience with this stuff). Could you explain further? – eclaire211 Aug 25 '17 at 00:17
  • @ICE - Unfortunately, I can't upgrade the version. But it's currently on version 5.6.10 – eclaire211 Aug 25 '17 at 00:17
  • 2
    @eclaire211 - my guess (it is only a guess) is that SQL Server complains about MySQL data type being too large. SQL Server supports DECIMAL precision up to 38 (`DECIMAL( 38, 0 )`). Try reducing (changing) the data type precision of the MySQL column to something that SQL Server can support e.g. DECIMAL( 38, 10 ) – Alex Aug 25 '17 at 00:46
  • Make the definitions the same! Suggest you discover what the numbers are like and make them small enough to comfortably handle the largest (and most decimals) found in the data. – Rick James Jan 21 '18 at 15:19

0 Answers0