Problem with Stored Procedure

[Phil Grimpo]

I have this in my stored procedure:

@PageID INT,
@VersionID INT

UPDATE Pages SET MainData = (SELECT MainData FROM Pages_Archive WHERE
PageID = @PageID AND VersionID = @VersionID)
WHERE PageID = @PageID

I get this error: "Error 279: The text, ntext, and image data types are
invalid in this subquery or aggregate expression."

MainData is of type "text"

However, I have done this same things before in an INSERT statement.
Does it not work for UPDATE?

Thanks.

-Phil

[Bob Barrows [MVP]]

What version of SQL Server?

I think you will need to use WRITETEXT to do this. Look it up in BOL.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Disregard that last message, you do not need WRITETEXT.

You should try:
1. UPDATE Pages SET MainData = (SELECT TOP 1 MainData ...

2. UPDATE Pages SET MainData = (SELECT MainData
FROM Pages_Archive WHERE
PageID = Pages.PageID AND VersionID = @VersionID)
WHERE PageID = @PageID
--also try TOP 1 with this version