adodbapi SQL Server CE query crashes when selecting a specific column

Question

After hours of headache I managed to connect to the .sdf database file with Python using adodbapi using this connection string:

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;
Data Source={};
ssce:Database Password='{}';

only to get stuck into this stupid ...... situation ....

Selection will work with any other column except column B

select * from table_name << crashes

select A, B from table_name << crashes

select A, C from table_name << works

select B from table_name << crashes

So it has to be something about that data because the same query will work just fine using sdfBrowser.

Traceback (most recent call last):

File "C:...PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\adodbapi\adodbapi.py", line 676, in _execute_command
recordset, count = self.cmd.Execute()

File "", line 2, in Execute

File "C:...PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\win32com\client\dynamic.py", line 271, in ApplyTypes
result = self.oleobj.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Cursor Engine', 'Multiple-step operation generated errors. Check each status value.', None, 0, -2147217887), None)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File "d:\data.py", line 63, in
cursor.execute(q)

File "C:...PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\adodbapi\adodbapi.py", line 869, in execute
self._execute_command()

File "C:...PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\adodbapi\adodbapi.py", line 684, in _execute_command
self._raiseCursorError(klass, _message)

File "C:...PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\adodbapi\adodbapi.py", line 557, in _raiseCursorError
eh(self.connection, self, errorclass, errorvalue)

File "C:...PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\adodbapi\apibase.py", line 50, in standardErrorHandler
raise errorclass(errorvalue)

adodbapi.apibase.DatabaseError: (-2147352567, 'Exception occurred.', (0, 'Microsoft Cursor Engine', 'Multiple-step operation generated errors. Check each status value.', None, 0, -2147217887), None)

Command:

select A,B from [table_name]

Parameters:
[]

Any suggestions?

Answer 1

The desktop OLEDB provider is "borken" for columns of type TEXT and IMAGE.

Workarounds: Change the column type to nvarchar(4000), skip the column, use a C# library for access the SQL CE database.

Docs are no longer available, as SQL Compact 3.5 is no longer supported.

But the SQL CE 4.0 docs ar available here: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms173053(v=sql.110)