xp_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)

xp_sendmail

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 07:00:58

Here is a different query which runs just fine standalone. I wish I could set it out nicely for you to look at. I format everything very carefully and readably but when pasting into here, the text is truncated to its own column length and I can't fix that...

select distinct top 100 ccs_qos_item_ref "SOR Code",
ccs_mee_oper_name "Operative",
ccs_quo_user_date#1 "Date", -- current datetime from jobcompletedUpdate
ccs_qos_title "Description",
ccs_not_text#1 + ' ' + ' ' + ccs_not_text#2 + ' ' + ccs_not_text#3 "Details of work carried out...",
ccs_quo_ref_number#2 "Job no.",
ccs_quo_site_contact "Name of Customer",
ccs_quo_company_ref "Client",
ccs_quo_address#1 + ', ' + ccs_quo_address#2 + ', ' + ccs_quo_address#3 + ', ' + ccs_quo_address#4 + ', ' + ccs_quo_address#5 + ', ' + ccs_quo_address#6 "Address",
ccs_qos_quoted_price#6 "Cost"
from axis_europe.dbo.ccs_quotes q
inner join axis_europe.dbo.ccs_q_sections qs
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
inner join axis_europe.dbo.ccs_site_meeting m
on m.ccs_mee_quote_number = q.ccs_quo_quote_number
inner join axis_europe.dbo.ccs_notes n
on q.ccs_quo_note_number = n.ccs_not_number

After I wrap this up in xp_sendmail, the immediate error at line 8 is the first concatenation of fields (the plus sign) which is a surprise... Why is this a problem, when it all parses and runs fine when it is executed before...?

Thanks

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 07:02:54

exec master.dbo.xp_sendmail
@recipients = 'michael.bird@axiseurope.com',
@subject = 'Please verify these SOR Codes',
@query = 'select distinct top 100 ccs_qos_item_ref "SOR Code",
ccs_mee_oper_name "Operative",
ccs_quo_user_date#1 "Date", -- current datetime from jobcompletedUpdate
ccs_qos_title "Description",
ccs_not_text#1 + ' ' + ' ' + ccs_not_text#2 + ' ' + ccs_not_text#3 "Details of work carried out...",
ccs_quo_ref_number#2 "Job no.",
ccs_quo_site_contact "Name of Customer",
ccs_quo_company_ref "Client",
ccs_quo_address#1 + ', ' + ccs_quo_address#2 + ', ' + ccs_quo_address#3 + ', ' + ccs_quo_address#4 + ', ' + ccs_quo_address#5 + ', ' + ccs_quo_address#6 "Address",
ccs_qos_quoted_price#6 "Cost"
from axis_europe.dbo.ccs_quotes q
inner join axis_europe.dbo.ccs_q_sections qs
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
inner join axis_europe.dbo.ccs_site_meeting m
on m.ccs_mee_quote_number = q.ccs_quo_quote_number
inner join axis_europe.dbo.ccs_notes n
on q.ccs_quo_note_number = n.ccs_not_number'

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near ' + '.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-03 : 07:18:29

[code]@query = 'select distinct top 100 ccs_qos_item_ref "SOR Code",
ccs_mee_oper_name "Operative",
ccs_quo_user_date#1 "Date", -- current datetime from jobcompletedUpdate
ccs_qos_title "Description",
ccs_not_text#1 + '' '' + '' '' + ccs_not_text#2 + '' '' + ccs_not_text#3 "Details of work carried out...",
ccs_quo_ref_number#2 "Job no.",
ccs_quo_site_contact "Name of Customer",
ccs_quo_company_ref "Client",
ccs_quo_address#1 + '', '' + ccs_quo_address#2 + '', '' + ccs_quo_address#3 + '', '' + ccs_quo_address#4 + '', '' + ccs_quo_address#5 + '', '' + ccs_quo_address#6 "Address",
ccs_qos_quoted_price#6 "Cost"
from axis_europe.dbo.ccs_quotes q
inner join axis_europe.dbo.ccs_q_sections qs
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
inner join axis_europe.dbo.ccs_site_meeting m
on m.ccs_mee_quote_number = q.ccs_quo_quote_number
inner join axis_europe.dbo.ccs_notes n
on q.ccs_quo_note_number = n.ccs_not_number'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-03 : 08:22:49

Thanks Harsh_Athalye

Perfect. For some reason I didn't want to try that, although it was very obvious. I thought it would be too easy to be the answer to add quotes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources