Footer rows to skip: website

Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance

That's because your field is not numeric, if it were numeric, you would expect 61,100,200

but, with text, it looks at MP with a '1' and sees it first, then, MP with a '2' and sees it next - - naturally 6 is after 2 - - but that's the reason.

|||

It is possible to get text to behave a bit like numbers.

If you had stored this instead, it would sort correctly:

MP061, MP100, MP200

This example presumes that the numerical component will always be no more than 3 characters and all 3 postiions are identified for each record (even if they are zero). In general, it's better not to try to sort alpha-numeric data in a numerical sort order.

|||If all of your records have a 2-character prefix in that column, andthere are only numeric characters that follow, you can use thisapproach to solve your sorting problem:

SELECT
someColumns
FROM
someTable
ORDER BY
CAST(SUBSTRING(mpColumn,3,99) AS integer),
mpColumn

I'm building a system when one can upload a document to the website.

I will be storing the document on the hard-drive for quick/easy access,
but I was also thinking of storing it in an existing database since most
of the sites information is all stored there.

As well there would be only one place to worry about backing up. And if
the file on the hard-drive was ever missing or became corrupted, I could
restore it form tha database. Is this feasable? Has anyone ever done this?

--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@.web-impact.com <mailto:donv@.webimpact.com>
web: http://www.web-impact.com

> I'm building a system when one can upload a document to the website.
> I will be storing the document on the hard-drive for quick/easy access,
> but I was also thinking of storing it in an existing database since most
> of the sites information is all stored there.
> As well there would be only one place to worry about backing up. And if
> the file on the hard-drive was ever missing or became corrupted, I could
> restore it form tha database. Is this feasable? Has anyone ever done
> this?

Sure it's possible to save any size binary object in the DBMS, but it is
not usually a performant use of a DBMS as a simple file store. I would
first consider a hardware solution for backups of read-only files. Either
just store copies on separate disks, or get a fancier RAID setup where
any one disk failure won't lose any data.
Joe Weinstein at BEA|||Don Vaillancourt (donv@.webimpact.com) writes:
> I'm building a system when one can upload a document to the website.
> I will be storing the document on the hard-drive for quick/easy access,
> but I was also thinking of storing it in an existing database since most
> of the sites information is all stored there.
> As well there would be only one place to worry about backing up. And if
> the file on the hard-drive was ever missing or became corrupted, I could
> restore it form tha database. Is this feasable? Has anyone ever done
> this?

I have not done this myself, but certainly it is a common scenario.

Storing the documents on disk makes for a simple implementation.
Storing them in the database requires a battle with the text/image
data types, which are somewhat difficult to use.

But as you note, storing in the database is safer. Backup is simpler,
and a file on directory can easily "disappear". And if all you store
is the file path, another problem is that you don't have two-phase
commit with the file system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I know that technically I can store this info in the database and
retrieving it is not an issue.

My issue really is how manageable will the database be with tons of
PDFs, Word, etc. documents in it. Or how willing people would be
willing to manage such a beast.

Right now our databases are about 300MB each. I may decide to create a
second database, not sure, but the total number of documents may be at
the very least in the 1000's which would most probably create a database
about 5GB + overhead.

To some this may be an intimidating size to manage. It would certainly
fit on a tape backup.

I'm just wondering about other issues that may come up.

Erland Sommarskog wrote:
> Don Vaillancourt (donv@.webimpact.com) writes:
>>I'm building a system when one can upload a document to the website.
>>
>>I will be storing the document on the hard-drive for quick/easy access,
>>but I was also thinking of storing it in an existing database since most
>>of the sites information is all stored there.
>>
>>As well there would be only one place to worry about backing up. And if
>>the file on the hard-drive was ever missing or became corrupted, I could
>>restore it form tha database. Is this feasable? Has anyone ever done
>>this?
>
> I have not done this myself, but certainly it is a common scenario.
> Storing the documents on disk makes for a simple implementation.
> Storing them in the database requires a battle with the text/image
> data types, which are somewhat difficult to use.
> But as you note, storing in the database is safer. Backup is simpler,
> and a file on directory can easily "disappear". And if all you store
> is the file path, another problem is that you don't have two-phase
> commit with the file system.
>

/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/|||Don Vaillancourt (donv@.webimpact.com) writes:
> I know that technically I can store this info in the database and
> retrieving it is not an issue.
> My issue really is how manageable will the database be with tons of
> PDFs, Word, etc. documents in it. Or how willing people would be
> willing to manage such a beast.
> Right now our databases are about 300MB each. I may decide to create a
> second database, not sure, but the total number of documents may be at
> the very least in the 1000's which would most probably create a database
> about 5GB + overhead.
> To some this may be an intimidating size to manage. It would certainly
> fit on a tape backup.

Few of our customers that run our system have databases that small. My test
database alone is 600 MB.

And 5 GB in one database is easier to manage, than 300 MB in a database + a
lot of loose files.

However, if you are using MSDE the size could be a concern, since MSDE
has a size limit of 2 GB.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||No MSDE for us. We tell how clients which version of whatever database
they must purchase in order to use our software.

Otherwise, from your experiences, my issue is really a non-issue.

Thanks for your input.

Erland Sommarskog wrote:
> Don Vaillancourt (donv@.webimpact.com) writes:
>>I know that technically I can store this info in the database and
>>retrieving it is not an issue.
>>
>>My issue really is how manageable will the database be with tons of
>>PDFs, Word, etc. documents in it. Or how willing people would be
>>willing to manage such a beast.
>>
>>Right now our databases are about 300MB each. I may decide to create a
>>second database, not sure, but the total number of documents may be at
>>the very least in the 1000's which would most probably create a database
>> about 5GB + overhead.
>>
>>To some this may be an intimidating size to manage. It would certainly
>>fit on a tape backup.
>
> Few of our customers that run our system have databases that small. My test
> database alone is 600 MB.
> And 5 GB in one database is easier to manage, than 300 MB in a database + a
> lot of loose files.
> However, if you are using MSDE the size could be a concern, since MSDE
> has a size limit of 2 GB.

Thursday, March 29, 2012

Field not sorting in ascending order

Wednesday, March 21, 2012

Feasability of storing PDFs and WORD.DOC files in MSSQL?

Footer rows to skip

Blog Archive

About Me