Problem with Stored ProcedureThis is a discussion on Problem with Stored Procedure within the Inetserver forums in Microsoft Tools category; 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... | ||||||||
| | ||||||||
| ||||||||
| Register | FAQ | Calendar | Search | Today's Posts | Mark Forums Read |
|
#1
08-25-2008, 03:50 PM
| |||
| |||
 Problem with Stored Procedure 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  |
|
#2
08-25-2008, 04:22 PM
| |||
| |||
| Re: Problem with Stored Procedure 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? > 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. |
|
#3
08-25-2008, 04:30 PM
| |||
| |||
| Re: Problem with Stored Procedure 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. |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
All times are GMT -5. The time now is 04:23 PM.
