operand type clash

I have a stored procedure that works fine on SQL Server 2000 or 2005,
but when run on an machine running MSDE, I get the Operand type clash,
int is incompatible with text data type error.  The data type is
actually text and text is the type of data being passed.  Any
parameters not being used are defaulted to null.  Any ideas why this
would happen on MSDE and work fine anywhere else?

Thanks so much.

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].& #91;Employee_Fingerp
rint] (
[Location_Code] [varchar] (8) NOT NULL ,
[Employee_Code] [varchar] (8) NOT NULL ,
& #91;Fingerprint_Temp
late1] [text]  NOT NULL ,
& #91;Fingerprint_Temp
late2] [text]  NULL,
& #91;Fingerprint_Temp
late3] [text]  NULL,
& #91;Fingerprint_Temp
late4] [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].& #91;Employee_Fingerp
rint] (
[Location_Code] [varchar] (8) NOT NULL ,
[Employee_Code] [varchar] (8) NOT NULL ,
& #91;Fingerprint_Temp
late1] [text]  NOT NULL ,
& #91;Fingerprint_Temp
late2] [text]  DEFAULT '',
& #91;Fingerprint_Temp
late3] [text]  DEFAULT '' ,
& #91;Fingerprint_Temp
late4] [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.

coop (carol.cooper@intura.com)  writes:
> 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].& #91;Employee_Fingerp
rint] (
>      [Location_Code] [varchar] (8) NOT NULL ,
>      [Employee_Code] [varchar] (8) NOT NULL ,
>      & #91;Fingerprint_Temp
late1] [text]  NOT NULL ,
>      & #91;Fingerprint_Temp
late2] [text]  NULL,
>      & #91;Fingerprint_Temp
late3] [text]  NULL,
>      & #91;Fingerprint_Temp
late4] [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].& #91;Employee_Fingerp
rint] (
>      [Location_Code] [varchar] (8) NOT NULL ,
>      [Employee_Code] [varchar] (8) NOT NULL ,
>      & #91;Fingerprint_Temp
late1] [text]  NOT NULL ,
>      & #91;Fingerprint_Temp
late2] [text]  DEFAULT '',
>      & #91;Fingerprint_Temp
late3] [text]  DEFAULT '' ,
>      & #91;Fingerprint_Temp
late4] [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.

Maybe if you post the offending code, we might be able to think of
an explanation.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx