SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Best way to store text in SQL Server for ASP pages?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/13/2000 :  17:58:29  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Brad writes What is the best way to store text in SQL Server (7.0) so that it can easily and quickly be served up to an ASP page? For example, say I have 500 articles, ranging in size from 500 characters to 25,000 characters. Not only do I want to store the text of the article, but also the title of the article, its date, the author, the subject, and so on. I also want to be able to search the text of the articles. Obviously a VARCHAR field won't hold really long articles, and TEXT fields are slow and hard to work with. I also want to avoid using the SQL Server Search service. What is the ideal solution?

Brad M. McGehee
sql-server-performance.com

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 08/15/2000 :  13:08:52  Show Profile  Reply with Quote
Fake story 8000 characters

If it's going to be a really interesting fake story, please write it.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 08/17/2000 :  15:12:54  Show Profile  Reply with Quote
Documentation

Can you show us a little documentation on how this was done? For example, how exactly did you make the quotes compatible with IIS 5? Thanks!

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/20/2000 :  11:36:59  Show Profile  Reply with Quote
Real Big TEXT Fields

Could you please explain how the following is done in more detail?

Real Big TEXT Fields
In my youthful innocence I created a 55,000 character text field using an ASP page (I used TEXTAREA). I passed this using a POST to another ASP page and called a stored procedure passing my 55,000 character field as a parameter. It got passed and INSERTed just fine. I returned it back to the page using a SELECT and it displayed just fine. Took a while to scroll down the page but all the text was there. I tried the same thing on the hosted site using a 15,000 character field and it worked fine also.

Joost Meij
jmeij@msn.com

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/04/2001 :  01:18:29  Show Profile  Reply with Quote
What youthful Innocence? All lies and a big & beautifully crafted fake metodology.

Have U lost your mind?

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/02/2001 :  04:17:01  Show Profile  Reply with Quote
fake story more than 8000 characters

please show us an example of how you did this. I am trying to do the same thing without success so far.

Thanks :)

Rachel

Go to Top of Page

diego
Starting Member

1 Posts

Posted - 11/05/2001 :  02:35:00  Show Profile  Reply with Quote
Ok, I've stumbled across this problem too and in "most" of all I aggree with You but as You read MOST of all but not all. I was designing a site for reading/publishing books online and knew that I surely had to deal with REALY large texts. First I started the same way as You..but see the "evolution" of my thoughts Yourself:

1. Storage of data-definitions (author,submitdate, title, genre, topic etc.) in a table. [status ok and kept because there IS no alternative way of sense]
2. Storage of the content in TEXT fields for easier fulltext-searches to be performed. [Status dropped this idea for a while, see below]
3. Because of the inefficient way of retreiving all the data for the content on every BOOK-page I played around with the much faster filesystemobject and also the STREAM object and got a very good performance boost in heavy-load situations [Still not satisfied because its harder to implement indexing as a linked service on a remote(!) sql-server while the indexed pages are located on the WEB-server as resource files. Additionally not satisfied with the obvious situation when You want to access the data from different machines without manually changing site-code (thats what makes a database so useful isnt it? ]
4. Full-Text Indexing of remote data went me nuts so I decided to play with TEXT-fields again. Additionally that killed my fears concerning data-access from remote sites.
5. IIS5 and MSSQL2000 allow for simple usage of Recordset addnew or inserts and selects. But(!) You will definately not be sure what happens with REALY large text (I found situations when IIS will raise an error (and these methods tend to be VERY slow in heavy load). I decided to go back to retreive/insert data by ado-methods getchunk and appendchunk.
6. I am still not satisfied because each time a user reads a bookpage he is causing a roundtrip to the sql-server. Yes, MDAC2.6 + has several intelligent caching options and it works just fine but still not the solution...
I decided to "cache" the bookpages on the webserver(s) and to write a little functionset that (1.writes to that chache when a page is setup/altered/deleted, 2. on READ checks if a certain document exists and if not creates it from the database content and then reads it for output. Only "negative" sideeffect is that You have to handle redundant data (text is stored as "master" in the database while all webservers have "slave" textcontent cached. Intimes of 100GB+ harddiscs this really should NOT be a problem of space, but indeed it CAN become a performance problem again if the caching is not organized VERY good (for more info get informed about the NTFS filesystem and its inner works)

After all my system looks like:

Post/edit message/book:
-write ALL data to database (all descriptive by inserts or better yet using the command object of ADO and procedures, textcontent by appending chunks to Your database.) Use Fulltext-indexing whereever You like in Your database (fast and reliable)
-Creating the Cachefile of the CONTENT to save Yourself from transfering the content text on each request (SQL-Server =WAN/LAN=> IIS-Server)
Read message
-read descriptive data from database (if a search is performed the CONTENT in database is used too)
- then read the LOCAL chache to get the Content of the book into the page

I am still considering if I will extend caching in the way that I create the whole HTML-document and dont perform any script for lets name it "content.asp?book=213" instead: "/content/213.html". Not a problem to make it but I still think about several negative effects of it. But as You all might know asp still performs slower than showing a static html-file. Remember even a messageboard is typically write*1/read*n and all extensive tasks should be made in *1 situations rather than *n situations.

Thats my input, no for my questions :

In Sql-server, to create a linked server named FTIndexWeb that accesses a full-text index named Web, You execute:
"sp_addlinkedserver FTIndexWeb, 'Index Server', 'MSIDXS', 'Web'"

Unfortunately I did not find an OLEDB property for indexing provider that maps the property location (as used in addserver) to the indexing server oledb property "machine" so I could use a remote machine where the textfiles reside and the sql-server uses a distributed query (openquery) to access this via a linked (indexing) server. I only managed to use it with any catalog stored in indexing service on the local server. Maybe someone knows how to define a remote machine in sp_addserver (the same when using the grafical admin in enterprise manager) with indexing service OLEDB? I need it for another scenario where I use sql-server to check for indexed documents on an enterprise fileserver and merge it with database informations. For now I have to use scripts running local on the fileserver and sending info periodically to the sql-server. This does not satisfy me and I'd prefer to use a pull-method instead of push.

Go to Top of Page

RobWafle
Starting Member

38 Posts

Posted - 03/22/2002 :  15:45:01  Show Profile  Visit RobWafle's Homepage  Send RobWafle an AOL message  Send RobWafle an ICQ Message  Send RobWafle a Yahoo! Message  Reply with Quote
diego,

if you're still out there.. please contact me at robwafle@hotmail.com ..

The idea of the linked server below allows you to query the microsoft indexing engine from SQL server, so you don't have to put your documents in the database.. just the filesystem.

For me, this was exactly what I was looking for, as I have 311GB+ (aprox 150GB/year) of documents that didn't need to be copied into my SQL server.

Rob

Go to Top of Page

NewtonTroy
Starting Member

Canada
2 Posts

Posted - 04/02/2002 :  17:16:45  Show Profile  Reply with Quote
Has anyone been able to set up indexing services on a remote machine and connect to it sing a linked server?

I have no problem setting up Indexing Services as a linked server locally, but I can't seem to connect to a remote machine's Indexing Service through setting up a linked server.

Any ideas or suggestions? Anyone have this working?


Troy
TroyN@Ingenium-Tech.com
Go to Top of Page

NewtonTroy
Starting Member

Canada
2 Posts

Posted - 04/02/2002 :  18:56:33  Show Profile  Reply with Quote
For anyone trying to do this, maybe give this a try.. note the MachineName.CatalogName..Scope()

select filename, doctitle from machine_name.catalog_name..scope()


Hope that helps... it seems to have worked for me.


Troy


ps.
http://groups.google.com/groups?q=MSIDXS+sp_addlinkedserver&hl=en&newwindow=1&selm=OcPc1mN3AHA.1804%40tkmsftngp04&rnum=4

Go to Top of Page

John
Starting Member

United Kingdom
9 Posts

Posted - 09/23/2002 :  10:10:59  Show Profile  Reply with Quote
Please could you give more explaination of your solution as I cannot seem to get this sorted.
Cheers

Go to Top of Page

pinklehammer
Starting Member

2 Posts

Posted - 01/27/2003 :  09:05:48  Show Profile  Reply with Quote
quote:

Real Big TEXT Fields <P>Could you please explain how the following is done in more detail?

Real Big TEXT Fields
In my youthful innocence I created a 55,000 character text field using an ASP page (I used TEXTAREA). I passed this using a POST to another ASP page and called a stored procedure passing my 55,000 character field as a parameter. It got passed and INSERTed just fine. I returned it back to the page using a SELECT and it displayed just fine. Took a while to scroll down the page but all the text was there. I tried the same thing on the hosted site using a 15,000 character field and it worked fine also.

Joost Meij
jmeij@msn.com





Go to Top of Page

pinklehammer
Starting Member

2 Posts

Posted - 01/27/2003 :  09:07:38  Show Profile  Reply with Quote
Define the table in SQL server, open Access and create a project with an existing data source, open the table in the Access project and you can successfully paste HUGE amounts of text which will be stored/retrieved quite happily within SQL.


quote:

Real Big TEXT Fields <P>Could you please explain how the following is done in more detail?

Real Big TEXT Fields
In my youthful innocence I created a 55,000 character text field using an ASP page (I used TEXTAREA). I passed this using a POST to another ASP page and called a stored procedure passing my 55,000 character field as a parameter. It got passed and INSERTed just fine. I returned it back to the page using a SELECT and it displayed just fine. Took a while to scroll down the page but all the text was there. I tried the same thing on the hosted site using a 15,000 character field and it worked fine also.

Joost Meij
jmeij@msn.com





Go to Top of Page

gjsiii
Starting Member

2 Posts

Posted - 02/27/2003 :  17:55:11  Show Profile  Reply with Quote
I'm having problems setting up a database as described in the article. I too would like a varchar field to hold 4000 characters however I am unable to get it to hold any more than 1023 -- even when the length of the field is set to 8000. Is the 8000 the size of the field in bytes?

I'm also unable to get any more than 256 characters into a field of datatype text and cannot change the length from the default length of 16. This is all happening on SQL Server 2000 and I am using Enterprise Manager to modify the tables. Any suggestions? Thanks.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 02/27/2003 :  18:47:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
You can't change the length of text. The 16 bytes is the size of the pointer it uses to store the actual data. You simply declare a column of type "text" and don't indicate a size.

Do not use Enterprise Manager for data entry, it is the wrong tool for the job. It has all kinds of little quirks like the one you're seeing. The easiest way to enter data into SQL Server is to link the tables into an Access database. You'll be able to do all of the data entry there without any limitations.

Go to Top of Page

gjsiii
Starting Member

2 Posts

Posted - 03/01/2003 :  13:06:38  Show Profile  Reply with Quote
robvolk,

That seems to work just fine. Thanks for the help.

Go to Top of Page

marcus
Starting Member

1 Posts

Posted - 06/19/2003 :  02:02:31  Show Profile  Reply with Quote
A question slightly off track if I may..

I have a database that has data (3 of 300 rows > 8000 characters) stored in a text field. I would like to UPPER this data but this cannot be done on a text field. Is there another way to do this please...my long search through google got me to here

cheers
Marcus
Go to Top of Page

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 06/19/2003 :  07:27:52  Show Profile  Visit robvolk's Homepage  Reply with Quote
Look in Books Online under the UPDATETEXT command. That's probably the only way you'll be able to do it. You'd use READTEXT to read part of the text column into a variable, UPPER() the variable, and use UPDATETEXT to change the column.

Go to Top of Page

Michael Shan
Starting Member

2 Posts

Posted - 06/08/2004 :  22:38:52  Show Profile  Reply with Quote
Hi,

Anyone can give me a quick answer. I make one field as varchar(8000) datatype. But when I try to insert long string data into this field, it only allow around 1000 char to be inserted. Do you know why and how to solve this problem?

Thanks

quote:
Originally posted by AskSQLTeam

Brad writes What is the best way to store text in SQL Server (7.0) so that it can easily and quickly be served up to an ASP page? For example, say I have 500 articles, ranging in size from 500 characters to 25,000 characters. Not only do I want to store the text of the article, but also the title of the article, its date, the author, the subject, and so on. I also want to be able to search the text of the articles. Obviously a VARCHAR field won't hold really long articles, and TEXT fields are slow and hard to work with. I also want to avoid using the SQL Server Search service. What is the ideal solution?<BR><BR>Brad M. McGehee<BR><a href="http://www.sql-server-performance.com">sql-server-performance.com</a><P>Article <a href="/item.asp?ItemID=202">Link</a>.

Go to Top of Page

nedda
Starting Member

Bulgaria
1 Posts

Posted - 03/29/2008 :  06:21:05  Show Profile  Visit nedda's Homepage  Reply with Quote
quote:
Originally posted by robvolk

You can't change the length of text. The 16 bytes is the size of the pointer it uses to store the actual data. You simply declare a column of type "text" and don't indicate a size.

Do not use Enterprise Manager for data entry, it is the wrong tool for the job. It has all kinds of little quirks like the one you're seeing. The easiest way to enter data into SQL Server is to link the tables into an Access database. You'll be able to do all of the data entry there without any limitations.





This does it - inserted text programatically and it worked just fine. Thanks for the time saver!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000