MysteriouString or binary data would be truncated

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

SQL Server Development (2000)

MysteriouString or binary data would be truncated

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2004-03-16 : 04:02:08

Hi,

Msg 8152, Level 16, State 1, Procedure CspxxLoadIndividualNondupes, line 1004
String or binary data would be truncated
The statement has been terminated

when running a SP I got the above message. The Sp has only one insert statement which is inserting one string column. to a varchar (255) column on the table. The maximum lentgh of the data being inserted is 6 chareters. The varchar column on the table was modified a while ago to 255 characters. But still I am getting the string truncation error. Can anyone tell me if I have to recompile the execution plan for the SP to pick up the new schema changes. I am running the SP directly throughy Query Analyzer. The server is patched to Patch 2 SQL Server 2000.

Thanks in advance!!!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-16 : 04:05:04

That's a bloomin' massive procedure! What's the code on line 1004?

Are you sure the data is clean? Try running this:

SELECT MAX(LEN(myVarcharColumn)) FROM SourceTable

OS

miranwar
Posting Yak Master

125 Posts

Posted - 2004-03-16 : 04:22:52

The data was inserted into the source table via BCP

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-16 : 05:16:30

[code]
SET NOCOUNT ON
CREATE TABLE #test(col1 VARCHAR(2))
GO
INSERT INTO #test(col1) VALUES('15')
INSERT INTO #test(col1) VALUES(15)
INSERT INTO #test(col1) VALUES('157')
INSERT INTO #test(col1) VALUES(157)
SET NOCOUNT OFF
SELECT * FROM #test
DROP TABLE #test

Server: Nachr.-Nr. 8152, Schweregrad 16, Status 9, Zeile 5
Zeichenfolgen- oder Binärdaten werden abgeschnitten.
Die Anweisung wurde beendet.
col1
----
15
15
*

(3 row(s) affected)
[/code]
SQL Server converts data (and truncates, if necessary) where appropriate. When converting to a VARCHAR (explicitely or implicitely) and the value is too large for that column, SQL Server will insert *. This is explained in BOL under CAST and CONVERT.
So I would second mohdowais and suspect some problems with your raw data.
Also I would suggest applying the latest service pack

--Frank
http://www.insidesql.de

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-16 : 05:23:48

I would imagine that :Server: Nachr.-Nr. 8152, Schweregrad 16, Status 9, Zeile 5
Zeichenfolgen- oder Binärdaten werden abgeschnitten.
Die Anweisung wurde beendet.

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

in German?

Duane.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-16 : 05:53:42

Not bad...I was hoping the code *speaks* for itself. Yes, it's german. I have a completely mixed installation here. English Server version, english BOL, german client tools and german BOL. That causes sometimes very strange results.

--Frank
http://www.insidesql.de

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources