Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

INSERT INTO table containing TEXT column

Posted on 2012-09-17
7
Medium Priority
?
897 Views
Last Modified: 2012-09-17
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.
0
Comment
Question by:FarrellFritz
  • 4
  • 3
7 Comments
 
LVL 12

Accepted Solution

by:
Jared_S earned 2000 total points
ID: 38406553
Use char as your data type.
0
 

Author Comment

by:FarrellFritz
ID: 38406567
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
 
LVL 12

Expert Comment

by:Jared_S
ID: 38406593
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:FarrellFritz
ID: 38406682
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
 
LVL 12

Expert Comment

by:Jared_S
ID: 38406805
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
 

Author Comment

by:FarrellFritz
ID: 38406812
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
 

Author Comment

by:FarrellFritz
ID: 38407343
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question