Mismatch or data overflow on MySQL Link Server

• makis_best

  Mr or Mrs. 500

  Points: 528

  More actions

  June 7, 2018 at 3:20 am

  #362131

  I try to Insert some records to my remote MySql from my SQL Server.
  The MySql is connected as Link server.

  The script I run is,
  
INSERT INTO OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, ERPCode from rszcf_virtuemart_categories_el_gr')
(virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT AA1, Category_Name1, Category_Description1, Metadesc1, Metakey1, CustomTitle1, slug1, ERPCode1 FROM EGM_WEB_Categories_el_gr T1
WHERE NOT EXISTS
(
  SELECT 1
  FROM OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode from rszcf_virtuemart_categories_el_gr') T2 WHERE
  T2.erpcode = t1.ERPCode1 collate Greek_CI_AI
)


  and always I get the error

  The OLE DB provider "MSDASQL" for linked server "LINKSERVER1" could not INSERT INTO table "[MSDASQL]" because of column "category_description".
  Could not convert the data value due to reasons other than sign mismatch or overflow.

  In the Mysql the category_description field  is VARCHAR(19000) and in the SQL SERVER is nvarchar(255)

  How I can pass this error?
  Any help?

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  June 7, 2018 at 5:12 am

  #1993183

  makis_best - Thursday, June 7, 2018 3:20 AM

  I try to Insert some records to my remote MySql from my SQL Server.
  The MySql is connected as Link server.

  The script I run is,
  
INSERT INTO OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, ERPCode from rszcf_virtuemart_categories_el_gr')
(virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT AA1, Category_Name1, CAST(Category_Description1 AS text) AS Category_Description1, Metadesc1, Metakey1, CustomTitle1, slug1, ERPCode1 FROM EGM_WEB_Categories_el_gr T1
WHERE NOT EXISTS
(
  SELECT 1
  FROM OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode from rszcf_virtuemart_categories_el_gr') T2 WHERE
  T2.erpcode = t1.ERPCode1 collate Greek_CI_AI
)


  and always I get the error

  The OLE DB provider "MSDASQL" for linked server "LINKSERVER1" could not INSERT INTO table "[MSDASQL]" because of column "category_description".
  Could not convert the data value due to reasons other than sign mismatch or overflow.

  How I can pass this error?
  Any help?

  So you're not going to tell us the datatype of category_description, yet you still expect a useful answer? 🙂

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• makis_best

  Mr or Mrs. 500

  Points: 528

  More actions

  June 7, 2018 at 5:37 am

  #1993190

  Phil Parkin - Thursday, June 7, 2018 5:12 AM

  makis_best - Thursday, June 7, 2018 3:20 AM

  I try to Insert some records to my remote MySql from my SQL Server.
  The MySql is connected as Link server.

  The script I run is,
  
INSERT INTO OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, ERPCode from rszcf_virtuemart_categories_el_gr')
(virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT AA1, Category_Name1, CAST(Category_Description1 AS text) AS Category_Description1, Metadesc1, Metakey1, CustomTitle1, slug1, ERPCode1 FROM EGM_WEB_Categories_el_gr T1
WHERE NOT EXISTS
(
  SELECT 1
  FROM OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode from rszcf_virtuemart_categories_el_gr') T2 WHERE
  T2.erpcode = t1.ERPCode1 collate Greek_CI_AI
)


  and always I get the error

  The OLE DB provider "MSDASQL" for linked server "LINKSERVER1" could not INSERT INTO table "[MSDASQL]" because of column "category_description".
  Could not convert the data value due to reasons other than sign mismatch or overflow.

  How I can pass this error?
  Any help?

  So you're not going to tell us the datatype of category_description, yet you still expect a useful answer? 🙂

  I am so sorry....  you right...  I forget it
  In the Mysql the category_description field is VARCHAR(19000) and in the SQL SERVER is nvarchar(255)

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  June 7, 2018 at 6:28 am

  #1993197

  OK, try this version:

  INSERT INTO OPENQUERY
    (LINKSERVER1
    ,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, ERPCode from rszcf_virtuemart_categories_el_gr'
    )
(
  virtuemart_category_id
, category_name
, category_description
, metadesc
, metakey
, CustomTitle
, slug
, erpcode
)
SELECT
  AA1
,  Category_Name1
,  Category_Description1 = CAST(Category_Description1 AS VARCHAR(255))
,  Metadesc1
,  Metakey1
,  CustomTitle1
,  slug1
,  ERPCode1
FROM EGM_WEB_Categories_el_gr T1
WHERE
  NOT EXISTS
(
  SELECT 1
  FROM
    OPENQUERY
    (LINKSERVER1
    ,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode from rszcf_virtuemart_categories_el_gr'
    ) T2
  WHERE T2.erpcode = t1.ERPCode1 COLLATE Greek_CI_AI
);

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• makis_best

  Mr or Mrs. 500

  Points: 528

  More actions

  June 7, 2018 at 2:29 pm

  #1993308

  Thank you for your reply Phil Parkin.
  I just try your version but I keep getting the same error.

• makis_best

  Mr or Mrs. 500

  Points: 528

  More actions

  June 8, 2018 at 3:59 am

  #1993338

  makis_best - Thursday, June 7, 2018 2:29 PM

  Thank you for your reply Phil Parkin.
  I just try your version but I keep getting the same error.

  Phil Parkin - Thursday, June 7, 2018 6:28 AM

  OK, try this version:

  INSERT INTO OPENQUERY
    (LINKSERVER1
    ,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, ERPCode from rszcf_virtuemart_categories_el_gr'
    )
(
  virtuemart_category_id
, category_name
, category_description
, metadesc
, metakey
, CustomTitle
, slug
, erpcode
)
SELECT
  AA1
,  Category_Name1
,  Category_Description1 = CAST(Category_Description1 AS VARCHAR(255))
,  Metadesc1
,  Metakey1
,  CustomTitle1
,  slug1
,  ERPCode1
FROM EGM_WEB_Categories_el_gr T1
WHERE
  NOT EXISTS
(
  SELECT 1
  FROM
    OPENQUERY
    (LINKSERVER1
    ,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode from rszcf_virtuemart_categories_el_gr'
    ) T2
  WHERE T2.erpcode = t1.ERPCode1 COLLATE Greek_CI_AI
);

  The same....  Nothing changed....
  I Get the same error.

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  June 8, 2018 at 5:32 am

  #1993346

  I gave you my best guess, but I don't know much about MySQL, so I think you need help from others.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• makis_best

  Mr or Mrs. 500

  Points: 528

  More actions

  June 8, 2018 at 6:11 am

  #1993353

  Phil Parkin - Friday, June 8, 2018 5:32 AM

  I gave you my best guess, but I don't know much about MySQL, so I think you need help from others.

  I appreciate you help...
  I keep searching what is going on...   Probably it is a bug.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  June 8, 2018 at 12:02 pm

  #1993430

  makis_best - Thursday, June 7, 2018 3:20 AM

  I try to Insert some records to my remote MySql from my SQL Server.
  The MySql is connected as Link server.

  The script I run is,
  
INSERT INTO OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, ERPCode from rszcf_virtuemart_categories_el_gr')
(virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT AA1, Category_Name1, Category_Description1, Metadesc1, Metakey1, CustomTitle1, slug1, ERPCode1 FROM EGM_WEB_Categories_el_gr T1
WHERE NOT EXISTS
(
  SELECT 1
  FROM OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode from rszcf_virtuemart_categories_el_gr') T2 WHERE
  T2.erpcode = t1.ERPCode1 collate Greek_CI_AI
)


  and always I get the error

  The OLE DB provider "MSDASQL" for linked server "LINKSERVER1" could not INSERT INTO table "[MSDASQL]" because of column "category_description".
  Could not convert the data value due to reasons other than sign mismatch or overflow.

  In the Mysql the category_description field  is VARCHAR(19000) and in the SQL SERVER is nvarchar(255)

  How I can pass this error?
  Any help?

  Well, not necessarily a bug. But stop and think about how you code an insert. Normal INSERT syntax is likely rather similar between MySQL and SQL Server, so why wouldn't you just do something like this:

  INSERT INTO LINKSERVER1..rszcf_virtuemart_categories_el_gr (virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT
  AA1,
  Category_Name1,
  CAST(Category_Description1 AS text) AS Category_Description1,
  Metadesc1,
  Metakey1,
  CustomTitle1,
  slug1,
  ERPCode1
FROM EGM_WEB_Categories_el_gr AS T1
WHERE NOT EXISTS
  (
  SELECT 1
  FROM OPENQUERY(LINKSERVER1, '
   select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode
   from rszcf_virtuemart_categories_el_gr
   ') AS T2
  WHERE T2.erpcode = t1.ERPCode1 COLLATE Greek_CI_AI
  );

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• makis_best

  Mr or Mrs. 500

  Points: 528

  More actions

  June 9, 2018 at 1:16 am

  #1993469

  sgmunson - Friday, June 8, 2018 12:02 PM

  makis_best - Thursday, June 7, 2018 3:20 AM

  I try to Insert some records to my remote MySql from my SQL Server.
  The MySql is connected as Link server.

  The script I run is,
  
INSERT INTO OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, ERPCode from rszcf_virtuemart_categories_el_gr')
(virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT AA1, Category_Name1, Category_Description1, Metadesc1, Metakey1, CustomTitle1, slug1, ERPCode1 FROM EGM_WEB_Categories_el_gr T1
WHERE NOT EXISTS
(
  SELECT 1
  FROM OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode from rszcf_virtuemart_categories_el_gr') T2 WHERE
  T2.erpcode = t1.ERPCode1 collate Greek_CI_AI
)


  and always I get the error

  The OLE DB provider "MSDASQL" for linked server "LINKSERVER1" could not INSERT INTO table "[MSDASQL]" because of column "category_description".
  Could not convert the data value due to reasons other than sign mismatch or overflow.

  In the Mysql the category_description field  is VARCHAR(19000) and in the SQL SERVER is nvarchar(255)

  How I can pass this error?
  Any help?

  Well, not necessarily a bug. But stop and think about how you code an insert. Normal INSERT syntax is likely rather similar between MySQL and SQL Server, so why wouldn't you just do something like this:

  INSERT INTO LINKSERVER1..rszcf_virtuemart_categories_el_gr (virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT
  AA1,
  Category_Name1,
  CAST(Category_Description1 AS text) AS Category_Description1,
  Metadesc1,
  Metakey1,
  CustomTitle1,
  slug1,
  ERPCode1
FROM EGM_WEB_Categories_el_gr AS T1
WHERE NOT EXISTS
  (
  SELECT 1
  FROM OPENQUERY(LINKSERVER1, '
   select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode
   from rszcf_virtuemart_categories_el_gr
   ') AS T2
  WHERE T2.erpcode = t1.ERPCode1 COLLATE Greek_CI_AI
  );

  I can't do that...
  SQL Server producer doesn't recognize the syntax of the insert....
  The insert command is inside procedure of sql server.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  June 11, 2018 at 6:16 am

  #1993525

  makis_best - Saturday, June 9, 2018 1:16 AM

  sgmunson - Friday, June 8, 2018 12:02 PM

  makis_best - Thursday, June 7, 2018 3:20 AM

  I try to Insert some records to my remote MySql from my SQL Server.
  The MySql is connected as Link server.

  The script I run is,
  
INSERT INTO OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, ERPCode from rszcf_virtuemart_categories_el_gr')
(virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT AA1, Category_Name1, Category_Description1, Metadesc1, Metakey1, CustomTitle1, slug1, ERPCode1 FROM EGM_WEB_Categories_el_gr T1
WHERE NOT EXISTS
(
  SELECT 1
  FROM OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode from rszcf_virtuemart_categories_el_gr') T2 WHERE
  T2.erpcode = t1.ERPCode1 collate Greek_CI_AI
)


  and always I get the error

  The OLE DB provider "MSDASQL" for linked server "LINKSERVER1" could not INSERT INTO table "[MSDASQL]" because of column "category_description".
  Could not convert the data value due to reasons other than sign mismatch or overflow.

  In the Mysql the category_description field  is VARCHAR(19000) and in the SQL SERVER is nvarchar(255)

  How I can pass this error?
  Any help?

  Well, not necessarily a bug. But stop and think about how you code an insert. Normal INSERT syntax is likely rather similar between MySQL and SQL Server, so why wouldn't you just do something like this:

  INSERT INTO LINKSERVER1..rszcf_virtuemart_categories_el_gr (virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT
  AA1,
  Category_Name1,
  CAST(Category_Description1 AS text) AS Category_Description1,
  Metadesc1,
  Metakey1,
  CustomTitle1,
  slug1,
  ERPCode1
FROM EGM_WEB_Categories_el_gr AS T1
WHERE NOT EXISTS
  (
  SELECT 1
  FROM OPENQUERY(LINKSERVER1, '
   select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode
   from rszcf_virtuemart_categories_el_gr
   ') AS T2
  WHERE T2.erpcode = t1.ERPCode1 COLLATE Greek_CI_AI
  );

  I can't do that...
  SQL Server producer doesn't recognize the syntax of the insert....
  The insert command is inside procedure of sql server.

  Does your MySQL instance have a database name and/or schema name for the table you are referencing?   When I used the double-dot notation, it was to skip over the database name and schema name, so it may just be a matter of supplying those values instead of just the double dots.  e.g.:
  LINKEDSERVER1.DatabaseName.SchemaName.rszcf_virtuemart_categories_el_gr

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• makis_best

  Mr or Mrs. 500

  Points: 528

  More actions

  June 12, 2018 at 4:33 am

  #1993657

  sgmunson - Monday, June 11, 2018 6:16 AM

  makis_best - Saturday, June 9, 2018 1:16 AM

  sgmunson - Friday, June 8, 2018 12:02 PM

  makis_best - Thursday, June 7, 2018 3:20 AM

  I try to Insert some records to my remote MySql from my SQL Server.
  The MySql is connected as Link server.

  The script I run is,
  
INSERT INTO OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, ERPCode from rszcf_virtuemart_categories_el_gr')
(virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT AA1, Category_Name1, Category_Description1, Metadesc1, Metakey1, CustomTitle1, slug1, ERPCode1 FROM EGM_WEB_Categories_el_gr T1
WHERE NOT EXISTS
(
  SELECT 1
  FROM OPENQUERY(LINKSERVER1,'select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode from rszcf_virtuemart_categories_el_gr') T2 WHERE
  T2.erpcode = t1.ERPCode1 collate Greek_CI_AI
)


  and always I get the error

  The OLE DB provider "MSDASQL" for linked server "LINKSERVER1" could not INSERT INTO table "[MSDASQL]" because of column "category_description".
  Could not convert the data value due to reasons other than sign mismatch or overflow.

  In the Mysql the category_description field  is VARCHAR(19000) and in the SQL SERVER is nvarchar(255)

  How I can pass this error?
  Any help?

  Well, not necessarily a bug. But stop and think about how you code an insert. Normal INSERT syntax is likely rather similar between MySQL and SQL Server, so why wouldn't you just do something like this:

  INSERT INTO LINKSERVER1..rszcf_virtuemart_categories_el_gr (virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode)
SELECT
  AA1,
  Category_Name1,
  CAST(Category_Description1 AS text) AS Category_Description1,
  Metadesc1,
  Metakey1,
  CustomTitle1,
  slug1,
  ERPCode1
FROM EGM_WEB_Categories_el_gr AS T1
WHERE NOT EXISTS
  (
  SELECT 1
  FROM OPENQUERY(LINKSERVER1, '
   select virtuemart_category_id, category_name, category_description, metadesc, metakey, CustomTitle, slug, erpcode
   from rszcf_virtuemart_categories_el_gr
   ') AS T2
  WHERE T2.erpcode = t1.ERPCode1 COLLATE Greek_CI_AI
  );

  I can't do that...
  SQL Server producer doesn't recognize the syntax of the insert....
  The insert command is inside procedure of sql server.

  Does your MySQL instance have a database name and/or schema name for the table you are referencing?   When I used the double-dot notation, it was to skip over the database name and schema name, so it may just be a matter of supplying those values instead of just the double dots.  e.g.:
  LINKEDSERVER1.DatabaseName.SchemaName.rszcf_virtuemart_categories_el_gr

  I try that way too....  But nothing change
  I am keep searching for the reason that happen

• makis_best

  Mr or Mrs. 500

  Points: 528

  More actions

  July 6, 2018 at 2:38 am

  #1996494

  So many days trying and experiment lot of things... but nothing....
  Any other help?