SQL SERVER error 80040e57

_{Tuesday, April 16, 2013 6:55 AM}

Morning,

I'm having issues with the above error code that I know means that the 'String or binary data would be truncated', the issue I'm having is that I have got a SP that fires as part of a loop in a VBS when processing orders, at least once a day the code will fail with the error code 80040e57, I will then take the SP from the VBS script into SQL Server Management Studio and fire it with the same order number and it works first time?!

Any ideas that could shed some light on this it would be great.

Andy

_{Tuesday, April 16, 2013 9:49 AM ✅Answered | 1 vote}

Hello Andy,

This error occurs, when you try to insert longer values then the column can contain per Definition. E.g. if the column is defined as varchar(5) and you try to insert 6 chars, you get the error. You have to take care, that you don't insert longer values then allowed, see sample below:

CREATE TABLE #test(value varchar(5));
GO

-- This works: Inserting only 5 chars:
INSERT INTO #test VALUES ('12345');
GO

-- This fails with the same error message
-- String or binary data would be truncated.
-- because it's tried to insert 6 char, where only 5 chars are allowed
INSERT INTO #test VALUES ('123456');
GO

-- Workaround: Truncate the max size manually
INSERT INTO #test VALUES (LEFT('1234567890', 5));
GO

DROP TABLE #test;

Olaf Helper

Blog Xing

_{Tuesday, April 16, 2013 2:01 PM | 1 vote}

Hello,

What I don't understand is why I get that error when the SP its fired via the VBS script but when I fire it for the same order directly in Management Studio I don't?

What would cause this difference?

Andy

_{Tuesday, April 23, 2013 9:17 AM}

80040e57 is not an SQL Server error, but this is an error code that VBscript sets, and this is unrelated to the SQL Server error codes.

When you run the procedure in SSMS, make sure that you run this command first:

SET ANSI_WARNINGS ON

This command should be on by default, unless you have meddled with the settings in SSMS. But when ANSI_WARNINGS are off, you don't get the errors about string truncation.

Do you actually see the error message "String of binary data would be truncated". If not, you need to investigate what is the actual error message, and that the cause is actually an error message from SQL Server, so that you are not looking in the wrong place.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se