Cannot Insert NULL into NTEXT field in SQL CE? -
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 personid , 'george benson' name , '123 st' addrline1 , null addrline2 , 'los angeles' suburb , '<example>record<example>' xmlrawinput , 'i love microsoft' xmlrawoutput go this works fine; notice null can inserted addrline2 column; if change 'i love microsoft' null on ntext column, following error: conversion not supported. [ type convert (if known) = int, type convert (if known) = ntext ]
insert fails below; ideas? using ce version 3.5.8080.0.
insert people ( personid , name , addrline1 , addrline2 , suburb , xmlrawinput , xmlrawoutput ) select 101 personid , 'george benson' name , '123 st' addrline1 , null addrline2 , 'los angeles' suburb , '<example>record<example>' xmlrawinput , null xmlrawoutput go
two ideas try:
use values clause instead of select clause in insert, ie:
insert people ( personid , name , addrline1 , addrline2 , suburb , xmlrawinput , xmlrawoutput ) values ( 101 , 'george benson' , '123 st' , null , 'los angeles' , '<example>record<example>' , null ) if, reason, must use select clause (why?), try casting null ntext explicitly. ie, select convert(ntext, null).
if values clause works, cause sqlce engine being dumb , using wrong default datatype null in select statement. convert may or may not work, depending on datatype null takes on first, since technically can't cast ntext ... it's worth trying.
Comments
Post a Comment