Posted by coop on 12/21/05 17:54

I found a solution. I knew it had something to do with null values,
so I dropped the table involved and recreated it declaring the
fields involved with defaults of '' instead of null and that solved
the problem.

Original table structure:

CREATE TABLE [dbo].[Employee_Fingerprint] (
[Location_Code] [varchar] (8) NOT NULL ,
[Employee_Code] [varchar] (8) NOT NULL ,
[Fingerprint_Template1] [text] NOT NULL ,
[Fingerprint_Template2] [text] NULL,
[Fingerprint_Template3] [text] NULL,
[Fingerprint_Template4] [text] NULL
) ON [PRIMARY]
GO

This caused operand type clash on MSDE when trying to insert a record,
but worked fine on SQL2000 and SQL2005

Restructuring the table like this:
CREATE TABLE [dbo].[Employee_Fingerprint] (
[Location_Code] [varchar] (8) NOT NULL ,
[Employee_Code] [varchar] (8) NOT NULL ,
[Fingerprint_Template1] [text] NOT NULL ,
[Fingerprint_Template2] [text] DEFAULT '',
[Fingerprint_Template3] [text] DEFAULT '' ,
[Fingerprint_Template4] [text] DEFAULT ''
) ON [PRIMARY]
GO

solves the problem.

If any of you SQL gurus knows why, I'd really appreciate hearing
from you. It's been driving me nuts.

[Back to original message]