October 28, 2003 at 11:12 am
Hey everyone -- got a particular situation that may be a bit over my head and I thought I would seek the wisdom of the masters out there.
I have a customer who has a website that is driven by MSSQL7 and he has data being stored in columns that are text. Now the problem that he is having is that whenever one of his posts is over 8000 characters all information is being trimmed. Customer wants to migrate everything over to ntext for his columns and he has some stored procedures in place as well.
My question is this: can we just make the modification without corrupting any of the existing data? Besides ntext, is there another route that my customer can take? Normally we wouldn't even touch this, but the DBA that he has been using has vanished. Any assistance out there is mucho appreciated. In addition, I can provide as much details as needed.
Thanks again.
Thats Bigman 2 Y-O-U!
Thats Bigman 2 Y-O-U!
October 28, 2003 at 12:11 pm
Going from text to ntext may not solve his problem. I'm pretty sure his problem is due to a problem in ADO and not SQL7. I tried to find the KB article but was unable to. The biggest question is does he really need to store unicode characters? If there is no need for the 2 byte character storage then just solve the 8000 character limit problem by changing the way that they access the fields.
If you are running into the problem found on the KB article Q259956 then the first thing he should do is to make sure that he has all the latest service packs installed.
In any case I wouldn't think that converting to a ntext field should cause any problems. Having said that I would certainly create a copy of his database on a test server and run through the conversion and then test the results before doing the change on the production box.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 28, 2003 at 12:31 pm
Thanks Gary!
I looked at the KB article that you specified, but this only applies to SQL 7 prior to SP2 and the server is running SP4: Microsoft SQL Server 7.00 - 7.00.1063
All the other latest service packs are installed for the operating system. Basically the database stores resumes and he has some customers posting resumes that exceed the 8000 characters -- only a small fraction. The customers post their resume through his site and they have the ability to go in and update their cover page, etc. This is sometimes where the problem arises when they are doing any updates, etc. and it exceeds the 8000 characters. Does he need to have the 2 byte character storage? Probably not, but didn't know if this would cause any issues modifying the tables over to ntext. I was just thinking whether or not this would be feasible -- and of course we would test this out on test database before implementing on the production to ensure of no data loss. Is there anything that I have to do to modify the columns from text to ntext? Or is this a pretty straightforward process?
If there is any other info that you can provide I would be more than willing to listen. Thanks again and I will post this in the correct location next time.
Thanks again.
Thats Bigman 2 Y-O-U!
Thats Bigman 2 Y-O-U!
October 28, 2003 at 1:56 pm
Ok...I'm a little confused so bear with me.
His data type is text. Checking the data type limitations for text it is over 2 billion characters.
NText would bring his limitation down to 1 billion.
Varchar has a limitation of 8000 characters.
You gain nothing from switching to ntext from text unless you are planning to use international characters.
It sounds as if there is some other limitations that are outside of the database - such as ADO.
Bonne chance
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
October 28, 2003 at 2:06 pm
Oooga Wooga,
Thanks Patrick. I'm confused myself, but I am relaying this information back to my client and I think he is as stumped as I am. But I will pursue this and respond back once I have more details.
Thanks again everyone! You guys have been saviors whether you believe it or not.
Thats Bigman 2 Y-O-U!
Thats Bigman 2 Y-O-U!
October 28, 2003 at 5:22 pm
As Patrick stated and I tried to point out. If they are not using UNICODE characters then there is no benefit of going to an NTEXT data type. I don't remember if you can use an ALTER TABLE ALTER COLUMN in SQL 7. If not you will need to create a new table with the proper structure and then copy the data into the new table. Then rename the old and new tables.
From what you have stated it sounds to me like they are losing the data during the edit process and need to look at that process very carefully to find out what the culprit is. My guess is that you will find that the text box being used on the web page is what is doing the truncating of the data.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 28, 2003 at 7:02 pm
Patrick & Gary,
Gentlemen -- thanks! I have passed on the information to the customer and he is going to look at his web app to see whether or not his app is the actual culprit. I expressed to him the issues that you brought up and he is going to speak with his web developer to look and see if that is the actual cause of the problem.
Again thanks -- as always, the members of SQLServerCentral have helped me out once again.
Have a great evening guys!
Thats Bigman 2 Y-O-U!
Thats Bigman 2 Y-O-U!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply