8

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.

Paul D
  • 676
  • 6
  • 8
  • is meta_value null or not null by definition? – Shubham Aug 09 '18 at 01:19
  • It's not null. Would that affect it? For now we got around it by making a 2nd column called meta_value_overflow (of type text), but it would be ideal if we could just have one longtext column. – Paul D Aug 13 '18 at 00:56
  • Sorry I can't get what you are doing, can you provide the diagram? To me it seem you don't need to `SELECT` **meta_value** you don't use it anywhere. Can't you just: `UPDATE OPENQUERY (woocommerce, 'SELECT meta_id FROM woocommerce.wp_postmeta WHERE meta_id = 9465078') SET meta_value = 'outofstock';` . ? – Alex Aug 13 '18 at 16:13

1 Answers1

1

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.

Mạnh Quyết Nguyễn
  • 17,677
  • 1
  • 23
  • 51