SQLException : String or binary data would be truncated

Question

Hi

I have a C# code which does lot of insert statements in a batch. While executing these statements, I got "String or binary data would be truncated" error and transaction roledback.

To find out the which insert statement caused this, I need to insert one by one in the SQLServer until I hit the error.

Is there clever way to findout which statement and which field caused this issue using exception handling? (SqlException)

Thanks, Venkat

Answer 1

In general, there isn't a way to determine which particular statement caused the error. If you're running several, you could watch profiler and look at the last completed statement and see what the statement after that might be, though I have no idea if that approach is feasible for you.

In any event, one of your parameter variables (and the data inside it) is too large for the field it's trying to store data in. Check your parameter sizes against column sizes and the field(s) in question should be evident pretty quickly.

Answer 2

It depends on how you are making the Insert Calls. All as one call, or as individual calls within a transaction? If individual calls, then yes (as you iterate through the calls, catch the one that fails). If one large call, then no. SQL is processing the whole statement, so it's out of the hands of the code.

Answer 3

BEGIN TRY
    INSERT INTO YourTable (col1, col2) VALUES (@val1, @val2)
END TRY
BEGIN CATCH
    --print or insert into error log or return param or etc...
    PRINT '@val1='+ISNULL(CONVERT(varchar,@val1),'')
    PRINT '@val2='+ISNULL(CONVERT(varchar,@val2),'')
END CATCH