Re: Problem with Stored Procedure

From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
Date: Mon, 25 Aug 2008 16:30:12 -0400

Phil Grimpo wrote:

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?

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

--
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.

.

References:
- Problem with Stored Procedure
  - From: Phil Grimpo

Prev by Date: Re: Problem with Stored Procedure
Next by Date: Re: SQL Injection and DB user permissions
Previous by thread: Re: Problem with Stored Procedure
Index(es):
- Date
- Thread

Relevant Pages

Problem with Stored Procedure
... UPDATE Pages SET MainData = (SELECT MainData FROM Pages_Archive WHERE ... WHERE PageID = @PageID ... I get this error: "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression." ...
(microsoft.public.inetserver.asp.db)
Re: Problem with Stored Procedure
... UPDATE Pages SET MainData = (SELECT MainData FROM Pages_Archive WHERE ... WHERE PageID = @PageID ... I get this error: "Error 279: The text, ntext, and image data types ... Please reply to the newsgroup. ...
(microsoft.public.inetserver.asp.db)