3

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!

patrickmdnet
  • 3,332
  • 1
  • 29
  • 34
papaiatis
  • 4,231
  • 4
  • 26
  • 38
  • Try using a differnet version of MySQL OLE DB Provider. – JotaBe Apr 12 '12 at 12:04
  • I had similar troubles with FoxPro drivers, and it could only be solved by finding the offensive row, and changing it on the FoxPro side (I had problems with date which were out of the normal range). Although it looks too hard, you can find it quite quickly if you try querying filtering by halves (passing from 65.000 -> 32.000 -> 16.000 -> 8.000 -> 4.000 -> 2.000 -> 1.000 -> 500 -> 250 -> 125 -> 62 -> to 31 takes only 14 shots). – JotaBe Apr 12 '12 at 12:11
  • Thx guys! It seems changing the datatype from varchar to text worked! – papaiatis Apr 12 '12 at 12:14

2 Answers2

9

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.

patrickmdnet
  • 3,332
  • 1
  • 29
  • 34
  • I'm wondering if the 4000 character limit is because SQL Server is trying to cast the data to `NVARCHAR`, which has a 4000-character limit. It might also be worth trying a cast to `VARCHAR(MAX)` or `NVARCHAR(MAX)`; that might avoid the problem without trimming down the data, but I don't have a linked MySQL server to test with myself... – Matt Gibson Jan 29 '16 at 11:57
  • Using MAX did not work for me - I had to specifically write the field length for the query to work. Anything over `4000` also returned an error. – Mark Nov 24 '22 at 14:36
4

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

papaiatis
  • 4,231
  • 4
  • 26
  • 38
  • 1
    They're depreciating the text datatype in MSSQL - now your data is across you should probably change the column type back to varchar unless you plan on importing the column again. See the warning [here](http://msdn.microsoft.com/en-us/library/ms187993.aspx). – Bridge Apr 12 '12 at 12:19
  • No problem - remember to mark this answer as the actual answer (when it lets you) so other people know it's been resolved. – Bridge Apr 12 '12 at 12:24