Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server Tutorial - SELECT Statement 101
Error Messages
Home > SQL Server Error Messages > Msg 2739 - The text, ntext, and image data types are invalid for local variables.
SQL Server Error Messages - Msg 2739 - The text, ntext, and image data types are invalid for local variables.

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.

Related Articles :