1

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 internal error: invalid row set accessor: Ordinal=# Status=UNSUPPORTEDCONVERSION

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.

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

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

enter image description here

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mbdavis
  • 3,861
  • 2
  • 22
  • 42
  • I is not supported to use OLEDB for sql ce access from managed code, you should be using the ADO.net provider (System.Data.SqlServerCe) – ErikEJ Feb 16 '16 at 07:50
  • @ErikEJ It is supported! I don't want to have to re-write masses of code, turns out my error was in not re-converting my sql server database to sql ce. My bad. – mbdavis Feb 16 '16 at 13:15

1 Answers1

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)
mbdavis
  • 3,861
  • 2
  • 22
  • 42