SQL Server Stored Procedure with Text Field

[honcho]

Hello,

Does anyone have an example of an SQL Server stored procedure that updates a
record, where one of its field is of type "text"? My procedure is

/*
** Update the client note and production cycle in a Sites record. Set the
** When_submitted field to the current date/time.
*/
CREATE PROCEDURE spSiteNotePCycle
@ID int,
@Client_notes text,
@PCycle char(14)
AS
UPDATE Sites
SET Client_notes=@Client_notes, When_submitted={ fn NOW() }, PCycle=@PCycle
WHERE (ID = @ID)
GO

The procedure executes successfully, and after the update I get the expected
results in the When_submitted and PCycle fields. But I get only an empty
string in Client_notes. Is this because it is of type "text"? Does "text"
in a stored procedure require me to do something different?

Any help will be gratefully appreciated.

 

[Robbe Morris [C# MVP]]

Are you 100% certain that there is data in your variable???

Does it work if you run it manually in Query Analyzer?

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp

 

[honcho]

Yes, there is definitely text in the variable.
No, it does not work with Query Analyzer.
I got the answer from a different group yesterday. Instead of UPDATE, I
need to use UPDATETEXT or WRITETEXT. (These are described in Books Online.)
Thank you for your suggestions.