I'm doing something like the following, but I need to insert a few
functions.
1) I would like to be able to insert each orderline from our
INVOICELINEARCHIVE in the email text below the T&T number - like:
*Pseudo code*
For Each Line in INVOICELINEARCHIVE, Where INVOICELINEARCHIVE.NUMBER =
@.OrderNumber
Print OrderText, OrderAmount, OrderPrice
Next
- or this might be done by a SELECT ? Or another Cursor somehow?
- I have used the SELECT with XPSMTP before, like:
SELECT @.message = @.message + '<TR><TD>' + ORDERHEADARCHIVE.NUMBER +
'</TD><TD>' + ORDERHEADARCHIVE.NAME + '</TD></TR>'
FROM ***
WHERE ***
(A) Tried something like this by comparing the variable @.OrderNumber with my
ORDERHEADARCHIVE.ORDERNUMBER in the WHERE statement, but it wouldnt work...
2) I would further more like to be able to insert multipe T&T numbers into
the same email - this is when an order is split over more than one package.
This is what I have right now:
--
CREATE PROC SP_TRACKNTRACE
AS
SET NOCOUNT ON
DECLARE @.CustomorName varchar(100), @.OrderNumber varchar(10), @.InvoiceNumber
varchar(20), @.EmailAddress varchar(150), @.TrackTraceNumber varchar(20),
@.NEXT_KEY int,
@.message varchar(4000), @.msg nvarchar(4000), @.From varchar(400), @.FromName
varchar(400), @.ReplyTo varchar(300), @.BCC varchar(300), @.RecepientList
varchar(400),
@.Subject varchar(300), @.RC int, @.body varchar(3000), @.title varchar(80),
@.header varchar(10), @.signature varchar(10)
SET @.BCC = 'netsales@.domain.com'
SET @.Subject = 'Deliverystatus - Track & Trace'
DECLARE SEND_EMAIL CURSOR
FOR
SELECT INVOICEHEADARCHIVE.NAME,
INVOICEHEADARCHIVE.EMAIL,
IT_TRACKNTRACE.ORDERNUMBER,
IT_TRACKNTRACE.INVOICENO,
IT_TRACKNTRACE.TRACKNTRACE
FROM INVOICEHEADARCHIVE,IT_TRACKNTRACE
WHERE INVOICEHEADARCHIVE.EMAIL like '%_@._%.__%'
AND IT_TRACKNTRACE.TRACKNTRACE <> ''
AND ISNUMERIC(IT_TRACKNTRACE.INVOICENO) > 0
AND INVOICEHEADARCHIVE.INVOICENO = CONVERT(INT,IT_TRACKNTRACE.INVOICENO)
AND INVOICEHEADARCHIVE.INVOICEDATE > DATEADD(DAY,-1,GETDATE())
OPEN SEND_EMAIL
FETCH NEXT FROM SEND_EMAIL INTO @.CustomorName, @.EmailAddress, @.OrderNumber,
@.InvoiceNumber, @.TrackTraceNumber
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
BEGIN
SET @.FromName = 'Company Name'
SET @.From = 'netsales@.domain.com'
SET @.msg = N'<HTML><font face="Arial" size="2">Dear ' +
@.CustomorName + ','
SET @.msg = @.msg + N'<BR><BR>'
SET @.msg = @.msg + N'Today we send you ordernumber ' + @.OrderNumber + ' ,
invoicenumber ' + @.InvoiceNumber + '.'
SET @.msg = @.msg + N'<BR><BR>'
SET @.msg = @.msg + N'Your package has Track & Trace number: ' +
@.TrackTraceNumber + '.'
--** This is where I wuld like to add all T&T numbers concerning the order
SET @.msg = @.msg + N'<BR><BR><BR>'
--** This is where I would like some lines from a table by a SELECT query or
something like it (A)
SET @.msg = @.msg + N'Best Regards'
SET @.msg = @.msg + N'<BR><BR>'
SET @.msg = @.msg + N'My Company Name'
SET @.msg = @.msg + N'<BR>'
SET @.msg = @.msg + N'<a href="http://links.10026.com/?link=http://www.domain.com">www.domain.com</a>'
SET @.msg = @.msg + N'</font></HTML>'
END
SET @.ReplyTo = @.From
EXEC @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.From,
@.FROM_NAME = @.FromName,
@.TO = @.EmailAddress,
@.BCC = @.BCC,
@.replyto = @.ReplyTo,
@.subject = @.Subject,
@.message = @.msg,
@.type = N'text/html',
@.server = N'192.168.0.1'
FETCH NEXT FROM SEND_EMAIL INTO @.CustomerName, @.EmailAddress,
@.OrderNumber, @.InvoiceNumber, @.TrackTraceNumber
END
CLOSE SEND_EMAIL
DEALLOCATE SEND_EMAIL
--
Thanx
Jakob"Sokrates" (somebody@.somewhere.earth) writes:
> I'm doing something like the following, but I need to insert a few
> functions.
> 1) I would like to be able to insert each orderline from our
> INVOICELINEARCHIVE in the email text below the T&T number - like:
> *Pseudo code*
> For Each Line in INVOICELINEARCHIVE, Where INVOICELINEARCHIVE.NUMBER =
> @.OrderNumber
> Print OrderText, OrderAmount, OrderPrice
> Next
> - or this might be done by a SELECT ? Or another Cursor somehow?
> - I have used the SELECT with XPSMTP before, like:
> SELECT @.message = @.message + '<TR><TD>' + ORDERHEADARCHIVE.NUMBER +
> '</TD><TD>' + ORDERHEADARCHIVE.NAME + '</TD></TR>'
> FROM ***
> WHERE ***
> (A) Tried something like this by comparing the variable @.OrderNumber
> with my ORDERHEADARCHIVE.ORDERNUMBER in the WHERE statement, but it
> wouldnt work...
"It wouldn't work" means what? You got an error message? You got an
unexpected result? It is always recommendable to be specific when you
post a question, as it easier for people to help you.
In any case, I am not really sure what the question is, but starting
from the subject line, yes you can nest cursors. And if I understand
this talk about T&T, it seems that a cursor is what you need to include
them all in the message.
However, I think you are on the wrong path when you try to send this from
SQL Server. When you build the message, you must assemble the text
in a varchar(8000) variable, which means that 8000 bytes is the space
you have, for content and HTML tags and all.
It's probably better to have a client app, to read the data and then
compose the HTML thing. You don't run the risk that the mail is cut
in the middle, and the coding is likely to be easier.
I also would like to point out that if this mail could be sent to
anyone, it's not a good idea to sent mail in HTML format only. Not
everyone uses mail readers that understand HTML. And some people may
have spam filterns that nukes everything which is in HTML only. So
probably you should generate a text-only message, or a
multipart/alternative with both text and HTML.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment