I have a Linked Server from SQL Server to my mySQL database (Windows Server using MySQL ODBC Driver 5.3). I have a unique situation where I can only get my longtext columns to return if I convert them in an OPEN QUERY:
SELECT *
FROM
OPENQUERY (woocommerce, 'SELECT meta_id, CONVERT(meta_value using UTF8) as meta_value
FROM woocommerce.wp_postmeta WHERE meta_id = 9465078')
If I don't convert/cast it I get the following error from the linked server:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "woocommerce" reported an error. The provider reported an unexpected catastrophic failure.
I need to update this longtext column, "meta_value", but I can't since it has been casted/converted.
UPDATE
OPENQUERY (woocommerce, 'SELECT meta_id, CONVERT(meta_value using utf8)
FROM woocommerce.wp_postmeta WHERE meta_id = 9465078')
SET meta_value = 'outofstock';
Is there any way to update a casted/converted column using OPENQUERY and a linked server? I've tried converting or casting it every which way.
Driver seems have problem with LONGTEXT
column.
You could limit the LONGTEXT
column size with option:
Limit column size to signed 32-bit range
(Take care to not lose your data, but it's a rarely case, I don't think any String would longer than 1 billions characters XD)
The last comment here would help you how to do that
Now you can remove the cast and can proceed updating normally.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With