Msg 8152, Level 16 Error

Question

Msg 8152, Level 16 Error

luis.gomes.henriques

Old Hand

Points: 343
More actions
September 28, 2010 at 8:48 am

#148906

Hi there!
I'm a rookie at SQL language!
I'm trying to insert info this way:
INSERT [CLH_ERROSRETORNO] ([TipoFicheiroId], [Codigo], [Descricao], [EstadoRegisto], [DataCriacao], [UtilizadorCriacao], [DataAlteracao], [UtilizadorAlteracao]) VALUES (N'4', N' 0002', N'teste', N'A', N'28-09-2010', N'F083059', N'28-09-2010', N'F083059')
And sql returned this:
Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
Any help would be appreciatted!
Regards,

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

One of the values you are trying to insert is too large for the column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

luis.gomes.henriques Old Hand Points: 343 More actions · Answer 2

GilaMonster (9/28/2010)
One of the values you are trying to insert is too large for the column.

I analysed the table and the respective column and can't find the problem.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

What's the definition of the table CLH_ERROSRETORNO? (Post the CREATE TABLE statement)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

luis.gomes.henriques Old Hand Points: 343 More actions · Answer 4

GilaMonster (9/29/2010)
What's the definition of the table CLH_ERROSRETORNO? (Post the CREATE TABLE statement)

As requested.

USE [Colheitas]

GO

/****** Object: Table [dbo].[CLH_ERROSRETORNO] Script Date: 09/29/2010 14:37:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[CLH_ERROSRETORNO](

[Id] [int] IDENTITY(1,1) NOT NULL,

[TipoFicheiroId] [int] NOT NULL,

[Codigo] [varchar](4) NOT NULL,

[Descricao] [varchar](150) NOT NULL,

[EstadoRegisto] [char](1) NOT NULL,

[DataCriacao] [datetime] NOT NULL,

[UtilizadorCriacao] [varchar](50) NOT NULL,

[DataAlteracao] [datetime] NOT NULL,

[UtilizadorAlteracao] [varchar](50) NOT NULL,

CONSTRAINT [PK_CLH_ERROSRETORNO] PRIMARY KEY CLUSTERED

(

[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

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[CLH_ERROSRETORNO] WITH CHECK ADD CONSTRAINT [FK_CLH_ERROSRETORNO_CLH_TIPOFICHEIRO] FOREIGN KEY([TipoFicheiroId])

REFERENCES [dbo].[CLH_TIPOFICHEIRO] ([Id])

GO

ALTER TABLE [dbo].[CLH_ERROSRETORNO] CHECK CONSTRAINT [FK_CLH_ERROSRETORNO_CLH_TIPOFICHEIRO]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

The problem is with the column [Codigo]. It's 4 characters long but your insert has a 5 character value specified for it (N' 0002'). There's a leading space that makes that 5 characters, not 4.

In addition, the insert defines all values as unicode strings, but there's no unicode columns in the table. Can you remove the N prefixes?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

luis.gomes.henriques Old Hand Points: 343 More actions · Answer 6

GilaMonster (9/29/2010)
The problem is with the column [Codigo]. It's 4 characters long but your insert has a 5 character value specified for it (N' 0002'). There's a leading space that makes that 5 characters, not 4.
In addition, the insert defines all values as unicode strings, but there's no unicode columns in the table. Can you remove the N prefixes?

Thanks! Now i have another kind of error

Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type datetime.

The statement has been terminated.

WayneS SSC Guru Points: 95461 More actions · Answer 7

Either you have an invalid date specification in the insert, or what is there is a different format than what sql is expecting. I see that your date is in day-month-year format; you might need to perform a "SET DATEFORMAT DMY;" first.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

That or change the dates so that the format is unambiguous. yyyy-mm-dd works well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

luis.gomes.henriques Old Hand Points: 343 More actions · Answer 9

luis.gomes.henriques

Old Hand

Points: 343

September 30, 2010 at 7:50 am

#1229628

Thanks!

I't worked like a charm!