The ‘String or binary data would be truncated’ error will occur if an insert or update statement is trying to put too many characters into a field, defined in a table, which has been assigned too few character spaces. For example trying to write an email address with 255 characters into a table where the column email has been assigned 40 characters.
The easy fix is assign more characters to the column or columns you have determined are experiencing the problem. The more complicated but potentially necessary fix might be to change the logic or introduce validation at the source of data entry.
Finding the columns experiencing the problems however can be time consuming.
( . . . without the little script below of course)
SQL Server will kindly direct you to the stored procedure or insert/update statement that is experiencing the problem. However it will not pin point the exact column or columns that cannot be written to. The pain then is determining where the data won’t fit.
To speed things up take the entire query or query section you know to be causing the problem and write the results it into a temp table called #temp, i.e. SELECT * INTO #temp FROM SomeTable
Once the data has been written to the temp table #temp run the scrip below in the same window.
DECLARE @sql VARCHAR(MAX)
SET @sql = (
SELECT ',MAX(LEN(' + NAME + ')) AS [' + NAME + ']'
WHERE object_id = object_id('tempdb..#temp')
FOR XML PATH('')
SET @sql = 'SELECT ' + RIGHT(@sql, LEN(@sql) - 1) + ' FROM #temp'
This will output results giving you the max character length of each field.
You can then compare these results to the defined destination table that the data could not be written to.
The source of the error will be where the max character number is greater than the assigned character spaces on the destination table.
For example the last time I used this query it easily highlighted that an agent had written a customers full address to the county name field which had a limit of 30 characters.
When setting up your data sets in an SSRS report if you are using a complicated stored procedure, i.e. a SP which relies on dynamic SQL, temp tables or finishes with an IF statement, chances are the SSRS report will not be able to figure out what the SP returns. When this happens you will not be able to populate the data set with data.
This happens because the execution plan of the SSRS software isn’t smart enough and won’t be able to determine what fields the SP creates and therefore will not be able to create a means of storing the data on the report end.
Subsequently you’ll see the, often misleading, table below popup.
The solution to stop this from happening is quite simple, but considering how expensive this software is it’s a solution that shouldn’t have to be employed.
The solution is to trick the SSRS software by simplifying the SP. Basically perform a select on the specific fields you need with no additional logic or create a table with the exact fields you need with corresponding data types and select from that.
Use this dumb SP to populate the dataset in the SSRS report.
In the “Choose a data source and create a query” window as below, click refresh fields.
The software should now pick up the fields.
Change the SP back to the way it was before and do not refresh the fields again in the SSRS software and the fields should continue to populate as you need them.