-1

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?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jatwork
  • 1
  • Is your code generating inserts that are not obvious? For example an error log table insert or a trigger that's inserting into another table. Check the sql server error logs. Also, see if you can run your procedure in debugger. – user2773289 Aug 07 '23 at 15:49
  • @user2773289 from what I can tell, @@Error values are returned and debugging that has been done before me used print statements. There is a table which stores when this procedure was run, but as for information related to the success/failure, it just stores whether it was successful or the error message given. In this case the error message stored is the 'string or binary data would be truncated' error with the formatting I outlined in the post. – jatwork Aug 07 '23 at 16:09
  • 2
    Not sure if it will work for you but you can try a "binary search". Run the first half of your code. If you see the error it is in that half, if you don't its in the other half. Then run the first half of the half with the error. Now you have narrowed the "location" of the error down to 25% of your code. With a few more iterations, you can get it down to 12.5% or 6.25% or even ~3%, at which point you have much less code to look at. As far as I can recall, this error does occur (as advertised) when a string (or binary data) is too long - so that is what you are looking for. – topsail Aug 07 '23 at 18:13
  • If you're convinced that the insert statements in the stored procedure are correct... do any of the target tables have insert triggers on them that could actually be the source of the warning message? – AlwaysLearning Aug 07 '23 at 22:00
  • 1
    Aside... the line numbers in the warning should match with the first line of each delete/insert/select/update statement, where line 1 is the first line of the batch that created the stored procedure. i.e.: the line numbering includes any lines of comments you might include before the `create or alter procedure` line. – AlwaysLearning Aug 07 '23 at 22:05
  • I suggest that you add a logging table and insert log lines at each point of the stored procedure. This will not only help you find the current error, but also help find errors in future and also give you some clues about performance (assuming your log has a timestamp). It's a bit laborious the first time but it's well worth it. Alternatively you could use `sp_whoisactive` to sniff out what queries the proc is running in real time and get some idea of what the proc has successfully run. These two steps will at least let you be 100% on which bit of code is causing the error – Nick.Mc Aug 08 '23 at 09:46
  • 1
    This is such a painfully non specific error that MS have released an enhancement to show the data.... this may not be an option for you but have a read of this https://www.sqlservercentral.com/articles/getting-more-information-from-the-string-or-binary-data-truncated-error – Nick.Mc Aug 08 '23 at 09:48

1 Answers1

0

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.

Alex
  • 4,885
  • 3
  • 19
  • 39