Problem with Stored Procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phil Grimpo - 25 Aug 2008 20:50 GMT

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

Reply to this Message

Bob Barrows [MVP] - 25 Aug 2008 21:22 GMT

> I have this in my stored procedure:
>
[quoted text clipped - 12 lines]
> However, I have done this same things before in an INSERT statement.
> Does it not work for UPDATE?

What version of SQL Server?

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

Signature

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.

Reply to this Message

Bob Barrows [MVP] - 25 Aug 2008 21:30 GMT

> I have this in my stored procedure:
>
[quoted text clipped - 12 lines]
> However, I have done this same things before in an INSERT statement.
> Does it not work for UPDATE?

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

Signature

Reply to this Message

Webmaster Forum / ASP / Database Access / August 2008

Problem with Stored Procedure