I have a column in my table that is an Image (blob). The data stored
in this blob is basically pdf files. I need a query to determine the
total size of these blob's in the database. Here is what is
happening, if I run it as below I get 23736000 for a result.

select sum(datalength (cast(document AS binary(8000)))) from
plan_report

If I run it like this select sum(datalength (cast(document AS
varbinary(8000)))) from plan_report
I get 23736000 also as a result

If I run it like this select sum(datalength (cast(document AS
varbinary))) from plan_report
I get 89010 this as a result

If I run this: select datalength(document) from plan_report then I
get a result for each row and when I sum those rows I get 5584452854
this as a result.

I do not believe the first 3 queries are returning correct result sets
due to the limit on the fields (binary/varbinary) and since this is an
image field I cannot convert to anything else.....

I am out of ideas, and any help you can give me will be greatly
appreciated...

You do not have to convert your BLOB column. DATALENGTH will return the
correct size of the BLOB column if you use it directly.

Something like this:

SELECT SUM(COALESCE(DATALENGTH(document), 0))
FROM plan_report

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Connie (csawyer (AT) rwbaird (DOT) com) writes:
Because in this context the default is varbinary(30).

The obvious query would be

SELECT SUM(datalength(document)) FROM plan_report

you don't say whether you tried this, but I can see that it will fail
with integer overflow. Howeever this should work:

SELECT SUM(cast(datalength(document) as bigint)) FROM plan_report


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Good catch Erland! I didn't see the big number and the overflow...

Plamen Ratchev
http://www.SQLStudio.com

On Apr 10, 4:46 pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
Thanks everyone.......your all awesome, Yes Erland I did try the
first option, but I did not try with the bigint.....I was looking at
it too long and getting frustrated I guess, but thanks again everyone,