Length Problem with Insert Statement

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

Length Problem with Insert Statement

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 06:12:18

Hi all

I'm running an insert into statement and getting the following error on the last two insert lines:

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

Now if I reduce the string size in the field it does not do this. Currently the string is about 60 long and the size for the column is 100, if i reduce the string size down to say 20 it works ok.

Why is this, the original string length is still < 100 ?

Thanks for any suggestions

G

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 06:20:47

Can you show the table structure and what you are doing in your statement?

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 06:25:34

Trailing spaces? - ignored by LEN() by considered by DATALENGTH()

Actually I don't think that is the case. SQL will silently

trim trailing spaces to make the field fit, but will not trim actual characters!


CREATE TABLE #TEMP
(
	MyTarget varchar(4)
)

DECLARE	@MyString varchar(10)
SELECT	@MyString = 'ABC   '
SELECT	LEN(@MyString), DATALENGTH(@MyString), '[' + @MyString + ']'

INSERT INTO #TEMP (MyTarget) VALUES (@MyString)

SELECT	'[' + MyTarget + ']'
FROM	#TEMP

SELECT	@MyString = 'ABC   X'
SELECT	LEN(@MyString), DATALENGTH(@MyString), '[' + @MyString + ']'

INSERT INTO #TEMP (MyTarget) VALUES (@MyString)

SELECT	'[' + MyTarget + ']'
FROM	#TEMP
GO
DROP TABLE #TEMP
GO

                                     
----------- ----------- ------------ 
3           6           [ABC   ]

------ 
[ABC ]
                                     
----------- ----------- ------------ 
7           7           [ABC   X]

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

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 06:27:48

Table Structure:

3 DiscardID int 4 0
0 Description varchar 100 0
0 Label varchar 50 0
0 AcceptanceLevel int 4 0
0 NumLabels int 4 0
0 ProductTypes varchar 100 0

Problem is with 2nd column 'description'

trying to insert like this, these are the last two rows that fail:

INSERT INTO TableA ([Description], Label, AcceptanceLevel, NumLabels, ProductTypes) VALUES ('Product processing not initiated within acceptable time limit', 'Discard', 1, 0, 'Value,ValueA,ValueAB'))
INSERT INTO TableA ([Description], Label, AcceptanceLevel, NumLabels, ProductTypes) VALUES ('Product processing not completed within acceptable time limit', 'Discard', 1, 0, 'Value,ValueA,ValueAB')

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 06:31:56

When I cut down the large string in the first column it works ok but as I said the length it is now is under 100.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 06:53:07

Anyone?

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 07:01:15

Ah I got it, someone has changed the column size in the DB I am working on to 100 but this change is not reflected in any alter table script, the size was only 50 in the other DB I am putting the values in!

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 07:01:33

"Problem is with 2nd column 'description'"

You sure its not with ProductTypes column? (or any other VARCHAR column?

Any trigger etc?

The exact error message will show you which Object the error is in - in particular check that is NOT the name of a trigger or somesuch.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 07:19:42

""Problem is with 2nd column 'description'"

You sure its not with ProductTypes column? (or any other VARCHAR column?"

No definitely the description column.

fixed now as the size was to small. I was pulling data from a table the same but the size had been increased, but not in new table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources