Using Enum fields in MS SQL Server Studio Linked Server

[Jason Zech]

Hi all. 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 in the connector? Especially a way for me to do INSERT or UPDATE?

Thank you!
jz