Yesterday I was getting a very interesting error in SSRS. A user was trying to export data to Excel and it was basically telling her that it was too much data to fit into Excel. I began to suspect that one of the fields in the report was quite large. I went to run a standard T-SQL query to see exactly how large the field in question was. The field was named [Description] and it's type was text:
USE [MyDatabase]
SELECT LEN([Description])
FROM MyTable
Of course, you know what happened:
Msg 8116, Level 16, State 1, Line 3
Argument data type text is invalid for argument 1 of len function.
So, the LEN() function cannot be used on text type fields. Was there a LEN() equivalent for the text type? A little googling revealed that there was:
DATALENGTH()
This function was exactly what I needed to get the length for a text type field.
