xp_sendmail statement - 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 statement

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 07:37:43

I need to send to send an email and part of it will be the outcome of a SQL statement. The @message will be passed as a SELECT statement but the message that is sent must be the result of this statement. While other parameters have been just strings:

@recipients = 'chicken@isp.com',
@subject = 'Whatever'

how do I put the SQL to run into the right syntax for

@message = SELECT * from <table> ? How do I enclose it?

I got an error saying 128 being the max length - is this the statement or the result?

Thanks

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 07:55:38

This is it:

-- Supervisor to validate SOR Codes
exec xp_sendmail
@recipients = 'michael.bird@axiseurope.com',
@subject = 'Please verify these SOR Codes',
@query = select distinct ccs_qos_item_ref "SOR Code"
from [ken-sql-002].ConstructMobile.dbo.ccs_q_sections qs
inner join [ken-sql-002].ConstructMobile.dbo.ccs_quotes q
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
where q.ccs_quo_analysis_code = 'PENDING'

I get

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'select'.

But if I wrap the statement in quotes '' I get

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'PENDING'.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-27 : 08:01:10

[code]exec xp_sendmail
@recipients = 'michael.bird@axiseurope.com',
@subject = 'Please verify these SOR Codes',
@query = 'select distinct ccs_qos_item_ref "SOR Code"
from [ken-sql-002].ConstructMobile.dbo.ccs_q_sections qs
inner join [ken-sql-002].ConstructMobile.dbo.ccs_quotes q
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
where q.ccs_quo_analysis_code = ''PENDING'''[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 08:40:43

A great simple fix on the quote depth.

The remaining problem is that xp_sendmail can't be found. It's in MASTER and I've run it and sent myself messages. When running the way you've suggested I get

Server: Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'xp_sendmail'.

Can't see why. This procedure is an extended stored procedure. What can I do?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-27 : 08:53:06

[code]exec master..xp_sendmail ....[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources