OleDbDataAdapter内部错误:无效的行集访问器.状态=未支持转换 [英] OleDbDataAdapter internal error: invalid row set accessor. Status=UNSUPPORTEDCONVERSION

问题描述

我目前正在尝试将应用程序从Microsoft Access数据库移至SQL Server Compact Edition.我已经设法使用SSMA和SQL Toolbox转换了数据库，但是使用旧的c#代码进行查询时出现了一些错误(如预期的那样).到目前为止，我已经能够解决问题，但是我对此感到有些困惑:

I am currently trying to move an application from a Microsoft Access database to an SQL Server Compact Edition one. I have managed to convert the database using SSMA and SQL Toolbox, but I am having some errors using the old c# code for queries (as expected). I have been able to fix things as I go along so far, but I am a bit stumped with this one:

OleDbDataAdapter内部错误:无效的行集访问器:Ordinal =#Status = UNSUPPORTEDCONVERSION

OleDbDataAdapter internal error: invalid row set accessor: Ordinal=# Status=UNSUPPORTEDCONVERSION

环顾了stackoverflow和google之后，我发现这通常是由于使用ntext列导致的.我没有使用任何东西，但是我在表的第18列上使用了NVARCHAR(MAX)列.显然，SQL Server 2012支持这些 .

After looking around on stackoverflow and google I found that often this results from using an ntext column. I'm not using any however, but I am using an NVARCHAR(MAX) column on the 18th column of the table. Apparently these are supported for SQL Server 2012.

无论如何，我决定将列转换为nvarchar(255)，以尝试使用此T-SQL查询进行修复:

I decided to convert the column to nvarchar(255) anyway to try to fix using this T-SQL query:

UPDATE dbo.Table1 SET Col1 = LEFT(Col1, 255)
GO
ALTER TABLE dbo.Table ALTER COLUMN Col1 NVARCHAR(255)

这仍然无法解决错误，并且由于我不确定到底是什么顺序位置，因此我使用以下查询进行了仔细检查:

This still didn't fix the error, and as I wasn't exactly sure what an ordinal position was anyway I used the following query to double check:

SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
ORDINAL_POSITION
FROM GPLADB.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table1'
ORDER BY ORDINAL_POSITION

这产生了以下结果(顺便说一句，个人资料"是我从nvarchar(MAX)更改为nvarchar(255)的列):

This yielded the following result (BTW 'Profile' is the column that I changed from nvarchar(MAX) to nvarchar(255)):

有人对调试下一步有什么建议吗?一种选择是使用SQL Server CE类重新编写我的所有代码，但这会花费很多时间，所以这将是最后的选择.

Does anyone have any suggestions on where to go next with debugging? One option is to re-write all my code using SQL Server CE classes, but this would take ages so that would be a last resort.

推荐答案

我的错误是在进行更改后没有将我的SQL Server数据库转换为SQL CE.似乎OleDb仍然不支持nvarchar(max)，所以我的解决方案是使用以下代码查找所有使用nvarchar(max)的列，因为这些字段的长度为-1:

My error was in not converting my SQL Server database to SQL CE after making the changes. It seems that nvarchar(max) is not supported by OleDb still however, so my solution was to use the following code to find all columns using nvarchar(max) as the length was -1 for these fields:

SELECT 
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
FROM [DatabaseName].COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH = '-1'

然后为找到的每个字段创建一个包含以下代码的脚本:

And then by creating a script with the following code for each field found:

UPDATE dbo.Table1 SET Col1 = LEFT(Col1, 4000)
GO
ALTER TABLE dbo.Table ALTER COLUMN Col1 NVARCHAR(4000)

这篇关于OleDbDataAdapter内部错误:无效的行集访问器.状态=未支持转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！