Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Best way to store text in SQL Server for ASP pages?

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-07-13 : 17:58:29
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.

RobWafle
Starting Member

38 Posts

Posted - 2002-03-22 : 15:45:01
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

2 Posts

Posted - 2002-04-02 : 17:16:45
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

2 Posts

Posted - 2002-04-02 : 18:56:33
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

9 Posts

Posted - 2002-09-23 : 10:10:59
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 - 2003-01-27 : 09:05:48
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 - 2003-01-27 : 09:07:38
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 - 2003-02-27 : 17:55:11
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

15732 Posts

Posted - 2003-02-27 : 18:47:07
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 - 2003-03-01 : 13:06:38
robvolk,

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

Go to Top of Page

marcus
Starting Member

1 Post

Posted - 2003-06-19 : 02:02:31
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

15732 Posts

Posted - 2003-06-19 : 07:27:52
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 - 2004-06-08 : 22:38:52
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

1 Post

Posted - 2008-03-29 : 06:21:05
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
   

- Advertisement -