May 8, 2012 at 3:20 am
Iām trying to update some values into one of my table but having the following errors again and again.
Msg 8152, Level 16, State 2, Line 7
String or binary data would be truncated.
May 8, 2012 at 3:24 am
your trying to insert more than the column can hold, for example inserting a string of 100 characters into a column which can only hold 50.
i would identify which column it is and either reduce the size of the import string or increase the destination column length.
May 8, 2012 at 3:30 am
Thanks anthony.green. š
But I tried the same code in another machine then its works.
May 8, 2012 at 3:40 am
can you post the code you executed, including the DDL definitions of the objects involved in the query?
May 8, 2012 at 3:40 am
Then you have either different length of column in table at the machine it worked or you are sending small string...
did you check from query what is the length of string being inserted where it worked?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 8, 2012 at 3:43 am
Going by what you posted Anthony is absolutely right.
He told you what the Error means. We as real people can only tell you the meaning of the Error by looking at the Error Message.
If you are looking for something more then please post the DDL of your tables, some sample Data and the query that is causing the Error.
If you didn't understand what I just wrote, then please refer to the link in my signature.
Please help us in helping you.
May 8, 2012 at 3:43 am
S_Kumar_S,
I update the same table.
May 8, 2012 at 3:44 am
same table same server? same table different server?
May 8, 2012 at 4:16 am
same table same server
This is the table DDL
USE [EPAS]
GO
/****** Object: Table [dbo].[Users] Script Date: 05/08/2012 15:43:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] NOT NULL,
[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Pass] [binary](16) NULL,
[Status] [int] NOT NULL,
[Salutation] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ServiceNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Rank] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Designation] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompetentTrade] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationID] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DivisionID] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteNo] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NIC] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactNo] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address1] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address2] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address3] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DOB] [datetime] NULL,
[Gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MaritalStatus] [int] NULL,
[LastVisit] [datetime] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedUser] [int] NOT NULL,
[ModifiedDate] [datetime] NULL,
[ModifiedUser] [int] NULL,
[HostID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Users_1] PRIMARY KEY CLUSTERED
(
[UserID] ASC,
[LocationID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
And this is the sample query
update Users set ServiceNo='01234'
where UserID='10000001'
May 8, 2012 at 4:35 am
Update to this column shouldn't throw error as this is varchar(20) column and what you are updating is less than 20 chars.
By the way are you putting quotes in where UserID='10000001'
userId is int and don't need quotes
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 8, 2012 at 4:50 am
change the varchar(20) to varchar(50) and try it ...... it may solve ur problem
May 8, 2012 at 9:28 am
Check for a trigger on the table you're updating. The direct update itself doesn't look to be the issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2012 at 2:08 am
Thank you guys.
I solved the porblem.
:-D:-D
May 9, 2012 at 2:31 am
manju.ccc (5/9/2012)
Thank you guys.I solved the porblem.
:-D:-D
Care to explain what was the issue š ?
May 9, 2012 at 2:41 am
Manju There is no Problem In query or in ur Table..Its excuting fine..Please find out the below Image..
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply