posted 5/9/2011 by SMcDonald - Views: [2492]
Let’s just say that you are responsible for managing the company reports. You may or may not have created the reports, but none-the-less, you own them. One day you get a call from somebody in sales and they tell you that one of the reports is broken.
Ok, time to trouble shoot. You access the report through BIDS or the Report Manager. When you preview the report that’s displayed says that, “String or Binary data would be truncated. The statement has been terminated.”
The data set is a stored procedure. So the first thing you do is go to SSMS (Sql Server Management Studio) to execute the stored proc to see if it runs. Here is the result set:
Same error! Well, this is the error message that I received. The way I started trouble shooting this was through process of elimination. I took my query and commented out everything except the Select and From. Once I executed the query, I received the same error message.
I looked at the data types and number of characters that my column was set to have. I then compared that the parameter values that I was passing in. I realized that my one of my parameters was set to have a Varchar 50 and my column was expecting a Varchar 80. As you would expect, I changed the number of characters for my parameter, altered and executed the stored proc and I had results returned.
So the next time you, receive that type of error message, confirm that number of characters you are trying to bring back is what your column will allow.
I frequently run into this one in the early stages of setting up new staging tables where schemas are going through regular revision before going into production. Isolate everything to your "VAR" data types and start comparing lenghts. Definitely a pain with a report in production, but even that happens when there are schema changes.