May 24, 2018 at 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?
May 24, 2018 at 10:34 am
rkelly58 - Thursday, May 24, 2018 9:48 AMHi,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.846687316895Any 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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 24, 2018 at 10:37 am
What does Datalength() show?
May 24, 2018 at 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?
May 24, 2018 at 11:44 am
rkelly58 - Thursday, May 24, 2018 11:07 AMHi,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: 7259652I 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 MBAny 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 24, 2018 at 12:03 pm
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.
May 25, 2018 at 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
May 25, 2018 at 5:12 am
hi. is there a free accessible tool to repair my corrupted databases. use a screen reader jaws for windows. thanks.
May 25, 2018 at 6:41 am
rkelly58 - Friday, May 25, 2018 4:46 AMHi,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)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply