SQL Server Error Messages - Msg 2739¶

Error Message¶

Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

Causes:¶

As the message suggests, you cannot create local variables of text, ntext or image data types. The following simple line of code will easily reproduce the error:

DECLARE @TextColumn TEXT

Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

Solution / Work Around:¶

If you are using SQL Server 2005, you can use the varchar(max), nvarchar(max) and varbinary(max) in place of the text, ntext and image data types, respectively. With varchar(max), nvarchar(max) and varbinary(max), you can create local variables of these data types and manipulate them just like any varchar, nvarchar and varbinary data type.

If you are using SQL Server 2000, a work around is to use VARCHAR(8000) or NVARCHAR(4000) as the data type of the local variable. Then you have to CAST the value of the TEXT into these data types. The disadvantage of using VARCHAR(8000) or NVARCHAR(4000) is that you are limited in the length, as specified in the length.

Here’s an example on how to use NVARCHAR in place of the NTEXT data type:

DECLARE @CategoryDescription  NVARCHAR(4000)

SELECT @CategoryDescription = CAST([Description] AS NVARCHAR(4000))
FROM [dbo].[Categories]
WHERE [CategoryID] = 1

A second option is to make use of multiple VARCHAR(8000) or NVARCHAR(4000) local variables then concatenate them as needed. One common task when using local variables with more than 8000 characters is when building and executing long dynamic SQL statements. This limitation can easily be overcome by making use of multiple VARCHAR(8000) or NVARCHAR(4000) local variables and then concatenating them when they are about to be executed:

DECLARE @SQLStmt1    VARCHAR(8000)
DECLARE @SQLStmt2    VARCHAR(8000)
DECLARE @SQLStmt3    VARCHAR(8000)

-- Build your SQL statements using these variables

EXECUTE (@SQLStmt1 + @SQLStmt2 + @SQLStmt3)

A third option is to use the READTEXT, WRITETEXT and UPDATETEXT Transact-SQL statements together with the TEXTPTR function to manipulate text, ntext and image columns in a table. You can refer to Books Online regarding these Transact-SQL statements and functions for more information.