Passing variables to HTML in xp_smtp_sendmail

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing variables to HTML in xp_smtp_sendmail

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-07 : 08:24:17

I have a load of @variables loaded up with values from my query and I want to output these in HTML and email the result.

I put these @things in the right places in the HTML tags but all I get in the message are the names of the variable as they appear. The HTML doesn't pick them up and convert them to values as I expected.
What am I doing wrong?

If I write a few SELECTs in the script then I get all the right values back in the grid. Why don't the parameters get converted to values in HTML yet?

Thanks

Here's the code:

-- ************************************************************************
-- Mobile Solution Mailer

-- ************************************************************************
declare @FROM NVARCHAR(4000),
@FROM_NAME NVARCHAR(4000),
@TO NVARCHAR(4000),
@CC NVARCHAR(4000),
@BCC NVARCHAR(4000),
@priority NVARCHAR(10),
@subject NVARCHAR(4000),
@message NVARCHAR(4000),
@type NVARCHAR(100),
@attachments NVARCHAR(4000),
@codepage INT,
@rc INT,
--------------------------------------
@Operative NVARCHAR(50),
@SOR NVARCHAR(100),
@email NVARCHAR(50),
@date datetime,
@received datetime,
@desc NVARCHAR(50),
@text1 NVARCHAR(100),
@text2 NVARCHAR(100),
@text3 NVARCHAR(100),
@jobno char(10),
@cust NVARCHAR(50),
@client NVARCHAR(10),
@address1 NVARCHAR(30),
@address2 NVARCHAR(30),
@address3 NVARCHAR(30),
@address4 NVARCHAR(30),
@address5 NVARCHAR(30),
@address6 NVARCHAR(30),
@quantity decimal,
@status NVARCHAR(12),
@cost money

select distinct @SOR = ccs_qos_item_ref,
@Operative = ccs_mee_oper_name,
@email = ccs_cd_email,
@date = ccs_quo_user_date#1, -- current datetime from jobcompletedUpdate
@received = received,
@text1 = ccs_not_text#1,
@text2 = ccs_not_text#2,
@text3 = ccs_not_text#3,
@jobno = ccs_quo_ref_number#2,
@cust = ccs_quo_site_contact,
@client = ccs_quo_company_ref,
@address1 = ccs_quo_address#1,
@address2 = ccs_quo_address#2,
@address3 = ccs_quo_address#3,
@address4 = ccs_quo_address#4,
@address5 = ccs_quo_address#5,
@address6 = ccs_quo_address#6,
@quantity = ccs_qos_qty#2,
@status = ccs_quo_analysis_code,
@cost = ccs_qos_quoted_price#6
from [ken-sql-002].constructmobile.dbo.ccs_quotes q
inner join [ken-sql-002].hettonhosttestdatabase.dbo.job j
on q.ccs_quo_ref_number#2 = left(j.cid,10)
left outer join [ken-sql-002].constructmobile.dbo.ccs_q_sections qs
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
left outer join [ken-sql-002].constructmobile.dbo.ccs_site_meeting m
on m.ccs_mee_quote_number = q.ccs_quo_quote_number
left outer join [ken-sql-002].constructmobile.dbo.ccs_notes n
on q.ccs_quo_note_number = n.ccs_not_number
left outer join [ken-sql-002].constructmobile.dbo.ccs_company_file f
on q.ccs_quo_company_ref = f.ccs_cd_account_number
where q.ccs_quo_analysis_code = 'PENDING'

select @FROM = N'DO_NOT_REPLY@axiseurope.com',
@FROM_NAME = N'DO_NOT_REPLY@axiseurope.com',
@TO = N'michael.bird@axiseurope.com',
@CC = N'',
@BCC = N'',
@priority = N'Normal',
@subject = N'Validation Required for - SHGD123456',
@message = N'<HTML>Confirmation of Works Completed <HR WIDTH="100%" SIZE="1" COLOR="#CCCCCC"><TABLE WIDTH="60%"BORDER="1" BORDERCOLOR="#CCCCCC" CELLPADDING="4" CELLSPACING="0" ><TR><TD>Operative : @Operative</TR></TD><TR><TD>Date / Time : @date</TD></TR><TR><TD>Details of work carried out... : @text1</TD></TR><TR><TD>Job Number : @jobno</TD></TR><TR><TD>System Received Date : @received</TD></TR><TR><TD>Current System Status : @status</TD></TR><TR><TD>Customer Name : @cust</TD></TR><TR><TD>Address :@address2</TD></TR><TR><TD>Signature of Customer : John Smith</TD></TR><TR><TD>Signature of Engineer : Bill Gates</TD></TR><TR><TD>Report Further Action : </TD></TR></TABLE><TABLE WIDTH="60%" BORDER="1" BORDERCOLOR="#CCCCCC" CELLPADDING="5" CELLSPACING="0" ><TR><TD>Sor Code</TD><TD>Description</TD><TD>Quantity</TD><TD>Check / Valid</TD><TD>Omit Code</TD></TD></TR><TR><TD>@SOR</TD><TD>@desc</TD><TD>@quantity</TD><TD>[___]</TD><TD>[___]</TD></TD></TR></TABLE><TABLE WIDTH="60%" BORDER="0" BORDERCOLOR="#CCCCCC" CELLPADDING="0" CELLSPACING="20" ><TD></TD><TR><TD>______________________________________</TD></TR><TR><TD>Signature of Supervisor</TD></TR></TABLE></HTML>',
@type = N'text/html',
@attachments = N'',
@codepage = 0

exec @rc = [ken-sql-001].master.dbo.xp_smtp_sendmail
@FROM = @FROM,
@TO = @TO,
@CC = @CC,
@BCC = @BCC,
@priority = @priority,
@subject = @subject,
@message = @message,
@type = @type,
@attachments = @attachments,
@codepage = @codepage,
@server = N'192.168.25.22'

select RC = @rc
go

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-07 : 10:15:09

I tried double-quoting the @parameters and it doesn't work

I think I have to get a procedure to re-produce this HTML per each email address (the values for each record are different) and then it will pick up the variables properly each time it makes the HTML text...

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-10 : 07:48:50

Hasn't this ever been done before...........?!?!??!?!??!

xp_smtp_sendmail is for HTML. And it's also for SQL. So what would be the point of those two without generating a query and putting data into that HTML?

How do you do it??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources