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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 A quick noob datatype question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

juicemousezero
Starting Member

10 Posts

Posted - 08/02/2005 :  09:20:20  Show Profile  Visit juicemousezero's Homepage  Reply with Quote
I was wondering what the best kind of datatype would be to use if one wanted to store some large bodies of text in the database (HTML tags and all, mind) and then retrieve them selectively for display on a .aspx page.

See, I do that same thing on my website where I have a MySQL database and I use PHP to talk to it, and I have that particular column set to the 'text' datatype and it works fine. For the sake of practice, I want to make a vb.net/sql server version of my site, but on my SQL Server version of that DB, the 'text' datatype doesn't work and I was just wondering if I should be using another datatype or if I’m doing anything wrong.

Thanks in advance.

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/02/2005 :  09:23:44  Show Profile  Reply with Quote
Welcome to SQLTeam juicemousezero!

On SQL Server varchar has a limit of 8000 characters (4000 if you use nvarchar in order to be able to store UNICODE characters), so that kinda suggests that you need TEXT datatype.

"on my SQL Server version of that DB, the 'text' datatype doesn't work"

That aint right! What sort of errors / "funny happening" do you get?

Kristen

Edited by - Kristen on 08/02/2005 09:26:29
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/02/2005 :  09:25:24  Show Profile  Reply with Quote
Store the file on a file server and store the path of the file in the database.

MOO



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/02/2005 :  09:28:32  Show Profile  Reply with Quote
Brett, we do this - allows us to do easy searches of Content, and create templates that include other stuff, and so on.

If JuiceMouse0 is like us then 99.999% of the content will be less than 8K (so splitting over multiple rows might be an answer).

In our case the agility of having the code in the DB outweights the ease of having it in a file.

Now ... if it was an image ... we'd be on the same hymn sheet

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/02/2005 :  10:15:10  Show Profile  Reply with Quote
I guess we need to define what large bodies of text are.

Are we talking war and peace, or France's terrorism deterance policy?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/02/2005 :  10:29:04  Show Profile  Reply with Quote
SELECT DATA_LENGTH('RUN') ?

Kristen
Go to Top of Page

juicemousezero
Starting Member

10 Posts

Posted - 08/03/2005 :  10:31:05  Show Profile  Visit juicemousezero's Homepage  Reply with Quote
Not to worry, none of this stuff is over 8k and should even be less than 8000 characters. I tried varchar as well with the same problem, thus my confusion.

In fact you can see the working example at my site in its PHP/MySQL glory... http://www.juicemousecypher.com . It's not a complex system, but it's freakin handy, you know?

This is the error I'm getting: "The data you entered is not consistant with the data type or length of the column, or over grid buffer limit."

So yeah I just don't know what else to try cause it should be so simple anyway. In the event that I copied something that I wrote in Microsoft Word, would that stuff I copied give me problems? I ask because I know Word does weird stuff to text, like commas and what not. If that can be a problem, that might be it.

Edited by - juicemousezero on 08/03/2005 10:35:25
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/03/2005 :  12:07:23  Show Profile  Reply with Quote
Single / Double Quotes is the most likely thing - Word turns those around to look "pretty"

Is that the exact error message verbatim? (Including the spelling of "consistant"<sic>?)

Kristen
Go to Top of Page

juicemousezero
Starting Member

10 Posts

Posted - 08/03/2005 :  12:17:16  Show Profile  Visit juicemousezero's Homepage  Reply with Quote
That's error message verbatim, albeit the "consistant" bit, which is just a manifestation of my laziness to use spell check. "Consistent" is what I meant. Sorry about that.

Edited by - juicemousezero on 08/03/2005 12:20:09
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/03/2005 :  16:01:41  Show Profile  Reply with Quote
Couldn't find it in Google, any ideas where its coming from?

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 08/03/2005 :  16:15:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by X002548

I guess we need to define what large bodies of text are.

Are we talking war and peace, or France's terrorism deterance policy?




That was great!

Tara
Go to Top of Page

juicemousezero
Starting Member

10 Posts

Posted - 08/03/2005 :  17:10:13  Show Profile  Visit juicemousezero's Homepage  Reply with Quote
Well I tried replacing the single and double quotes with plain text and it still didn't work.

This is really starting to get to me. I guess I'll have to ask someone at school next time I go in, but that may be in another week or so. Grr.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/04/2005 :  00:45:11  Show Profile  Reply with Quote
Post the faulty code here?

Kristen
Go to Top of Page

juicemousezero
Starting Member

10 Posts

Posted - 08/04/2005 :  10:18:04  Show Profile  Visit juicemousezero's Homepage  Reply with Quote
I think I've done enough troubleshooting to track the problem down, which now leaves me scared and confused. It appears that the amount of text I'm trying to insert is simply too much, which I find totally puzzling since I've got the MySQL DB doing it, and you'd think that high end software like SQL Server could certainly handle the job, and yet it doesn't seem to be able to... it just doesn't add up to me, you know?

Well the code itself is just a few insert statements, such as...

INSERT INTO AOW (album_name, content, band_name)
VALUES ("Permission to Land", "I started the first BOW thing with the Darkness so I'll do that with this, too. This album is absolutely awesome. I listen to it a lot; always while driving and I'm always dancing around in my seat and singing along (poorly) with Justin Hawkins as he shreds and falsettos his way through one amazingly listenable song after another. I mean this puppy is awesome from beginning to end, people. With this release they've taken a lot of great elements from classic bands and rolled them into one nice package (the theatrics of Queen, for example). I really can't wait to see these dudes live. Till then however, I'll be continuing my jammage with this CD. I can only imagine what people think when they see me in my car jumping around and looking stupid. But it just doesn't matter because this CD just makes you feel good. Very much recommended. You had better give it a chance before you go around hating them because of the singer.", "The Darkness")

That's the shortest of all the entries for the AOW table. If I can't do that one, I can't do the others. And in another table I'll be storing pretty big chunks of HTML (cause you see, I only want two actual pages on the site, one to redirect to the viewing page, and the viewing page to display the website's content which will be taken from the DB).

But is that even a feasible project?
Go to Top of Page

juicemousezero
Starting Member

10 Posts

Posted - 08/04/2005 :  10:21:38  Show Profile  Visit juicemousezero's Homepage  Reply with Quote
By the way, thanks for the help you guys. This is really a pleasant forum.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/04/2005 :  10:32:18  Show Profile  Reply with Quote
What you are doing is "normal"

Pushing large text into SQL is not a problem, but:

1) You should be sure that your Application is catching, and displaying to you, any errors that occur - otherwise you are running blind

2) The ADO Drivers for SQL used to have, in old versions, a Real Problem with Big Text.

3) You've got some embedded single quotes in your "content" above. Depending on how you are passing that data through ADO to SQL that may be mucking things up.

Kristen
Go to Top of Page

juicemousezero
Starting Member

10 Posts

Posted - 08/04/2005 :  10:46:46  Show Profile  Visit juicemousezero's Homepage  Reply with Quote
Well, I'm using an academic version of Visual Studio.NET 2002... old it is indeed but Microsoft likes their money, and I dont have enough for anything newer. Do you know if that's old enough to give me problems? I guess that'd make sense if so.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/04/2005 :  10:49:05  Show Profile  Reply with Quote
You can download the latest version of ODBC/ADO/etc. which might cure the problem - if that's what the problem is! Might be an idea to check what version you have, and then see what MS say has been fixed in the newer version's Feature Lists

Kristen
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.12 seconds. Powered By: Snitz Forums 2000