SQL Server question - field contents

Dave

Dave

Perch
I have a field that holds posted forum messages that is of type ntext. I notice that if the field doesn't have too much text in it I can view/edit the contents of the field with Enterprise Manager. But with longer messages all I can see is <Long Text>. I'm unable to view or change the text, although it displays just fine from the web page.

Anyone know what this is all about? Is it possible to change the view so I can see the actual text?

Thanks!
 
I believe there is no way to show the data when the field is a ntext field type when viewing through Enterprise Manager.

>> Anyone know what this is all about?
Here is something that has been taken from the help files from Enterprise Manager:

Individual text, ntext, and image values can be a maximum of 2-GB, which is too long to store in a single data row. In Microsoft? SQL Server? 2000, small text, ntext, or image values can be stored directly in the row, but values too large to fit in the row are stored in a collection of pages separate from the pages holding the data for the other columns of the row.

So this is the reason why you can see some stuff, but not all.

>> Is it possible to change the view so I can see the actual text?
After spending about 20minutes of research on this the only thing that i can say is, i dont think its possible. I wish it was :(
 
As far as I know, there is no way to do it. But instead, you can use Query Analyzer for your task (this is what I do). Yes, a bit more work - as you need to write SQL scripts, but works perfectly
 
Yes, you should really be using query analyzer for this. Make sure to change it's options setting which increases the size of returned text fields, and you might want to display it in text format too, not grid. By the way, I think Enterprise Manager will also place unwanted page intent locks on your table, whereas you can turn that off with query analyzer using the nolock hint in your query or setting the transaction isolation level to read uncommitted...
-Dave
 
Thanks to all for the explanations and work-arounds. Since I've just started learning about SQL Server recently I have quite a ways to go before I'm anywhere near proficient with it and all it's tools.

I really appreciate the help from everyone here.
 
Dave said:
I have a field that holds posted forum messages that is of type ntext. I notice that if the field doesn't have too much text in it I can view/edit the contents of the field with Enterprise Manager. But with longer messages all I can see is <Long Text>. I'm unable to view or change the text, although it displays just fine from the web page.

Anyone know what this is all about? Is it possible to change the view so I can see the actual text?

Thanks!
Click to expand...
there is a very easy way to do this:

use the cast function to create a new varchar column on the fly.

SELECT CAST(TextColumn AS varchar(4000)) AS Readable,*
From Table
Where conditions
 
Something I find very handy for data manipulation, is to simply use MS Access to examine my SQL data, instead of using EM which is a bit clunky for that.

Create an ODBC link to your SQL database, create a new Access MDB, then link in your tables using the ODBC connection. Link, not Import.

Then you can do funky stuff like seeing all your ntext data, use subdatasheets, create handy queries, copy-paste discrete columns of data, etc. etc. I find EM great for design, but Access better for data viewing & editing.

hope that helps.
 
You must log in or register to reply here.
Back
Top