Generated SQL not correct with ntext field

Andria Jensen — Fri, 28 Mar 2008 14:20:09 GMT

I am doing a save on one of my BOs which is returning the following exception message:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I have looked at the SQL which is being generated and it looks like the problem is that an equals is being used in the SQL to compare an ntext field. I am posting the SQL below. The only ntext fields in the table are Email and SendMsg. There are no text or image data types. It looks like the Email field is the one causing the problem....I have highlighted the part that I believe to be in error.

exec sp_executesql N'UPDATE [dbo].[ReportHdr] SET [ReportType] = @ReportType, [ReportDescr] = @ReportDescr, [NameKey] = @NameKey, [ReqMachine] = @ReqMachine, [ReqUser] = @ReqUser, [ReqDateTime] = @ReqDateTime, [RunMachine] = @RunMachine, [RunDateTime] = @RunDateTime, [Status] = @Status, [ErrMsg] = @ErrMsg, [ScheduleType] = @ScheduleType, [EventType] = EventType, [PeriodicInterval] = @PeriodicInterval, [PeriodicUnit] = @PeriodicUnit, [DayOfWeek] = @DayOfWeek, [WeekOfMonth] = @WeekOfMonth, [WeekOfMonthDay] = @WeekOfMonthDay, [DayOfMonthDay] = @DayOfMonthDay, [Destination] = @Destination, [SendSubject] = @SendSubject, [SendMsg] = @SendMsg, [ClientKey] = @ClientKey, [AcctExec] = @AcctExec, [CliGroup] = @CliGroup, [CliValue] = CliValue, [Office] = @Office, [DebtorKey] = @DebtorKey, [DebGroup] = @DebGroup, [DebValue] = @DebValue, [Email] = @Email, [Fax] = @Fax, [FacilityKey] = @FacilityKey, [CollateralKey] = @CollateralKey WHERE [ReportKey] = @ReportKey AND (([ReportType] = @ReportType_ORG) OR ((@ReportType_ORG IS NULL) AND ([ReportType] IS NULL))) AND (([ReportDescr] = @ReportDescr_ORG) OR ((@ReportDescr_ORG IS NULL) AND ([ReportDescr] IS NULL))) AND (([NameKey] = @NameKey_ORG) OR ((@NameKey_ORG IS NULL) AND ([NameKey] IS NULL))) AND (([ReqMachine] = @ReqMachine_ORG) OR ((@ReqMachine_ORG IS NULL) AND ([ReqMachine] IS NULL))) AND (([ReqUser] = @ReqUser_ORG) OR ((@ReqUser_ORG IS NULL) AND ([ReqUser] IS NULL))) AND (([ReqDateTime] = @ReqDateTime_ORG) OR ((@ReqDateTime_ORG IS NULL) AND ([ReqDateTime] IS NULL))) AND (([RunMachine] = @RunMachine_ORG) OR ((@RunMachine_ORG IS NULL) AND ([RunMachine] IS NULL))) AND (([RunDateTime] = @RunDateTime_ORG) OR ((@RunDateTime_ORG IS NULL) AND ([RunDateTime] IS NULL))) AND (([Status] = @Status_ORG) OR ((@Status_ORG IS NULL) AND ([Status] IS NULL))) AND (([ErrMsg] = @ErrMsg_ORG) OR ((@ErrMsg_ORG IS NULL) AND ([ErrMsg] IS NULL))) AND (([ScheduleType] = @ScheduleType_ORG) OR ((@ScheduleType_ORG IS NULL) AND ([ScheduleType] IS NULL))) AND (([EventType] = @EventType_ORG) OR ((@EventType_ORG IS NULL) AND ([EventType] IS NULL))) AND (([PeriodicInterval] = @PeriodicInterval_ORG) OR ((@PeriodicInterval_ORG IS NULL) AND ([PeriodicInterval] IS NULL))) AND (([PeriodicUnit] = @PeriodicUnit_ORG) OR ((@PeriodicUnit_ORG IS NULL) AND ([PeriodicUnit] IS NULL))) AND (([DayOfWeek] = @DayOfWeek_ORG) OR ((@DayOfWeek_ORG IS NULL) AND ([DayOfWeek] IS NULL))) AND (([WeekOfMonth] = @WeekOfMonth_ORG) OR ((@WeekOfMonth_ORG IS NULL) AND ([WeekOfMonth] IS NULL))) AND (([WeekOfMonthDay] = @WeekOfMonthDay_ORG) OR ((@WeekOfMonthDay_ORG IS NULL) AND ([WeekOfMonthDay] IS NULL))) AND (([DayOfMonthDay] = @DayOfMonthDay_ORG) OR ((@DayOfMonthDay_ORG IS NULL) AND ([DayOfMonthDay] IS NULL))) AND (([Destination] = @Destination_ORG) OR ((@Destination_ORG IS NULL) AND ([Destination] IS NULL))) AND (([SendSubject] = @SendSubject_ORG) OR ((@SendSubject_ORG IS NULL) AND ([SendSubject] IS NULL))) AND (([SendMsg] LIKE @SendMsg_ORG) OR ((@SendMsg_ORG IS NULL) AND ([SendMsg] IS NULL))) AND (([ClientKey] = @ClientKey_ORG) OR ((@ClientKey_ORG IS NULL) AND ([ClientKey] IS NULL))) AND (([AcctExec] = @AcctExec_ORG) OR ((@AcctExec_ORG IS NULL) AND ([AcctExec] IS NULL))) AND (([CliGroup] = @CliGroup_ORG) OR ((@CliGroup_ORG IS NULL) AND ([CliGroup] IS NULL))) AND (([CliValue] = @CliValue_ORG) OR ((@CliValue_ORG IS NULL) AND ([CliValue] IS NULL))) AND (([Office] = @Office_ORG) OR ((@Office_ORG IS NULL) AND ([Office] IS NULL))) AND (([DebtorKey] = @DebtorKey_ORG) OR ((@DebtorKey_ORG IS NULL) AND ([DebtorKey] IS NULL))) AND (([DebGroup] = @DebGroup_ORG) OR ((@DebGroup_ORG IS NULL) AND ([DebGroup] IS NULL))) AND (([DebValue] = @DebValue_ORG) OR ((@DebValue_ORG IS NULL) AND ([DebValue] IS NULL))) AND (([Email] = @Email_ORG) OR ((@Email_ORG IS NULL) AND ([Email] IS NULL))) AND (([Fax] = @Fax_ORG) OR ((@Fax_ORG IS NULL) AND ([Fax] IS NULL))) AND (([FacilityKey] = @FacilityKey_ORG) OR ((@FacilityKey_ORG IS NULL) AND ([FacilityKey] IS NULL))) AND (([CollateralKey] = @CollateralKey_ORG) OR ((@CollateralKey_ORG IS NULL) AND ([CollateralKey] IS NULL)));', N'@ReportType nvarchar(32),@ReportDescr nvarchar(102),@NameKey nvarchar(102),@ReqMachine nvarchar(22),@ReqUser nvarchar(14),@ReqDateTime smalldatetime,@RunMachine nvarchar(22),@RunDateTime smalldatetime,@Status smallint,@ErrMsg nvarchar(102),@ScheduleType smallint,@EventType nvarchar(22),@PeriodicInterval smallint,@PeriodicUnit smallint,@DayOfWeek nvarchar(9),@WeekOfMonth nvarchar(7),@WeekOfMonthDay smallint,@DayOfMonthDay smallint,@Destination smallint,@SendSubject nvarchar(52),@SendMsg ntext,@ClientKey int,@AcctExec nvarchar(14),@CliGroup nvarchar(12),@CliValue nvarchar(22),@Office nvarchar(22),@DebtorKey int,@DebGroup nvarchar(12),@DebValue nvarchar(22),@Email nvarchar(52),@Fax nvarchar(22),@FacilityKey int,@CollateralKey int,@ReportKey int,@ReportType_ORG nvarchar(32),@ReportDescr_ORG nvarchar(102),@NameKey_ORG nvarchar(102),@ReqMachine_ORG nvarchar(22),@ReqUser_ORG nvarchar(14),@ReqDateTime_ORG smalldatetime,@RunMachine_ORG nvarchar(22),@RunDateTime_ORG smalldatetime,@Status_ORG smallint,@ErrMsg_ORG nvarchar(102),@ScheduleType_ORG smallint,@EventType_ORG nvarchar(22),@PeriodicInterval_ORG smallint,@PeriodicUnit_ORG smallint,@DayOfWeek_ORG nvarchar(9),@WeekOfMonth_ORG nvarchar(7),@WeekOfMonthDay_ORG smallint,@DayOfMonthDay_ORG smallint,@Destination_ORG smallint,@SendSubject_ORG nvarchar(52),@SendMsg_ORG ntext,@ClientKey_ORG int,@AcctExec_ORG nvarchar(14),@CliGroup_ORG nvarchar(12),@CliValue_ORG nvarchar(22),@Office_ORG nvarchar(22),@DebtorKey_ORG int,@DebGroup_ORG nvarchar(12),@DebValue_ORG nvarchar(22),@Email_ORG nvarchar(52),@Fax_ORG nvarchar(22),@FacilityKey_ORG int,@CollateralKey_ORG int', @ReportType = N'BBS.Reports.rptPositionSummary', @ReportDescr = N'RA_Test_Template', @NameKey = N'RATESTTEMPLATE', @ReqMachine = N'BHM_RAYERS', @ReqUser = N'RAYERS', @ReqDateTime = 'Mar 26 2008 3:19PM', @RunMachine = N'BHM_RAYERS', @RunDateTime = 'Mar 26 2008 3:20PM', @Status = 0, @ErrMsg = N'', @ScheduleType = 0, @EventType = N'', @PeriodicInterval = 0, @PeriodicUnit = 0, @DayOfWeek = N'NNNNNNN', @WeekOfMonth = N'NNNNN', @WeekOfMonthDay = 0, @DayOfMonthDay = 0, @Destination = 0, @SendSubject = N'RA_Test_Template', @SendMsg = N'TEST MESSAGE', @ClientKey = 49, @AcctExec = N'RAYERS', @CliGroup = N'', @CliValue = N'', @Office = N'', @DebtorKey = 0, @DebGroup = N'', @DebValue = N'', @Email = N'rayers@baysidebiz.com', @Fax = N'5555555555', @FacilityKey = 159, @CollateralKey = 227, @ReportKey = 153, @ReportType_ORG = N'BBS.Reports.rptPositionSummary', @ReportDescr_ORG = N'RA_Test_Template', @NameKey_ORG = N'RATESTTEMPLATE', @ReqMachine_ORG = N'BHM_RAYERS', @ReqUser_ORG = N'RAYERS', @ReqDateTime_ORG = 'Mar 26 2008 3:19PM', @RunMachine_ORG = N'', @RunDateTime_ORG = 'Mar 26 2008 3:20PM', @Status_ORG = 0, @ErrMsg_ORG = N'', @ScheduleType_ORG = 0, @EventType_ORG = N'', @PeriodicInterval_ORG = 0, @PeriodicUnit_ORG = 0, @DayOfWeek_ORG = N'NNNNNNN', @WeekOfMonth_ORG = N'NNNNN', @WeekOfMonthDay_ORG = 0, @DayOfMonthDay_ORG = 0, @Destination_ORG = 0, @SendSubject_ORG = N'RA_Test_Template', @SendMsg_ORG = N'TEST MESSAGE', @ClientKey_ORG = 49, @AcctExec_ORG = N'RAYERS', @CliGroup_ORG = N'', @CliValue_ORG = N'', @Office_ORG = N'', @DebtorKey_ORG = 0, @DebGroup_ORG = N'', @DebValue_ORG = N'', @Email_ORG = N'rayers@baysidebiz.com', @Fax_ORG = N'5555555555', @FacilityKey_ORG = 159, @CollateralKey_ORG = 227

RE: Generated SQL not correct with ntext field

Trent L. Taylor — Fri, 28 Mar 2008 14:20:09 GMT

Yeah...I was about to say :)

Also, just as a side not here, you will get far better performance if you use Row Version or Time Stamp concurrency instead of All Fields.

RE: Generated SQL not correct with ntext field

Andria Jensen — Fri, 28 Mar 2008 14:12:47 GMT

Ok, sorry. I spoke way too soon. The database I am mapping to has the data type as an nvarchar, but the database I was actually running against had the type as ntext for some reason. I changed the one I was running against to be nvarchar and that fixed the problem.

StrataFrame Forum » StrataFrame Application Framework - V1 » Issues » Generated SQL not correct with ntext field

Generated SQL not correct with ntext field

RE: Generated SQL not correct with ntext field

RE: Generated SQL not correct with ntext field