Bizarre error on int data type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Bizarre error on int data type

Author

Topic

gyclone
Starting Member

5 Posts

Posted - 2009-07-27 : 02:32:00

Greetings,

I have three columns in a table that are all set to the "int" data type. When I try to enter any number, even the number 1, I receive the following error:

No row was updated
The data in row 1 was not committed.
Error source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).

I have tried to enter data both by using Insert statements and directly in the data table. I have changed the data type to bigint and get the same error.

If anybody has any idea what might be causing this, and more importantly, how to get around the problem, I'd greatly appreciate some assistance. I've never seen this problem before.

I'm using SQLServer 2005 Express w/ Management Studio Express

Thanks!

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 02:48:48

Hi

Can you post the table structure and Data which you tried to insert...

-------------------------
R..

gyclone
Starting Member

5 Posts

Posted - 2009-07-27 : 03:03:09

Here is the code for the table:

USE [CER]
GO
/****** Object: Table [dbo].[equipment] Script Date: 07/26/2009 23:56:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[equipment](
[equipment_id] [int] NOT NULL,
[menu_id] [int] NULL,
[type_id] [int] NULL,
[Description] [nvarchar](100) NULL,
[functional_location] [nvarchar](100) NULL,
[physical_location] [nvarchar](100) NULL,
[manufacturer] [nvarchar](50) NULL,
[model_name] [nvarchar](50) NULL,
[model_number] [nvarchar](50) NULL,
[month_manufactured] [nchar](2) NULL,
[year_manufactured] [nchar](4) NULL,
[serial_number] [nvarchar](100) NULL,
[day_installed] [nchar](2) NULL,
[month_installed] [nchar](2) NULL CONSTRAINT [DF_equipment_date_installed] DEFAULT (N'Unknown'),
[year_installed] [nchar](4) NULL,
[image] [nvarchar](50) NULL,
CONSTRAINT [PK_equipment] PRIMARY KEY CLUSTERED
(
[equipment_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
ALTER TABLE [dbo].[equipment] WITH CHECK ADD CONSTRAINT [FK_equipment_equipment_type] FOREIGN KEY([type_id])
REFERENCES [dbo].[equipment_type] ([type_id])
GO
ALTER TABLE [dbo].[equipment] CHECK CONSTRAINT [FK_equipment_equipment_type]
GO
ALTER TABLE [dbo].[equipment] WITH CHECK ADD CONSTRAINT [FK_equipment_menu] FOREIGN KEY([menu_id])
REFERENCES [dbo].[menu] ([menu_id])
GO
ALTER TABLE [dbo].[equipment] CHECK CONSTRAINT [FK_equipment_menu]

I only tried to enter one record before I got the error. Even when I only try to enter the number 1 in the "equipment_id" column, I still get the error.

I would just rebuild the table to see if that would solve the problem, but the primary key for this table is a foreign key for almost every other table in my database and I don't want to have to redefine all those relationships.

Thanks!!

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 03:42:15

Hi

This error message appears when you try to insert a string with more characters than the column can maximal accommodate.

Can you check the total length of data which u have passed is not more then the column length in table which you have assign..

-------------------------
R..

gyclone
Starting Member

5 Posts

Posted - 2009-07-27 : 04:00:30

Thanks for your replies! The column [equipment_id] is set for data type "int" (integer). I get the error when I insert any number, even the number 1. Unless I'm mistaken, the int data type should accept any number between -2,147,483,648 and 2,147,483,647. Therefore, the number 1 should be an acceptable entry.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 04:07:56

Hi

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

-------------------------
R..

gyclone
Starting Member

5 Posts

Posted - 2009-07-27 : 04:13:33

Exactly, so 1 should not cause an error. Right?

mhorseman
Starting Member

44 Posts

Posted - 2009-07-27 : 04:14:38

Hi,

I think you need to change the length of your month_installed column. "Unknown" won't fit your defined length of 2. Change it to 7 (or more, depending upon what other values you want).

Thanks

Mark

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 04:19:37

Hi
when you try to insert a string with more characters than the column can maximal accommodate. check all the column length..

[month_installed] [nchar](7) NULL CONSTRAINT [DF_equipment_date_installed] DEFAULT (N'Unknown'),

-------------------------
R..

gyclone
Starting Member

5 Posts

Posted - 2009-07-27 : 04:36:33

Thanks, it does appear to have been the month_installed column that was causing the problem. The really strange thing is that I didn't add that unknown default, I have no idea how it got there. Once I edited that column, the problem went away. I should have caught that, but I'm a little tired.

Thank you both for your help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources