MSG 8152 LEVEL 16 State 9 - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

MSG 8152 LEVEL 16 State 9

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-29 : 09:36:22

I am trying this update query to update the table(Allfile) with a daily data feed we get. Both Table structures are identical. I get the following error:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary would be truncated.
The statement has been terminated.

Here is my update statement
update allfile
set
allfile.Det_coll=allfile_up.Det_coll,
allfile.[File]=allfile_up.[File],
allfile.Unit_coll=allfile_up.Unit_coll,
allfile.Date_updated=allfile_up.Date_updated,
allfile.ORI=allfile_up.ORI,
allfile.OSR_code=allfile_up.OSR_code,
allfile.Details=allfile_up.Details,
allfile.Date_open=allfile_up.Date_open,
allfile.Ass_coll=allfile_up.Ass_coll,
allfile.Jur_coll=allfile_up.Jur_coll,
allfile.Status=allfile_up.Jur_coll,
allfile.Member_out=allfile_up.Member_out,
allfile.Diary_date=allfile_up.Diary_date,
allfile.Date_occured_from=allfile_up.Date_occured_from,
allfile.Time_occured_from=allfile_up.Time_occured_from,
allfile.Date_occured_to=allfile_up.Date_occured_to,
allfile.Time_occured_to=allfile_up.Time_occured_to,
allfile.Restriction=allfile_up.Restriction,
allfile.Location=allfile_up.Location,
allfile.Extract_date=allfile_up.Extract_date
from
allfile join allfile_up as allfile_up on allfile.[file]=allfile_up.[file] and allfile.det_coll=allfile_up.det_coll and allfile.unit_coll=allfile_up.unit_coll
where exists
(Select * from allfile as W where allfile.[file]=W.[file] and allfile.det_coll=W.det_coll and allfile.unit_coll=W.unit_coll)

Here is the structure for allfile:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_FILECOUNT_FACT_ALLFILE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[FILECOUNT_FACT] DROP CONSTRAINT FK_FILECOUNT_FACT_ALLFILE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ALLFILE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ALLFILE]
GO

CREATE TABLE [dbo].[ALLFILE] (
[FILE_KY] [int] IDENTITY (1, 1) NOT NULL ,
[Det_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[File] [numeric](18, 0) NULL ,
[Unit_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORI] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSR_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Details] [char] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_open] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ass_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Jur_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Member_out] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Diary_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_occured_from] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time_occured_from] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_occured_to] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time_occured_to] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Restriction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extract_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[count] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

Here is the structre for allfile_up:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ALLFILE_up]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ALLFILE_up]
GO

CREATE TABLE [dbo].[ALLFILE_up] (
[Det_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[File] [numeric](18, 0) NULL ,
[Unit_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORI] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSR_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Details] [char] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_open] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ass_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Jur_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Member_out] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Diary_date] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_occured_from] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time_occured_from] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_occured_to] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time_occured_to] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Restriction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extract_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[count] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-29 : 09:57:43

The error message means that you are trying to update a char or varchar type column with data from another column that would be too wide to fit.

Compare the data structures on your source and destination columns to make sure all the entries will fit.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources