I am getting this error when running my application, built using the sync framework, OFFLINE
The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates.
I have a database holding some tests, TestOptions. Another holding test collections called CreateScript. These tables are used to populate a list view with tests to be carried out.
Using the Sync framework these tables have been cloned locally as SQL Server CE tables.
The user selects the desired test and the result is stored in the results table.
When populating the list view a query is run using two joins incorporating all three of these tables to gather the data for tests.
select
TestType, TestName, LowerLimits, UpperLimits
from
CreateScriptTable
inner join
TestOptionsTable on CreateScriptTable.TestType = TestOptionsTable.TestName
LEFT JOIN
TestResultsTable on CreateScriptTable.TestType = TestResultsTable.TestName
WHERE
CreateScriptTable.InstrumentType= 'type1'
ORDER BY
[Index] ASC
This query works fine when the application is online. When the application is offline I get the exception above about the nText and image datatypes.
I also get the exception,
Large objects (ntext and image) cannot be used in ORDER BY clauses.
when running a similar query to the one above.
In my database there are no column with these datatypes. From reading around it seems that the varbinary(MAX) and nvarchar(MAX) datatypes are cast to image and nText respectively on the local SQL Server CE database. To fix this I changed the datatypes to nvarchar(4000) and varbinary(4000) but the problem still persists.
There is a hot fix available here but it pre-dates the version SQL Server CE that is on my machine. I have not tested it yet as I am wary of messing up my development environment and am very close to needing a full build to demo.
I also toyed with the idea of creating in memory DataTables for each table and querying those but this has not worked out either.
At the moment I am out of ideas and would really appreciate any help.
