宽varchar字段会导致“不支持请求的转换".在MySQL链接服务器上使用openquery时出错 [英] Wide varchar field causes "Requested conversion is not supported" error using openquery with MySQL linked server

问题描述

我正在尝试使用openquery将表从MySql迁移到MSSQL，但我不断收到以下错误消息:

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

我要运行的SQL查询:

The SQL query I'm trying to run:

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

使用openquery，我已经复制了几张桌子，但是这欺骗了我.

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

在数据库的两侧，description字段均为varchar(8000).在MySql中，没有描述为null的行，最长的描述仅为5031个字符.

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.

我尝试在MySql中使用相同的数据结构创建一个新视图，但遇到了相同的错误. 我无法确定哪一行的描述字段无效，因为该表包含超过65000行.

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.

我也尝试将数据转储到SQL文件中，但是我在Management Studio中得到了OutOfMemoryException.转储的sql文件本身约为60 MB.

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?

提前谢谢！

推荐答案

我设法通过将MySql和MSSQL端的数据类型都更改为TEXT来解决了此问题.

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

这篇关于宽varchar字段会导致“不支持请求的转换".在MySQL链接服务器上使用openquery时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！