Posted by Connie on 04/10/07 19:46

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...

• Next in forum: Re: Stored procedure returns duplicates
• Prev in forum: Re: Retrieve ALL tables from ALL Databases
• Thread view: Sum of Column of Blob type

[Reply to this message]