I'm able to send an email from query analyzer with this:

master..xp_sendmail
@recipients = 'jj297 (AT) yahoo (DOT) com',
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it'

What I want to do is add a parameter so I've tried this but getting
Line 3: Incorrect syntax near '='. (the @recipients line)

master..xp_sendmail
declare @requestoremail varchar(75),
@recipients = 'jj297 (AT) yahoo (DOT) com',
@subject = 'Over Due Resource Library Item',
@message = 'Your item is overdue please return it',
@query = 'select Libraryrequest.LoanRequestID, Titles.Title,
requestors.requestoremail,
libraryrequest.requestdate,libraryrequest.shipdate ,libraryrequest.duedate
from libraryrequest
join requestors on requestors.requestorid=libraryrequest.requestorid
join Titles on Titles.Titleid = Libraryrequest.titleid
where duedate < getdate() and requestoremail='@requestoremail''

Can someone tell me what I'm doing wrong. Thanks.

JJ297 (nc297 (AT) yahoo (DOT) com) writes:
So you have on line 1:

That's a call to a stored procedure. (Since SQL Server assumes a missing
EXEC if a batch starts with an identifier.

Here you declare a variable, and thus you terminate the EXEC statement
on the line above. Your line ends with a comma, which tells us to
expect a declaration on the next line as well. But this line goes:

Does not look like a variable declaration above, does it?

If you remove the DECLARE statement, this will however be part of that
EXEC statement, and be legal syntax.

I have never used xp_sendmail, but it does not seem that it the query
can be parameterised, but you would have to interpolate the parameter
into the query string:

where duedate < getdate() and requestoremail= +
quotename(@requestoremail, '''')

And you would have to put the query string in a variable. Which you should
declare before you call xp_sendmail.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx