none
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

    Question

  • Hi

    I am getting an error: 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 while trying to save the changes to SQLCE tables. Please note this is an update operation.

    The Description and note fields store RTF strings. And also I am able to insert data fine but its only the updates that fails

    I have version 3.5.1.0 of SQLCE installed.

    Below is the error message and stack trace with the code:

    System.Data.SqlServerCe.SqlCeException was unhandled
      Message=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.
      Source=SQL Server Compact ADO.NET Data Provider
      HResult=-2147217900
      NativeError=25923
      StackTrace:
           at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
           at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
           at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
           at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
           at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
           at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
           at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicUpdate(TrackedObject item)
           at System.Data.Linq.ChangeDirector.StandardChangeDirector.Update(TrackedObject item)
           at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
           at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

     

     

    Code causing the error:  

     

    var storedBug = DataContext.Bug_Tables.Where(b => b.Id.Equals(bugToSave.Id)).FirstOrDefault();

    storedBug.Name = bugToSave.Name;

    storedBug.DeveloperId = bugToSave.Developer ==

    null ? null

    : bugToSave.Developer.Id;

    storedBug.TesterId = bugToSave.Tester ==

    null ? null

    : bugToSave.Tester.Id;

    storedBug.AssignedPersonId = bugToSave.AssignedPerson ==

    null ? null

    : bugToSave.AssignedPerson.Id;

    storedBug.BugAreaId = bugToSave.BugArea.Id;

    storedBug.BugStatusId = bugToSave.Status.Id;

    storedBug.PriorityId = bugToSave.Priority ==

    null ? null

    : bugToSave.Priority.Id;

    storedBug.Description = bugToSave.Description;

    storedBug.Notes = bugToSave.Notes;

    storedBug.Replication = bugToSave.Replication;

    storedBug.FixedInBuild = bugToSave.FixInBuild;

    • Edited by Amitesh_Sharma Sunday, March 27, 2011 8:27 PM Code not formatted
    Sunday, March 27, 2011 8:09 PM

Answers

  • Hi Amitesh_sharma,

    SQL Server Compact does not support the nvarchar(max) data type or the varbinary(max) data type. The provider may mark the nvarchar(max) data type parameter as the ntext data type or as the image datatype, an error occurs if any equality operations, grouping operations, or sorting operations are being performed on the parameter.

    This problem has been confirmed and fixed by Microsoft, please view the fix link: http://support.microsoft.com/kb/958478#appliesto.


    Best Regards,
    Stephanie Lv

    Tuesday, March 29, 2011 3:28 AM

