MySQL > SSMS 2012 Linked Server "Requested conversion is not supported."

Question

This little gem has kept me going for a wee while now and I'm still not much further forward.

Environment

• SSMS 2012 SP1 (target) (Server 2012 64 bit)
• MySQL (source)
• Linked server using ODBC 5.3 64bit Unicode driver

I am trying to port the tables from MySQL onto SSMS. A majority of the tables work fine....until I hit a column that is cast > 8000 chars.

As I was having an issue I tried to just select the offending Columnm

Running a simple

SELECT RogueCol  FROM LinkServer...MyTbl

returns the error

OLE DB provider "MSDASQL" for linked server "LinkServer" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].RogueCol" from OLE DB provider "MSDASQL" for linked server "LinkServer". 

So I then tried

SELECT * FROM OPENQUERY(LinkServer, 'SELECT RogueCol  FROM MyTbl')

This returns the same error

I'm totally confused as I cannot seem to even view the data in SSMS, let alone SELECT it into a table (I have a script that will cast the fields according to some rules based on their original MySQL castings)

As I say, for a majority of other fields its not an issue.

The MySQL casting of the RogueCol is varchar(32767)

The settings enabled on the MSDASQL Provider is:

• Nested Queries
• Level Zero Only
• Allow Inprocess
• Supports 'Like'

So far I've tried a raft of things which I've 'discovered' on various forums including:

• CASTING, CONVERTING, LEFT, LTRIM(RTRIM()) on the actual call.
• Tried also restricting it to a TOP 1 record (LEN = 45, Doesn't have any white space or foreign characters in the string)
• Set up the ANSI version of the ODBC driver
• Various settings within the ODBC driver (Allow Big Result sets, changing Character sets, Limit Columns to 32 bit, Pad char with space among others)

Can I call upon your collective knowledge and request some further suggestions please as this driving me nuts?

Many thanks

Answer 1

UPDATE

So guys and girls, no responses

I ended up having to talk to the DBA responsible for the MySQL DB and asked him to alter the casting of the RogueCol from varchar(32767) to TEXT

Job jobbed

Answer 2

If the linked server is not available for editing, you can always use cast(RogueCol as text) in OPENQUERY

SELECT 
RogueCol
FROM OPENQUERY([LinkServer],
'
SELECT 
cast (RogueCol as text) as RogueCol
FROM public.MyTbl
')