TEXT/NTEXT column type

[Guest]

I'm designing an ASP.NET site where I was thinking of storing all my articles in a MSSQL2K database. 8000 chars isn't much for a real article with HTML codes etc so I have to use a TEXT/NTEXT column for this

In good coding practice, I have created a seperate data-tier which utilizes stored procedures in the database. The problem comes when I try using an output parameter to retrieve a TEXT/NTEXT column from a single row in my Articles table. I have read that when retrieving single rows that output parameters are much better than returning recordsets, but the recordset method is the only method I can get working with the TEXT/NTEXT column

Is it impossible to return the whole column as an output parameter or do I have to do it using the recordset -> DataTable/DataSet method? Is this good design or is there a better way of doing things?

 

[Miha Markic [MVP C#]]

Hi Chris,

Yes, you are correct - you can't return text datatype in a parameter.
Instead, return a recordset.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I'm designing an ASP.NET site where I was thinking of storing all my

articles in a MSSQL2K database. 8000 chars isn't much for a real article
with HTML codes etc so I have to use a TEXT/NTEXT column for this.

In good coding practice, I have created a seperate data-tier which

utilizes stored procedures in the database. The problem comes when I try
using an output parameter to retrieve a TEXT/NTEXT column from a single row
in my Articles table. I have read that when retrieving single rows that
output parameters are much better than returning recordsets, but the
recordset method is the only method I can get working with the TEXT/NTEXT
column.

Is it impossible to return the whole column as an output parameter or do I

have to do it using the recordset -> DataTable/DataSet method? Is this good
design or is there a better way of doing things?

 

[Kevin Yu [MSFT]]

Thanks for Miha's quick response.

Hi Chris,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to return a text/ntext column
from a stored procedure with an output parameter. If there is anything
unclear, please feel free to let me know.

Just as Miha mentioned, we cannot return text datatype in a parameter.
Instead, we have to return a recordset which contains the text information.
We can also make the stored procedure to return the record, an use
SqlDataReader.GetSqlString method to get the value of it.

For more information about GetSqlString method, please check the following
link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqldatareaderclassgetsqlstringtopic.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Thank you for your responses. The following article confused my understanding
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.as

This article states that text, ntext and image data types can serve as input and output parameters for stored procedures. Is this just plain wrong or is there something I'm missing here

I will continue using the recordset method since this works. I don't know if its good design keeping all articles in the database instead of the file-system but I will give the former method a try

Best regards
Chris

 

[Kevin Yu [MSFT]]

Hi Chris,

I think there might be some misunderstanding here. Text, ntext and image
data types can serve as input and output parameters for stored procedures.
However, when setting values to the parameter, we cannot use SET
statements. As it is a pointer points to a block that contains text, we
have to use UPDATETEXT or WRITETEXT to achive this.

For more informations about UPDATETEXT and WRITETEXT statement, please
check the following links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
ua-uz_6i2c.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
wa-wz_2odw.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."