Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot add certain integers to table Expand / Collapse
Author
Message
Posted Monday, February 7, 2011 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 5:54 AM
Points: 3, Visits: 26
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
Posted Monday, February 7, 2011 12:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 12,927, Visits: 32,330
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
Posted Monday, August 15, 2011 4:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse