SQL File Attachment Size different from Windows Explore size

Question

SQL File Attachment Size different from Windows Explore size

rkelly58

SSCrazy

Points: 2151
More actions
May 24, 2018 at 9:48 am

#341542

Hi,
Anyone help:
My table has a File column (nvarcharmax) which is used on a form to upload files i.e. PDF
I need to retrieve the actual file size to use for calculating the file size of all attachments before the user can submit the form.
The PDF i used is 5.19 MB when i do Windows properties
In SQL, i ran a query on the column as follows:
TH([FILE]) AS SizeInBytes, LEN([File]) AS NumberOfCharacters FROM [MyDB].[dbo].[File_Requests]
The SizeinBytes is showing as 14519304
If i use an online converter to change Bytes to MB it's 13.846687316895
Any ideas what i;m doing wrong? The form technology won't show me the file size, hence me trying to get it from SQL
Any way of doing this?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

rkelly58 - Thursday, May 24, 2018 9:48 AM
Hi,
Anyone help:
My table has a File column (nvarcharmax) which is used on a form to upload files i.e. PDF
I need to retrieve the actual file size to use for calculating the file size of all attachments before the user can submit the form.
The PDF i used is 5.19 MB when i do Windows properties
In SQL, i ran a query on the column as follows:
TH([FILE]) AS SizeInBytes, LEN([File]) AS NumberOfCharacters FROM [MyDB].[dbo].[File_Requests]
The SizeinBytes is showing as 14519304
If i use an online converter to change Bytes to MB it's 13.846687316895
Any ideas what i;m doing wrong? The form technology won't show me the file size, hence me trying to get it from SQL
Any way of doing this?

Shouldn't you be using VARBINARY(MAX) for storing files?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736632

May 24, 2018 at 10:37 am

#1991854

What does Datalength() show?

rkelly58 SSCrazy Points: 2151 More actions · Answer 3

Hi,

Thanks for the replies.

@SSC Guru - i'm using K2 Smartforms, their is a File attachment control which gets created as a column type of File in the SQL table (its called a smartobject in K2). This translates to nvarchar(max) when i checked the table design in management studio.

@steve-2 Jones - here is an example

I have a PDF :
In Windows file explorer - shows 5,318 KB, if i right-click on the PDF in Windows it says Size 5.19MB (5,444,674 bytes)

So i upload the file,
then run that script i posted and get the following in Management studio:
SizeinBytes: 14519304
NumberofCharacters: 7259652

I put SizeinBytes through an online converter bytes to mb and get this: 13.846687316895 MB
If i put Numberofcharacters through the online converter charcters to mb i get: 6.923343658447 MB

Any ideas?

Phil Parkin SSC Guru Points: 246993 More actions · Answer 4

rkelly58 - Thursday, May 24, 2018 11:07 AM
Hi,
Thanks for the replies.
@SSC Guru - i'm using K2 Smartforms, their is a File attachment control which gets created as a column type of File in the SQL table (its called a smartobject in K2). This translates to nvarchar(max) when i checked the table design in management studio.
@steve-2 Jones - here is an example
I have a PDF :
In Windows file explorer - shows 5,318 KB, if i right-click on the PDF in Windows it says Size 5.19MB (5,444,674 bytes)
So i upload the file,
then run that script i posted and get the following in Management studio:
SizeinBytes: 14519304
NumberofCharacters: 7259652
I put SizeinBytes through an online converter bytes to mb and get this: 13.846687316895 MB
If i put Numberofcharacters through the online converter charcters to mb i get: 6.923343658447 MB
Any ideas?

My guess here is that the application software has to (somehow) convert the file from non-unicode to unicode format before it can save the file in an nvarchar(max) column and that it is the conversion process which makes the file size in SQL Server so much bigger than when it's native.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 5

Interesting. I'd guess there's some sort of padding or overhead, as Phil noted. Maybe try a few different known sizes and see? I'll try to load a few as well.

rkelly58 SSCrazy Points: 2151 More actions · Answer 6

Hi,

The files are re-added to an email and sent, so there must be a decrypt that puts the size back to how much it was.
Is there any SQL i can use to get the exact size as Windows Explorer shows?

Thanks

neilmarvin Grasshopper Points: 23 More actions · Answer 7

hi. is there a free accessible tool to repair my corrupted databases. use a screen reader jaws for windows. thanks.

sgmunson SSC Guru Points: 110640 More actions · Answer 8

rkelly58 - Friday, May 25, 2018 4:46 AM
Hi,
The files are re-added to an email and sent, so there must be a decrypt that puts the size back to how much it was.
Is there any SQL i can use to get the exact size as Windows Explorer shows?
Thanks

Only if you have access to the same "routine" that performs that same task. Why any file would ever be saved as nvarchar(max) when varbinary(max) is so much more appropriate is beyond understanding. Even if you are applying encryption, overhead that large is foolish, at best. Until I can see a convincing argument to the contrary, I'm guessing someone somewhere may have made it work, but is wasting a lot of space unnecessarily. And chances are, also wasting compute time converting to nvarchar from anything else.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)