Wednesday, November 14, 2012

The DATALENGTH() Function

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.



No comments:

Post a Comment