String or binary data would be truncated - Integers?

Question

On SQL server 2008 on Windows Server 2008 I have the query below and on execution I get the old 'String or binary data would be truncated'.

BUT, the really weird thing here is that if I change the last fields value (isOrganiser) to between 2 and 9, it runs fine. Same thing if I change 114841 to 114741 (and that pattern continues). So the issue is being caused by integer fields? Or bit fields? What?

So I'm stumped. Few bits of extra info:

Some of the int columns have indexes - I have disabled them all and I get the same issue.
This query runs all over the place in this an other applications and have never had an issue - only different here is perhaps the quantity of records
Although the ID's are FK's, the constraints are not enforced in SQL

This table has an ID column (100001 seed and +1 increment) - each time the execution errors, there is no record inserted, but the ID of the next record that does insert is incremented as if the previous error has inserted

INSERT INTO [emailRecipient] ( [recordTitle], [createdByID], [canUpdate], [canDelete], [isDeleted], [canRemove], [drsQuickSummary], [drsSummary], [isEditLocked], [sourceRID], [insertIndex], [isSaved], [allowNextInsertTrigger], [allowNextUpdateTrigger], [allowNextDeleteTrigger], [emailRID], [personID], [organisationID], [emailAddressID], [emailAddress], [isOrganiser] ) VALUES ( '', 100002, 1, 1, 0, 0, '', '', 0, '', 1, 1, 1, 0, 0, 100753, 102969, 114841, 102434, '[email protected]', 0 );

Is there a trigger on the table? What is the datatype of emailAddress (including length), and what about isOrganiser? What is the purpose of the allowNext...Trigger columns? Does this table have nested triggers too? Isn't it possible this error is happening somewhere in these triggers? — Aaron Bertrand
– Aaron Bertrand, Commented Mar 19, 2013 at 0:57
@Aaron Bertrand you were right. It was occurring on a trigger on a parent table 5 triggers away. Can't believe I didn't think to follow it down the trail. Thanks so much for the pointer! Also, I'm still quite new here - What's the correct etiquette for showing this as answered but attributing it to you? — John James
– John James, Commented Mar 19, 2013 at 12:10
I've posted my guess as an answer. Glad I was right and that it helped. How are you trapping the error? The error message might include ERROR_PROCEDURE which - in spite of its name - will tell you which trigger the error occurred in. — Aaron Bertrand
– Aaron Bertrand, Commented Mar 19, 2013 at 12:57

Aaron Bertrand · Accepted Answer · 2013-03-19 12:56:32Z

I suspect the truncation is happening in one of your triggers.

Oh, and 5 triggers? I suspect there is some optimization that could happen here. :-)

Collectives™ on Stack Overflow

String or binary data would be truncated - Integers?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related