Hello.

SQL Server update issue:

I want to be able to append data to a comments field when I update my table however, the code below gives me this error: Server: Msg 403, Level 16, State 1, Line 6
Invalid operator for data type. Operator equals add, type equals text. note that comments is a text field on roomreservationseriesmaster.

declare @comments varchar(50)
set @comments = 'stuffff'

update roomreservationseriesmaster
set comments = @comments + comments

Yep, read the SQL Server docs:

http://msdn.microsoft.com/en-us/libr...2(SQL.80).aspx


You are hosed.

Now, that's for SQL Server 2000.

But the docs for SQL Server 2008 say the same thing:
http://msdn.microsoft.com/en-us/library/ms177561.aspx

So... I don't see how you can do this via an UPDATE statement.

You may have to read the data into an app (e.g., C# or VB.NET code), perform the concatenation, and then update the entire TEXT field at one go.

That's one of the reasons not to use the text data type in more modern versions, use nvarchar(max) instead if available.
However you can update but you need to get a pointer to the field and then use specialised methods. See http://msdn.microsoft.com/en-us/library/ms189466.aspx

__________________
Joe
http://joe.fawcett.name/

There is a RBAR (Row By Agonizing Row) way to do this on a TEXT datatype but it can only be one row at a time... still interested?

__________________
--Jeff Moden

Good morning all and thank you for your responses.

Seems like this update is very difficult therefore, I plan to use a simple work-around: read my table, extract the comments field, append the new comments to the field in ASP and then update the table.

sal