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

master..xp_sendmail
@recipients = 'jj297@yahoo.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@yahoo.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@yahoo.com) writes:
> I'm able to send an email from query analyzer with this:
>
> master..xp_sendmail
> @recipients = 'jj297@yahoo.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@yahoo.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.

So you have on line 1:

> master..xp_sendmail

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

> declare @requestoremail varchar(75),

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:

> @recipients = 'jj297@yahoo.com',

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@sommarskog.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