Cursor Error

Results 1 to 6 of 6

Thread: Cursor Error

  1. #1
    Join Date
    Dec 1969
    Posts
    1,045

    Default Cursor Error

    Im trying to loop through one table and then UPDATE another using a cursor. The problem Is I keep getting the same error:<BR><BR>Server: Msg 2739, Level 16, State 1, Line 2<BR>The text, ntext, and image data types are invalid for local variables.<BR><BR>The data thats being updated it of datatype text. Here is the script.<BR><BR>DECLARE @ContentID int, @Content text<BR><BR>SET NOCOUNT ON <BR><BR>DECLARE abc CURSOR FOR<BR><BR>SELECT wci.ContentID, wci.Content FROM dbo.WEBContent wci<BR><BR>WHERE wci.Approved = &#039;y&#039; <BR> AND Day(wci.UpdateDate) = Day(GetDate()) <BR> AND Year(wci.UpdateDate) = Year(GetDate())<BR><BR>OPEN abc<BR><BR>FETCH NEXT FROM abc INTO @ContentID, @Content<BR>WHILE (@@FETCH_STATUS = 0)<BR><BR>BEGIN<BR>UPDATE kbrady.WEBContent <BR>SET kbrady.WEBContent.Content = @Content<BR>WHERE @ContentID = kbrady.WEBContent.ContentID<BR><BR>FETCH NEXT FROM abc INTO @ContentID, @Content<BR>END<BR><BR>CLOSE abc<BR>DEALLOCATE abc<BR>GO<BR><BR>SET NOCOUNT OFF

  2. #2
    Join Date
    Dec 1969
    Posts
    11,247

    Default RE: Cursor Error

    Actually the error message says it all <BR>The text, ntext, and image data types are invalid for local variables.<BR><BR>DECLARE @ContentID int, @Content text &#060;---- Local variables<BR><BR>

  3. #3
    Join Date
    Dec 1969
    Posts
    3,921

    Default Correct...Limitation in SQL Server

    But, if you know virtually all of the columns hold a number of bytes that will fit into a nvarchar or varchar datatype, can you fetch into a varchar or nvarchar variable.<BR><BR>http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=309

  4. #4
    Join Date
    Dec 1969
    Posts
    1,045

    Default RE: Cursor Error

    Interesting I have revised the script to actauuly create a Temp table and then I loop through and I get the following error.<BR><BR>Server: Msg 279, Level 16, State 3, Line 14<BR>The text, ntext, and image data types are invalid in this subquery or aggregate expression.<BR><BR>What is up with Text<BR><BR>Here is the new script:<BR>SELECT blenker.WEBContentBK.Content <BR>INTO #ContentUpdate<BR>FROM blenker.WEBContentBK <BR>WHERE ContentID = 33<BR><BR>SELECT * FROM #ContentUpdate<BR><BR><BR>SET NOCOUNT ON<BR><BR>DECLARE @minID INT,<BR> @maxID INT,<BR> @counter INT<BR><BR>--GET MIN AND MAX IDS<BR>SELECT @minID = MIN(contentID) FROM blenker.WEBContent<BR>SELECT @maxID = MAX(contentID) FROM blenker.WEBContent<BR><BR>SELECT @counter = 0<BR><BR>WHILE (@counter &#060;= @maxID) BEGIN<BR> UPDATE blenker.WEBContentBK <BR> SET Content = (SELECT * FROM #ContentUpdate)<BR> WHERE ContentID IN<BR> (<BR> SELECT ContentID<BR> FROM blenker.WEBContent WC1<BR> WHERE Approved = &#039;y&#039; AND<BR> Day(UpdateDate) = Day(GetDate()) AND<BR> Year(UpdateDate) = Year(GetDate()) AND<BR> ContentID = @counter<BR> )<BR> SELECT @counter = @counter + 1<BR>END<BR><BR><BR>SET NOCOUNT OFF


  5. #5
    Join Date
    Dec 1969
    Posts
    96,118

    Default A text field can be...

    ...up to *TWO GIGABYTES* in size.<BR><BR>And so you simply can&#039;t do any operations on such fields, other than return them from SELECTs (where, if need be, the user can get their full contents via GetChunk operations).<BR><BR>SQL Server refuses to believe you when you tell it "oh, but *this* particular TEXT field is never going to be more than 17K bytes." It just makes a worst case assumption that it will need a 2GB buffer and says "nope, no way I can do that."<BR><BR>

  6. #6
    Join Date
    Dec 1969
    Posts
    3,921

    Default You can get around it if less than x number of byt

    If you know the datalength is say less than 4000 bytes for an ntext, you can CAST it to a nvarchar:<BR><BR>select cast(bodytext as nvarchar(4000)) as bodyText<BR> from tableA<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •