January 11, 2005 at 3:41 pm
Hi Everyone,
I'm creating a stored procedure that generates an HTML email and inserts the record into a temporary table that is accessed by an email engine we created. My problem is that many emails that I'm generating are much larger than 8000 characters but from what I understand I can't use the datatype of ntext in my procedure.
Here's some sample code form my procedure:
declare @message1 varchar(8000)
while (@@fetch_status = 0)
begin
select @message1 = '<TR bgcolor="#e0e0e0">'
select @message1 = @message1 + '<TD valign="top" class="searchresults">' + Cast(@tid as varchar(20)) + '</td>'
select @message1 = @message1 + '<TD valign="top" class="searchresults">' + @sport + '</td>'
select @message1 = @message1 + '<TD valign="top" class="searchresults">' + Cast(@trans_date as varchar) + ', ' + @trans_time + '</td>'
select @message1 = @message1 + '<TD valign="top" class="searchresults">'
if @qty < 0
begin
select @message1 = @message1 + '<font color="red">' + Cast(@qty as varchar(10)) + '</font>'
end
else
begin
select @message1 = @message1 + Cast(@qty as varchar(10))
end
------
Depending on the number of records returned by my cursor, the paramater, @message1 is going to store much more than 8000 characters. Is there a way to declare a variable in T-SQL to store more than 8000 characters?
Any suggestions are appeciated.
Thanks,
Gutch
January 12, 2005 at 1:28 am
Hi,
You could try using a temporary table to build the text. You could try something like this :
declare @message varchar(8000)
declare @txtptr varbinary(16)
declare @i int set @i = 1
-- create the temp table to hold the message we are building
create table #message(message text )
-- create an empty message
insert #message (message) values ('')
-- start of the loop ( can be while @@fetch_status ... )
while @i < 100
begin
-- Generate the message of max length 8000 bytes
set @message = replicate(@i,4000)
-- To update a text column, we need the text pointer and use updatetext
-- This is the same as update #message set message = message + @message
select @txtptr = textptr(message) from #message
updatetext #message.message @txtptr null 0 @message
-- increment i ...
set @i = @i + 1
end
-- The result is now stored in the table #message !
January 12, 2005 at 2:57 am
Hi Jason,
Cant you just declare @Ntext1(4000), @NText2(4000), @NText3(4000), @NText4(4000). Portion up your message / e-mail and then SELECT @NText1 + @NText2 + @NText3 + @NText4.
Have fun
Steve
We need men who can dream of things that never were.
January 12, 2005 at 6:21 am
I think a better way of approaching this is to remove the HTML creation from the stored procedure completely. HTML is usually display-tier stuff.
If you created the email engine, then you should be able to push a change through that would pull the data from SQL Server, and create the email body as it creates the email.
Have you looked into SQLXML? By pulling the data as XML from SQL Server, then transforming it with XSLT either at the email engine or with a middle tier object between your SQL Server and email engine, you will realize serious performance and flexibility gains over performing multiple string concats inside a cursor.
For example, changing the SQL to:
SELECT @tid AS [tID], @sport as [Sport],
CONVERT(char(10), @trans_date, 101) + @trans_time AS [TransDate],
@qty AS [Qty]
FOR XML RAW...would produce XML similar to the following (namespaces omitted):
<root> <row tID="1" Sport="College Hoops" TransDate="01/15/2005 1:30:00" Qty="10" /> </root>
...which can then be transformed and formatted with XSLT.
This approach can also keep things set-based, and you can run without the cursor, further improving performance. Just turn your DECLARE CURSOR FOR SELECT (...) into a normal SELECT, with FOR XML RAW:
SELECT t.tID, s.Sport, CONVERT(char(10), t.TransDate, 101) + t.TransTime AS [TransDate],
t.Qty
FROM dbo.table1 t (NOLOCK) INNER JOIN
dbo.table2 s ON t.ID = s.ID
WHERE t.Field1 = @SearchCondition1 AND s.Field2 = @SearchCondition2
FOR XML RAW
...would produce XML similar to the following (namespaces omitted):
<root> <row tID="1" Sport="College Hoops" TransDate="01/15/2005 1:30:00" Qty="10" /> <row tID="3" Sport="College Football" TransDate="01/10/2005 10:30:00" Qty="12" /> <row tID="4" Sport="College Hoops" TransDate="12/15/2004 5:30:00" Qty="-6" /> <row tID="7" Sport="College Hoops" TransDate="01/15/2005 1:30:00" Qty="14" /> </root>
Just as with normal resultsets, there is no 8000-character limit using this approach.
The XSLT might look something like (snippet only):
<xsl:for-each select="//root/row">
<TR bgcolor="#e0e0e0">
<TD valign="top" class="searchresults"><xsl:value-of select="@tID" /></TD>
<TD valign="top" class="searchresults"><xsl:value-of select="@Sport" /></TD>
<TD valign="top" class="searchresults"><xsl:value-of select="@TransDate" /></TD>
<TD valign="top" class="searchresults"><xsl:value-of select="@Qty" /></TD>
</TR>
</xsl:for-each>If the email engine was written in .NET, then you can look into the SQLXML classes provided by ADO.NET. If your data access is through ADO, then there are a few changes you need to make to your calls to receive XML results (see "Dialect", "InputStream", and "OutputStream" in the SQLXML documentation). Note that SQLXML is a separate download and install on the client machine (the email engine box).
There can be a bumpy learning curve when creating your first few HTML pages using SQLXML. Stick to FOR XML RAW when getting started (skip FOR XML AUTO, it's more trouble than it's worth). Later, when you're comfortable with that, you can look into FOR XML EXPLICIT, which provides complete control over the structure of the XML document.
The stuff can look kind of ugly at first, but everyone I've taught SQLXML to quickly fell in love with it for all sorts of uses, from creating HTML to using XML strings to pass data between different systems. For many uses, it can provide hefty performance gains, especially when compared to the method of building HTML by looping through a resultset.
Eddie Wuerch
MCM: SQL
January 13, 2005 at 10:59 am
Thanks for the great ideas! I like the temporary table method but after I have the data in the temporary table I still need to extract it from the temp table in my stored proc so that I can insert that text into another table (the table that our email engine reads from). Again I'm back at square 1 because I can't declare a variable that is large enough to extract the data from the temporary table. Am I missing something here?
Suppose my message is @message. I need to write an insert statment like the following:
insert into email_engine(@to_address, @from_address, @subject, @message) values(.....)
So in order to insert @message I need to assign it my long email message. How can I do that from the temporary table field?
Thanks,
Jason
January 13, 2005 at 2:12 pm
I do not understand why you need it in a variable to do the insert.
You can do something like this, using the temp table :
create table email_engine
(
to_address varchar(255),
from_address varchar(255),
subject varchar(255),
message text
)
declare @to_address varchar(255),
@from_address varchar(255),
@subject varchar(255)
set @to_address = 'anaddress@adomain.be'
set @from_address = 'me@mydomain.be'
set @subject = 'a test'
-- execute some code to fill #message table as in previous post
insert into email_engine (to_address, from_address, subject, message)
select to_address = @to_address,
from_address = @from_address,
subject = @subject,
message = #message.message
from #message
January 13, 2005 at 3:48 pm
Bert,
Thank-you! This worked perfectly! I didn't realize you could use your suggested syntax for the insert statement. This is exactly what I was looking for!
Thanks again!
-Jason
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply