Cannot Insert NULL into NTEXT field in SQL CE?

Question

A simple example:

CREATE TABLE People (
      personID     int          not null
    , name         nvarchar(50)  not null
    , addrLine1    nvarchar(50) null
    , addrLine2    nvarchar(50) null
    , suburb       nvarchar(30) not null
    , xmlRawInput  ntext        not null
    , xmlRawOutput ntext        null
) 
GO


INSERT People (
      personID          
    , name              
    , addrLine1         
    , addrLine2         
    , suburb            
    , xmlRawInput       
    , xmlRawOutput
)
SELECT
      101             AS personID           
    , 'george benson'   AS name
    , '123 help st'   AS addrLine1
    , NULL            AS addrLine2          
    , 'los angeles'   AS suburb
    , '<example>record<example>' AS xmlRawInput     
    , 'I LOVE MICROSOFT' AS xmlRawOutput
GO

This works fine; Notice Null can easily be inserted into addrLine2 column; But if i change 'I Love Microsoft' to NULL on the ntext column, i get following error: The conversion is not supported. [ Type to convert from (if known) = int, Type to convert to (if known) = ntext ]

Insert fails for below; any ideas? I am using CE version 3.5.8080.0.

INSERT People (
      personID          
    , name              
    , addrLine1         
    , addrLine2         
    , suburb            
    , xmlRawInput       
    , xmlRawOutput
)
SELECT
      101             AS personID           
    , 'george benson'   AS name
    , '123 help st'   AS addrLine1
    , NULL            AS addrLine2          
    , 'los angeles'   AS suburb
    , '<example>record<example>' AS xmlRawInput     
    , NULL AS xmlRawOutput
GO

Looks like a bug - can you try on a previous version and see if it manifests itself there? — Will A, Dec 01 '10 at 04:43

score 2 · Answer 1 · answered Dec 01 '10 at 05:49

Two ideas to try:

Use a values clause instead of select clause in your insert, ie:

INSERT People (
      personID          
    , name              
    , addrLine1         
    , addrLine2         
    , suburb            
    , xmlRawInput       
    , xmlRawOutput
)
VALUES (
      101
    , 'george benson'
    , '123 help st'
    , NULL     
    , 'los angeles'
    , '<example>record<example>'
    , NULL 
)

If, for some reason, you must use the select clause (why?), try casting the NULL to ntext explicitly. ie, select convert(ntext, NULL).

If the values clause works, then the cause is probably the SqlCE engine being dumb and using the wrong default datatype for the null in the select statement. The convert may or may not work, depending on what datatype null takes on first, since technically you can't cast anything to ntext ... but it's worth trying.

Thank you. This is a problem with the uniqueidentifier type as well. — Evil Pigeon, Dec 01 '14 at 06:05

Cannot Insert NULL into NTEXT field in SQL CE?

1 Answers1