1

I've been using the connector for a few years now to push and pull data between a MS SQL Server database and a couple different MySQL databases. I set up a Linked Server, then using OPENQUERY I create the views I need and I write my selects, updates, and inserts against those views. Works like a dream.

However, I'm trying to integrate with a new MySQL database built by a vendor which uses the ENUM datatype, which is causing me trouble.

When I try the OPENQUERY I get a weird error:

OLE DB provider 'MSDASQL' for linked server 'MYSQL_DATABASE' returned data that does not match expected data length for column '[MSDASQL].EnumDataField'. The (maximum) expected data length is 10, while the returned data length is 8.

I can fix this by converting the ENUM field to a CHAR in the query and it works ok.

But now I need to insert or update that ENUM field, and I cannot figure out how to do it. If I convert the datatype on the view, I can't use that view to insert or update.

Is there a way for me to work with ENUM fields through the connector? Especially a way for me to do INSERT or UPDATE of an ENUM value?

jbz
  • 163
  • 7
  • 1
    Do any of the workarounds described in this bug report work for you? https://bugs.mysql.com/bug.php?id=46857 – Shadow Oct 05 '20 at 18:28
  • Alternatively, have you tried to update it using the index number (the position of the string value in the list of values) as opposed to the string value? – Shadow Oct 05 '20 at 18:32
  • I saw that earlier and found that adding DBCC TRACEON(8765) to my query would indeed allow me to pull in the data without having to do a char conversion. However, it doesn't seem to help with insert/update. – jbz Oct 05 '20 at 19:01
  • Any luck with this? I have the same exact issue and can't find a resoution! Thanks – Dawsy Mar 04 '21 at 22:58

0 Answers0