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!
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.