Inconsistent SQL Results

[Nielsen David]

I use an INSERT statement in my code and it works successfully the vast
majority of the time. However, some of the time it generates the error
"Conversion failed when converting the varchar value '11/23/2009' to data
type int".

The statement that generated the error listed above is:

INSERT EntityData (Entity_id, Group_id, Attr_id, Data)
VALUES (6420348, 0, 39, '11/23/2009')

The table definition is as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EntityData](
[id] [int] IDENTITY(1,1) NOT NULL,
[Entity_id] [int] NOT NULL,
[Group_id] [int] NOT NULL,
[Attr_id] [int] NOT NULL,
[Data] [varchar](8000) NULL,
[DataShort] AS (substring([data],(1),(200))) PERSISTED,
[Ts] [datetime] NOT NULL CONSTRAINT [DF_EntityData_Ts] DEFAULT (getdate()),
CONSTRAINT [PK_EntityData] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Any ideas on what may be causing the sporadic error would be GREATLY
appreciated.

--
David
The Nielsen Company

[Erland Sommarskog]

Nielsen David (Nielse...@discussions.microsoft.com) writes:
> I use an INSERT statement in my code and it works successfully the vast
> majority of the time. However, some of the time it generates the error
> "Conversion failed when converting the varchar value '11/23/2009' to data
> type int".
>
> The statement that generated the error listed above is:
>
> INSERT EntityData (Entity_id, Group_id, Attr_id, Data)
> VALUES (6420348, 0, 39, '11/23/2009')

Have you checked whether there are any triggers on the table?

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Aaron Bertrand]

I don't believe that this statement is generating that error, because I see
no reason why it would attempt to convert it to an int in this case. Is it
*possible* that sometimes your INSERT statement leaves out the column list?
That could definitely cause the problem.

I do strongly recommend using YYYYMMDD for your date literals rather than a
format like m/d/y which is subject to regional settings and session-level
settings (especially if you "sporadically" forget to leave off the single
quotes).

On 11/23/09 5:56 PM, in article
D0ADB36D-8A24-4724...@microsoft.com, "Nielsen David"

[Nielsen David]

Aaron,

I didn't see how the INSERT statement could be causing the error either.
After some digging, I discovered there was a view that included the
EntityData table. That view contained CONVERT(int, DataShort) in its SELECT
list. The clustered unique index on that view was causing the problem. The
view really didn't need that column to be an int, so I removed the CONVERT
and just with the DataShort varchar computed column and all is now well.

Thanks to all responders for your input.

--
David
The Nielsen Company

"Aaron Bertrand" wrote:

> .
>