This is my first attempt at writing a stored procedure that emails someone. When trying to execute I get these errors:
Msg 102, Level 15, State 1, Procedure EmailTodaysErrors, Line 14
Incorrect syntax near '@MailServer'.
Msg 137, Level 15, State 2, Procedure EmailTodaysErrors, Line 26
Must declare the scalar variable "@mailserver".
Msg 137, Level 15, State 2, Procedure EmailTodaysErrors, Line 33
Must declare the scalar variable "@Body".
The code that I am using which is causing this is:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE EmailTodaysErrors
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000)
@MailServer varchar(100) = 'localhost'
AS
SET NOCOUNT ON;
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
END
set nocount off
GO
To send email with T-SQL, you need to use the sp_send_dbmail stored procedure in the msdb database. This procedure accepts many arguments, one of which is the @query argument. That's the argument that attaches the results of your query to the email.
You're missing a comma after the @body
line, which is throwing off your declarations.
Add it here:
@Body varchar(8000), -- HERE
@MailServer varchar(100) = 'localhost'
Don't use sp_oa_family to send mail, there is already a built-in solution with SQL Server: Database Mail. Simply configure Database Mail properly on your server, then call sp_send_dbmail
.
There's a comma missing in your parameters :
@Body varchar(8000), ---- HERE
@MailServer varchar(100) = 'localhost'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With