|
|
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/Workaround:
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.
|