How to get over "Cannot fetch into text, ntext, and image variables." on SQL Server?

Question

I'm getting this error message: Msg 16927, Level 16, State 1, Procedure GetWfGenProcessParameters, Line 21 Cannot fetch into text, ntext, and image variables.

I realy need to make this cursor work with text data. Is there any way to get over this error?

Answer 1

FIrst, why are you using a cusrsor, cursors are to be avoided at all costs. If you use a set-based solution maybe your problem will go away. However without more of an idea as to what you are trying to do inteh cursor, it is hard to provide advice. Certainly I can't change the fact that SQL Server does not let you put text data into a variable. When you meet a system limitation, you need to rethink what you are doing and how you are doing it.

Answer 2

If you are using SQL Server 2005, you can use NVARCHAR(MAX):

CREATE TABLE text_test (test NTEXT NOT NULL)

INSERT
INTO    text_test
VALUES  ('test')

DECLARE @t NVARCHAR(MAX);

DECLARE txt_cur CURSOR FOR
        SELECT test
        FROM   text_test

OPEN    txt_cur

FETCH   NEXT
FROM    txt_cur
INTO    @t

CLOSE txt_cur

DEALLOCATE txt_cur
GO

DROP TABLE text_test
GO