INSERT INTO table containing TEXT column

I have a SQL Table (SQL 2008) which contains a TEXT column.  Stuck with it.  Purchased package.

I need to INSERT a row to the table using TSQL (ultimately will become a trigger but for now cannot get it to work even as ascript).

Cannot set the local Variable as TEXT as I get the error:
Msg 2739, Level 16, State 1, Line 149
The text, ntext, and image data types are invalid for local variables.


If I set the local variable as VARCHAR(MAX)  I get:
Msg 402, Level 16, State 1, Line 107
The data types text and varchar(max) are incompatible in the equal to operator.
 
Also tried CAST (just for fun) and that didnt work either.

This should be simpler and I'm surprised I've never run across it before.

Any help appreciated.
FarrellFritzAsked:
Who is Participating?
 
Jared_SCommented:
Use char as your data type.
0
 
FarrellFritzAuthor Commented:
Sorry...already tried that too...

Msg 402, Level 16, State 1, Line 106
The data types text and char are incompatible in the equal to operator.
0
 
Jared_SCommented:
Sorry - I'm running 2005 and didn't realize that was a difference between the two.

Are you generating the text yourself or taking it from another table?

The second source of text is a shot in the dark, but here is an example of how it could be handled:

http://www.thejackol.com/2004/12/08/text-ntext-and-image-data-types-are-invalid-for-local-variables/

Otherwise your looking at WriteText or UpdateText as far as I can tell.

http://msdn.microsoft.com/en-us/library/ms186838(v=sql.100).aspx
http://msdn.microsoft.com/en-us/library/ms189466(v=sql.100).aspx

Yuck.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
FarrellFritzAuthor Commented:
Generating (typing) the text myself (from another example) but that may be part of the problem.  The actual value I am inserting is some HTML code (to trigger an  automated message generation through the application versus through SQL.  

Now..if I run just that INSERT statement by itself replacing the the variable with the actual text minus the embedded variable it works great.  BUt if I sue the variable (created by the SET statement) whether CHAR, VARCHAR(MAX) or any other data type, and which includes the embedded variable.  It give the error.

SCALED DOWN actual statements follow......

-----------------------------------------
Note that @BODY contains the VARCHAR variable  @WO_NUMA  that was created via the CAST command from an INT variable) (that part is working fine)  


SET @BODY  = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD><META http-
equiv=Content-Type content="text/html; charset=iso-8859-1"></HEAD><BODY><DIV><FONT face=Arial color=#000000
size=2>.********** ********** **********.<br>.********** Reply to this email to append information to this Work Order **********.<br>.********** Please do not remove this line when replying [[WO#' + @WO_NUMA +']] **********.<br>.********** ********** **********.<br><br><br><br>ACTUAL BODY OF MESSAGE CONTINUES HERE.<br>Thank you.<br></FONT></DIV></BODY></HTML>'      

------------------------------------------
Here's where I try to INSERT

INSERT INTO [TRACKIT8_DATA2].[dbo].[Notificationmessage]
(Subject, BodyText, CreateDate, NotificatioNStatusID,
      EngineName, StatusDate, ScheduledSendDate, SendRetriesRemainingCount)
VALUES(@SUBJECT, @BODY, @NOW, 1, 'Email', @NOW, @NOW, 3)

----------------------------------------------

BODYTEXT (@BODY) is where I'm running into the snag.  Again, it could simply be the embedded variable but I use that all the time (unless I'm experiencing advanced senility today)

THANKS!
0
 
Jared_SCommented:
I was able to run that in memory.

Can you try this and see if it fails?

declare @temp table (column1 text)

declare @WO_NUMA as varchar(5)
set @WO_NUMA = cast(5 as varchar)
declare @body as varchar(5000)

set @body = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD><META http-
equiv=Content-Type content="text/html; charset=iso-8859-1"></HEAD><BODY><DIV><FONT face=Arial color=#000000 
size=2>.********** ********** **********.<br>.********** Reply to this email to append information to this Work Order **********.<br>.********** Please do not remove this line when replying [[WO#' + @WO_NUMA +']] **********.<br>.********** ********** **********.<br><br><br><br>ACTUAL BODY OF MESSAGE CONTINUES HERE.<br>Thank you.<br></FONT></DIV></BODY></HTML>'      

insert into @temp values (@body)

select * from @temp 

Open in new window

0
 
FarrellFritzAuthor Commented:
That works. Very odd.  Even my code, taken out of the rmain script, works fine (again have to substitute literal values for the variables).  Only when executed as the entire script I get that error.  Suspect some problem somewhere else butunclear at this point.

Thanks
0
 
FarrellFritzAuthor Commented:
Thanks for everyone's help.  Seems once I changed it to CHAR it WAS working but the error (though actually atrributed to the specific statement I sent) had absolutely nothing to do with it wand instead was related to teh subsequent SELECT state.  Thanks to Jarrod_s for the CHAR solution.  That actually did resolve it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.