asked on

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.

ASKER CERTIFIED SOLUTION

Jared_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FarrellFritz

ASKER

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.

Jared_S

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.

FarrellFritz

ASKER

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>.********** ********** **********. .********** Reply to this email to append information to this Work Order **********. .********** Please do not remove this line when replying [[WO#' + @WO_NUMA +']] **********. .********** ********** **********. ACTUAL BODY OF MESSAGE CONTINUES HERE. Thank you. </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!

Jared_S

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

FarrellFritz

ASKER

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

FarrellFritz

ASKER

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.