Cannot add certain integers to table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » Cannot add certain integers to table

Cannot add certain integers to table

Rate Topic

Display Mode

Topic Options

Author

Message

mcinwg

Posted Monday, February 07, 2011 12:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 15, 2011 5:04 AM
Points: 3, Visits: 25

I have a table :

CREATE TABLE [dbo].[haEmails] (
[emailRef] [int] IDENTITY (1, 1) NOT NULL ,
[Sent] [bit] NOT NULL ,
[FromUserNum] [int] NOT NULL ,
[ToUserNum] [int] NOT NULL ,
[MessageType] [int] NOT NULL ,
[AddedAt] [datetime] NOT NULL ,
[SentAt] [datetime] NULL ,
[MessageText] [varchar] (600) NULL ,
[Version20091013] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[haEmails] WITH NOCHECK ADD
CONSTRAINT [PK_TestEmails] PRIMARY KEY CLUSTERED
(
[emailRef]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[haEmails] ADD
CONSTRAINT [DF_TestEmails_Sent] DEFAULT (0) FOR [Sent],
CONSTRAINT [DF_TestEmails_MessType] DEFAULT ((-1)) FOR [MessageType],
CONSTRAINT [DF_TestEmails_AddedAt] DEFAULT (getdate()) FOR [AddedAt],
CONSTRAINT [DF_haEmails_Version20091013] DEFAULT (0) FOR [Version20091013]
GO

My application wasn't always responding to an INSERT trigger on the table, and my investigations found that :

In Enterprise Manager, adding a new row :
haEmails(FromUserNum,ToUserNum,MessageType)
Values (22,11,-1)
fails, with message "string or binary data would be truncated".

But, Values (22,11,1) is successful.
In the range 1..250 I only get this behaviour for FromUserNum = 22 or 74 or 97, all other FromUserNum integers allow a negative MessageType value.

The criteria for failure appear to be :
(FromUserNum = 22 OR FromUserNum = 74 OR FromUserNum = 97)
AND MessageType < 0

Can anyone tell me what's going on and how to avoid the problem ?

Thanks in anticipation.
Mike C

Post #1059822

Lowell

Posted Monday, February 07, 2011 12:42 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

the error "string or binary data would be truncated". is specific to char/varchar fields...it doesn't have anything to do with integer fields.

since the command you are using doesn't even touch the sole varchar(600) column in your definition, I'd look to see if there are any triggers on the table...maybe there is a trigger that is updating something and it is failing.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1059855

jimmylee150

Posted Monday, August 15, 2011 4:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 15, 2011 4:13 AM
Points: 1, Visits: 3

sdsafdf

acheter viagra

Post #1159869

« Prev Topic | Next Topic »

Permissions