CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
B Woods
B Woods

Reputation: 580

SQL Server Stored Procedure to Send Email

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

Upvotes: 8

Views: 45038

Answers (4)

Kamal Pratap
Kamal Pratap

Reputation: 177

From my blog post, Create a Database Mail Configuration Using T-SQL Script, published 05 Feb 2018:

--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestProfile', 
@recipients = 'To Email Here', 
@copy_recipients ='CC Email Here',             --For CC Email if exists
@blind_copy_recipients= 'BCC Email Here',      --For BCC Email if exists
@subject = 'Mail Subject Here', 
@body = 'Mail Body Here',
@body_format='HTML',
@importance ='HIGH',
@file_attachments='C:\Test.pdf';               --For Attachments if exists

Upvotes: 0

MaxiWheat
MaxiWheat

Reputation: 6251

There's a comma missing in your parameters :

@Body varchar(8000), ---- HERE
@MailServer varchar(100) = 'localhost'

Upvotes: 2

JNK
JNK

Reputation: 65147

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'

Upvotes: 13

Remus Rusanu
Remus Rusanu

Reputation: 294177

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.

Upvotes: 12

Related Questions