September 27, 2005 at 7:04 am
I have a table that has a ntext and varchar col and I need to return both really at the same time.
At the moment I do this to return the nText data.
SELECT @Size = DATALENGTH(Textual)/2 FROM tbl_DEFAULT_EMAILS WHERE EmailPK = @EmailPK
SELECT @ptr=TEXTPTR(Textual)
FROM tbl_DEFAULT_EMAILS
WHERE EmailPK = @EmailPK
if @ptr <> null
READTEXT tbl_DEFAULT_EMAILS.Textual @ptr 0 @Size
I have just added a col called sFields which is a csv list of fields that I am going to loop through replacing the placeholders with values.
If possible I would like to retrive both at the same time something like
SELECT Textual, stFields FROM tbl_DEFAULT_EMAILS where EmailPK=@EmailPK
but i was told this would not work if the nText blob was large.
Any tips or help much appreciated.
Is this possible
September 27, 2005 at 7:07 am
AFAIK (may be wrong) but you need to retrieve the TEXT column(s) last
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 27, 2005 at 9:55 am
what if the size of the Text data is bigger than the rowsize does it not get cut off.
I thought you had to use readtext.
if i can get all the data back no matter how big and the other cols by going
SELECT sFields, Textual FROM tbl_DEFAULT_EMAILS where EmailPK = @EmailPK
then I will do that but I thought there was a reason you couldnt do that if the ntext was so large. If im wrong then thats cool.
Cheers
September 28, 2005 at 1:16 am
Readtext is usually for use within TSQL stuff executing on the server. You can "select" text / image columns and it is up to your client side library (eg ADO, ADO.NET, ODBC, etc) to provide it to you as a string, stream, memory buffer, etc.
The max size of the row (~8000bytes) is for data stored in a table - think though that you can join many tables together in one query that may easily exceed 8000 bytes - it doesn't matter how much data you return or how "wide" it is.
So your select statement will work a treat, and yeah, I had heard that you should select your BLOBs last as well. What are you doing client side with the data - eg what library are you using to access the DB?
September 28, 2005 at 1:53 am
im using standard ADO.
Its a website written in ASP. I need to store default and custom emails with placeholders. Return the text/html replace the placeholders with values then send off the email.
As I have feeling that some of our emails are quite large and I am writing a site that could be multi language Ive chose ntext.
Ok thanks for your help.
September 28, 2005 at 7:21 am
You can have text or image data type column in select statement directly. The TEXT column does not need to be the last one. I have this kind of proc running in production (the Text column is the first one). For image column, i am not sure it needs to be the last or not. I have both ado and ado.net clients.
As Ian said, it's upto what connectivity your client is using.
The maximum bytes of text/image column can be returned in the select statement is controlled by @@TEXTSIZE. You can change it by SET TEXTSIZE. By default oledb and odbc connection set it to 2147483647 bytes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply