Wide varchar field causes "Requested conversion is not supported" error using openquery with MySQL linked server

I'm trying to migrate a table from MySql to MSSQL using openquery but I keep getting the following error message:

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

The SQL query I'm trying to run:

insert into dbo.tickets (id, description, createdAt)
select * from openquery(MYSQL, 'select * from mydb.tickets')

With openquery I have already copied a couple tables but this one tricks me.

On both side of databases the description field is varchar(8000). In MySql there is no row where description is null and the longest description is only 5031 characters.

I tried creating a new view in MySql with the same data structure but got the same error. I can't determine which row has an invalid description field because the table contains more than 65000 rows.

I also tried dumping the data into an SQL file but I got OutOfMemoryException in Management Studio. The dumped sql file itself is about 60 MB.

Any suggestions or other ways of migrating this data?

Thanks in advance!

Answers


In my testing, I found that adding CAST(field as char(4000)) also solved the problem.

I created the following in a MySQL 5.1 database:

create table tmp_patrick (summary_text varchar(4096));
insert into tmp_patrick values ('foo');

When I executed the following on SQL Server 2008 R2 SP1 (10.50.2500), using MySQL ODBC driver 64-bit, either version 5.1 or 5.2w:

select * from openquery(MYSQL, 'select summary_text from scratch.tmp_patrick')

it generates the error:

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

but if I add CAST:

select * from openquery(MYSQL, 'select CAST(summary_text as char(4000)) from scratch.tmp_patrick')

then it works. Casting to char(4001) will fail.

It's not clear to me where the 4000 character limit comes from.


I managed to fix this issue by changing the datatype to TEXT at both MySql and MSSQL side.


Need Your Help

multiple modal windows with js css and html

javascript html css

I am making multiple modal windows , although I am repeating myself. If I could see a different approach to the javascript in order to make this more terse I would definitely appreciate it. And I ...

precision in python upto 50 decimal places

python precision floating-point-precision double-precision

I am trying to code to find out the radius of the inner soddy circles, the code is running fine but i am not getting the required precision, i want the answer to be truncated to 50 digits