Msg 2739 : The text, ntext, and image data types are invalid for local variables.
MS SQL Server data types Text, NText or Image data types have some limitations to work with.
One major limitation ob text, ntext and image sql data types is you can not declare local variables of sql type text, ntext and image with in your t-sql codes.
Executing the below t-sql declaration statements on a SQL Server database will cause the following sql server error messages - Msg 2739 :
declare @text as ntext;
declare @ntext as ntext
declare @image as image;
Msg 2739, Level 16, State 1, Line 3
The text, ntext, and image data types are invalid for local variables.
As the SQL Server error 2739 message points to that limitation, you can not use text, ntext and image data types for declaring local variables.
Solution or Workaround for error Msg 2739
Microsoft SQL Server 2005 has many enhancements when compared to SQL Server 2000.
One of the enhancements in MS SQL Server 2005 is the new sql data types like varchar(max), nvarchar(max) and varbinary(max).
You can prefer using these new data types if you are working on MS SQL Server 2005 or MS SQL Server 2008.
As far as I remember these new data types varchar(max), nvarchar(max) and varbinary(max) can handle data sizes up to 2 GB which is capable of solving problems of many of us.
If you are using MS SQL Server 2000 databases, the work-around for error 2739 may be a bit complicated.
You may prefer to use varchar(8000) or nvarchar(4000) instead of text and ntext sql data types.
You can CONVERT or CAST data which you can read from sql table into local variables of varchar or nvarchar and use this converted values in your t-sql code statements or in sql stored procedures.
declare @varchar varchar(8000)
declare @nvarchar nvarchar(4000)
-- Using CAST function
select @varchar = CAST(txt as varchar(8000)) from TextData where id = 1
select @nvarchar = CAST(txt as nvarchar(4000)) from TextData where id = 1
-- Using CONVERT function
select @varchar = CONVERT(varchar(8000), txt) from TextData where id = 1
select @nvarchar = CONVERT(nvarchar(4000), txt) from TextData where id = 1
select @varchar, @nvarchar
An other work around for dealing SQL Server error message 2739 is using READTEXT, WRITETEXT or UPDATETEXT T-SQL commands with TEXTPTR text pointer function to read, manipulate and edit or update text, ntext and image data type columns in SQL Server database tables.
|