All replies

  • Please share the SQL statement generated.
    Visit my SQL Server Compact blog - Please mark as answer, if this was it.
    Monday, March 28, 2011 8:34 AM
  • Thanks for replying. How can I get to the SQL statment that was generated. I would have used the Query Analyzer tool that was in SQL but does seem to exit in SSMS 10.50.1600.1

    Monday, March 28, 2011 8:44 AM
  • Finally I managed to get SQL Profiler on another instance of SSMS but it seems like you cannot profile SQLCE database. So is there any other way of getting to the generated SQL statment
    Monday, March 28, 2011 9:13 AM
  • Ok I got the following sql output in my Output window (I used DataContext.Log = Console

    .Out to get output)

    UPDATE [Bug]

    SET [Description] = @p13, [Notes] = @p14, [FixInBuild] = @p15

    WHERE ([Id] = @p0) AND ([Name] = @p1) AND ([DeveloperId] = @p2) AND ([TesterId] = @p3) AND ([AssignedPersonId] IS NULL) AND ([PriorityId] = @p4) AND ([Description] = @p5) AND ([BugStatusId] = @p6) AND ([Notes] = @p7) AND ([Replication] = @p8) AND ([BugAreaId] = @p9) AND ([DateLogged] = @p10) AND ([LoggedByPersonId] = @p11) AND ([BugNumber] = @p12) AND ([FixInBuild] IS NULL)

    -- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [f8816065-f8b2-4bd2-b526-28c81285ebd2]

    -- @p1: Input String (Size = 0; Prec = 0; Scale = 0) [Remember Me - Not working]

    -- @p2: Input Guid (Size = 0; Prec = 0; Scale = 0) [82c1f4e5-d0e2-4d4b-9bf3-6bafd5a51512]

    -- @p3: Input Guid (Size = 0; Prec = 0; Scale = 0) [ff4f3364-eed8-4f3f-8be5-973f055fbd3d]

    -- @p4: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4]

    -- @p5: Input String (Size = 0; Prec = 0; Scale = 0) [The remember me button for login does not work.]

    -- @p6: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]

    -- @p7: Input String (Size = 0; Prec = 0; Scale = 0) []

    -- @p8: Input String (Size = 0; Prec = 0; Scale = 0) []

    -- @p9: Input Int32 (Size = 0; Prec = 0; Scale = 0) [8]

    -- @p10: Input DateTime (Size = 0; Prec = 0; Scale = 0) [22/11/2010 9:19:44 a.m.]

    -- @p11: Input Guid (Size = 0; Prec = 0; Scale = 0) [8eb511ea-5a4a-4ab3-9105-03b99c99cced]

    -- @p12: Input Int32 (Size = 0; Prec = 0; Scale = 0) [16]

    -- @p13: Input String (Size = 0; Prec = 0; Scale = 0) [{\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}

    \viewkind4\uc1\pard\lang5129\f0\fs17 The remember me button for login does not work.l\par

    }]

    -- @p14: Input String (Size = 0; Prec = 0; Scale = 0) [{\rtf1\ansi\ansicpg1252\deff0\deflang5129{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}

    \viewkind4\uc1\pard\f0\fs17\par

    }]

    -- @p15: Input String (Size = 0; Prec = 0; Scale = 0) []

    -- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 4.0.30319.1


    • Edited by Amitesh_Sharma Monday, March 28, 2011 9:26 AM missed comment
    Monday, March 28, 2011 9:22 AM
  • Does your tabel have a primary key? Could you share your table definition (CREATE TABLE ...) ?
    Visit my SQL Server Compact blog - Please mark as answer, if this was it.
    Monday, March 28, 2011 12:07 PM
  • Here is the dump of the Create script:

    CREATE

    TABLE [dbo].[Bug](

    [Id] [uniqueidentifier] NOT

    NULL,

    [Name] [varchar]

    (100) NOT NULL,

    [DeveloperId] [uniqueidentifier]

    NULL,

    [TesterId] [uniqueidentifier]

    NULL,

    [AssignedPersonId] [uniqueidentifier]

    NULL,

    [PriorityId] [int]

    NULL,

    [Description] [nvarchar]

    (max) NULL,

    [BugStatusId] [int]

    NULL,

    [Notes] [nvarchar]

    (max) NULL,

    [Replication] [nvarchar]

    (max) NULL,

    [BugAreaId] [int]

    NULL,

    [DateLogged] [datetime]

    NULL,

    [LoggedByPersonId] [uniqueidentifier]

    NOT NULL,

    [BugNumber] [int]

    NOT NULL,

    [FixInBuild] [varchar]

    (20) NULL,

     

    CONSTRAINT [PK_Bug] PRIMARY KEY CLUSTERED

    (

    [Id]

    ASC

    )

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    )

    ON [PRIMARY]

    GO

    SET

    ANSI_PADDING

    OFF

    GO

    ALTER

    TABLE [dbo].[Bug] WITH CHECK ADD CONSTRAINT [FK_Bug_Person] FOREIGN KEY([LoggedByPersonId]

    )

    REFERENCES

    [dbo].[Person] ([Id]

    )

    GO

    ALTER

    TABLE [dbo].[Bug] CHECK CONSTRAINT [FK_Bug_Person]

    GO

    Monday, March 28, 2011 6:32 PM
  • Hi Amitesh_sharma,

    SQL Server Compact does not support the nvarchar(max) data type or the varbinary(max) data type. The provider may mark the nvarchar(max) data type parameter as the ntext data type or as the image datatype, an error occurs if any equality operations, grouping operations, or sorting operations are being performed on the parameter.

    This problem has been confirmed and fixed by Microsoft, please view the fix link: http://support.microsoft.com/kb/958478#appliesto.


    Best Regards,
    Stephanie Lv

    Tuesday, March 29, 2011 3:28 AM
  • Hi

    Thanks for all the help Stephanie and Erik. I will try and uninstall SQLCE 3.5 SP1 and install the hotfix. I however have SP2 installed in which I would have expected the fix to be present

    Tuesday, March 29, 2011 6:44 PM
  • Hi.
    I'm bringing a death thread to live
    i'm having exactly the same situation . 
    and after i remove my sql ce 3.5 sp2 instalion and instal 3.5 + the hot fix 
    after that i can't connect o sql ce through visual studio, studio management ... and my app blows on weconfig on providers section (hum?! )

    this is an web app (mvc3 + sql ce 3.5)
    and all this start with:

    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

    any help?


     
    Thursday, May 10, 2012 1:25 PM
  • Same problem here with CE 3.5 SP2. Basically VS2010 refuses to work without SP2 installed, but SP2 has this exact problem (the ntext and image data types blah-blah-blah)
    Wednesday, August 29, 2012 6:43 AM
  • Has this been solved yet. I  am getting the error about ntext and image datatypes. It seems that the nvarchar datatype is being cast to ntext.

    I am using the sync framework, which has installed a SQL CE database locally. This is a clone of the remote server's database. The aim is to have the same functionality offline as online. The queries I use are fine as long as they are sent to the remote server but locally they do not work.

    The local database is Microsoft SQL Compact 3.5 SP2 Version 3.5.8080.0 and the hot-fixes available pre-date this version.

    The query causing the problem is:


    "select TestType, TestName, Limits 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"   

    It seems the problem is the varbinary(max) and nvarchar(max) are being cast to ntext and image datatypes.

    I have changed these to be nvarchar(4000) and varbinary(4000) but the casting still seems to occur.

    Another exception being thrown is "Large objects (ntext and image) cannot be used in ORDER BY clauses."

    Is there any solutions to these problems available? 




    • Edited by kra_m Wednesday, January 16, 2013 4:49 PM
    Monday, January 14, 2013 3:55 PM