Resolving "String or binary data would be truncated" error when provided line is inaccurate

Question

I am running SQL Server 2017 using SQL Server Management Studio.

The details I can give about my error are that it reads as follows:

Failed to load data into -- Database due to : String or binary data would be truncated. :Procedure ~~ : Line #

The "Procedure ~~" is approx. 5000 lines of code consisting of a large number of statements that insert data into various tables.

The line # specified is within one of the many insert statements, but due to the checking I have done thus far is not the actual cause of the error.

Since the Line # specified was within one of many insert statements, my first goal was to check that line.

• The information in that line is selected from a table where it is a stored in a varChar(2)
• The information is being inserted into a table where it will be stored as a varChar(3)
• In the specific case that initially caused the error as well as cases I am testing, the value would be 2 characters long

My first attempted fix was to try and use the LEFT( , ) function to truncate the information in case there is somehow a change in the value I am not seeing. I tried both LEFT( , 3) and LEFT( , 2), but neither removed the error.

The second thing I checked was in case the Line # in the error message was off by a line or two, I double checked the other bits of information handled by this insert statement. This information in its entirety was of the proper length for the table it was being inserted into and was also already being modified by the LEFT( , ) function in case it was not naturally of the proper length.

I am currently attempting to remove various insert statements within procedure ~~. There were multiple conditions in which the procedure might insert into the same table as it was during the Line # incident.

I first went through the procedure and removed these similar insert statements one-by-one and attempted to run the code again. The error continued to appear.

I then went through the procedure and attempted remove other insert statements which were present in procedure ~~ in bulk. For example, removing all instances of "Insert into Foobar" and testing, then removing all instances of "Insert into Boofar" and testing.

While I have not run through each of the possible tables yet, as processing the request takes some time, I am growing uncertain this method is the best use of my time. The insert statements I have tried removing so far have not resulted in the error being removed.

• Should my current process not result in finding the error, what do I try next?
• Are there other reasons this "String or binary data would be truncated" error may appear besides the values I am attempting to insert being too long for the location I am attempting to insert them into?
• How can I resolve this issue?

Answer 1

RE: "I am currently attempting to remove various insert statements within procedure ~~. There were multiple conditions in which the procedure might insert into the same table as it was during the Line # incident."

Since you are able to modify code in your SP and re-run it. Try running statements inside the body of your SP one by one i.e. copy paste the body of your SP into a new query window in SSMS, while declaring all arguments as variables and initializing them to the right values.

RE: "before me used print statements" - provided you have print statements that are unique e.g. "executing insert stament X" and you have a lot of them, why not run your SP manually from a query window and see what was the last print statement text shown before the error. This should help you narrow down the line number.