Getting size of blob in MSSQL

Hi all,

Does anyone have any clues, hints, suggestions, or code scraps that will retrieve the size of a blob field in Microsoft SQL Server 2000? We use ASP, ASPX, JavaScript, HTML, SQL. We are hitting a 4MB threshold somewhere, and we are trying to determine if it is in the population process or the retrieval process.

Many thank you’s in advance.

To find the length of a field

select Len(field) from table

the 4 MB limit is more likely coming from ADO than from mssql, qhich can store up to 2 GB in one of these fields

Oh, I see, you want to know the length of the data, not the field size…


select datalength(field) from table
where PK = Expression


Fan(blooming)tastic! I could not find that datalength documented anywhere. Now I know the name of the function I can read up on it in SQL Server Books Online.

Thank you very very very very very very very much. This is going to save me a lot of headache in dealing with our SQL CDBA’s.