Composing an xp_smtp_sendmail - SQL Server Forums

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)

Composing an xp_smtp_sendmail

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-06 : 10:27:49

Can anyone tell me what's wrong with my syntax? The error is

Server: Msg 141, Level 15, State 1, Line 65
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

So I'm thinking the order of the statements is wrong and I've tried changing order or the assignments and query... I have yet to finish putting thr @parameters into the HTML, but clearly the main problem is with the one above. I have to do data retrieval and assign variables / parameters to that at the smae time? How to I do it? Script is below.... Thanks

-- ************************************************************************
-- 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(12),
@email NVARCHAR(50),
@date datetime(8),
@desc NVARCHAR(50),
@text NVARCHAR(4000),
@jobno int,
@cust NVARCHAR(50),
@client NVARCHAR(10),
@address NVARCHAR(150),
@cost money

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 : 03/03/2008 12:00PM</TD></TR><TR><TD>Details Of Work Carried Out : Any printing character within the 8-bit character encoding of ISO 8859/1 (256 character positions) or the 7-bit character encoding of ISO 646 (128 character positions) may be represented within the text of an HTML document by a numeric character reference, e.g. #233; is a small e with an acute accent. It is recommended that character entity references such as é are used in preference to numberic character references</TD></TR><TR><TD>Job Number : SHGD123456</TD></TR><TR><TD>System Received Date : 03/03/2008 06:00PM</TD></TR><TR><TD>Current System Status : Pending</TD></TR><TR><TD>Customer Name : John Smith</TD></TR><TR><TD>Address : Address 1 + Address 2 + Address 3 + Address 4 +Address 5 + Post Code</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 : Control characters are non-printable characters that are typically used for communication and device control, as format effectors, and as information separators. In SGML applications, the use of control characters is limited in order to maximize the chance of sucessful interchange over heterogenous networks and operating systems. In HTML, there are only three control characters which are used. The remaining 55 control characters are shunned and should not appear in an HTML document. The valid control characters and their interpretation are: </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>S123456</TD><TD>Single door with glass 100x50</TD><TD>1</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

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
@desc = ccs_qos_title,
@text = ccs_not_text#1 + ' ' + ' ' + ccs_not_text#2 + ' ' + ccs_not_text#3,
@jobno = ccs_quo_ref_number#2,
@cust = ccs_quo_site_contact,
@client = ccs_quo_company_ref,
@address = ccs_quo_address#1 + '', '' + ccs_quo_address#2 + ', ' + ccs_quo_address#3 + ', ' + ccs_quo_address#4 + ', ' + ccs_quo_address#5 + ', ' + ccs_quo_address#6,
@cost = ccs_qos_quoted_price#6
from [ken-sql-002].constructmobile.dbo.ccs_quotes q
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'

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-06 : 10:30:06

Ahhhhhhhhhhhhh..!!!!

Do I need to put a GO in there somewhere..?!?!?!??

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-06 : 10:32:42

Tell me - is a GO equivalent to a semicolon?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources