Sending Mail without Outlook or MAPI
-
Has anyone been able to get SQL Server to send mail without using MAPI? I have downloaded a utility from sqldev.com and installed it ok. It then instructed me to download another utility from dbmaint.com to enable sending SQL Agent Alerts and job notifications messages through SMTP mail. We use Lotus Notes as our mail client and have not been able to get SQL Server to send mail via SMTP....has anyone figured anything out...any help would be appreciated! THANKS!
-
All you need to have is an email server which will allow open relays from the server which you are attempting to send the mail from. Check the settings on the mail server to see if it allows this, if not it will need to be enabled (make sure that you use a filter to only allow open relay from the SQL Server IP address). This should allow you to send emails using xp_smtpmail. I use it myself and it's a great alternative to SQL Mail when you don't have an exchange server in your environment.
-
You can also have the SMTP service running on the local host and bounce off there to your notes server.
-
The xp_smtpmail utility does work, I mean it sends mail just fine. The problem I am having is getting it to send mail on any errors or alerts that may arise. SQLDEV suggests installing another untility from dbmaint that will allow this to happen automatically, but I cannot seem to get the utility to work...this is the error that I get...
Any suggestions? THANKS a lot!
-
I was having the same problem until someone told me that they were using a trigger on the msdb..sysjobhistory table to accomplish the failure notifications, I implemented it myself and it works great...it give the job name in the subject and the error message in the body of the email.
The script below is the trigger, it calls a proc that invokes the xp_smtpmail dll, you could change it to make the call directly...the proc is all the way at the bottom...
USE MSDB
GO
/* drop trigger trg_stepfailures */
CREATE trigger trg_stepfailures
on sysjobhistory
for insert
as
declare @strcmd varchar(1000),@strRecipient varchar(500),@strMsg varchar(3000), @subject varchar(100)
set @strRecipient = 'EMAIL ADDY GOES HERE'
if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')
begin
select @strMsg = @@servername + ' Job ' + sysjobs.name +char(13) + ' Step ' +
inserted.step_name +char(13)+ ' Message ' + inserted.message
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0
select @subject = 'Job ' + sysjobs.name + ' Failed' from inserted join sysjobs on inserted.job_id = sysjobs.job_id where inserted.run_status = 0
-- raiserror (@strMsg, 16, 10) with log
exec master.dbo.usp_sendmail @recipients = @strRecipient,
@msgtext = @strMsg,
@mailsubject = @subject
GO
USE MASTER
GO
CREATE PROCEDURE USP_Sendmail
@mailsubject varchar(75),
@msgtext varchar(2000),
@attach varchar(300) = null,
@recipients varchar(2000) = null
AS
DECLARE @rc int
--rc just gives us a return code of 1 - failure or 0 - success
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = N'MACHINE NAME HERE', --the machine the email is coming from
@FROM_NAME = N'SQL Distribution Server', --the machine again
@TO = @recipients, --the recipient list
@CC = N'', --cc should we need them
@BCC = N'', --bcc should we need them
@Subject = @mailsubject, --subject line (pulled as a variable from the job)
@message = @msgtext, --message (pulled as variable from the job)
@server = N'SERVER IP GOES HERE', --relay server ip address
@attachments = @attach, --file location
@attachment = N'', --multiple files ; delimited
@priority = N'' --default = normal
GO
-
Hi All,
I am also trying to use xp_smtpmail dll to send mail from sql server, however not able to.We have lotus notes as our email client. Can any of you guide me to the info page where I can get information as how to set this on the sql server box to be able to send mail from sql server. I already placed the dll in the Binn directly of the sql server under program files and have registered the dll using the xpsmtp80.dll using the exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp70.dll' command query analyzer. Now when calling the xp_smtp_sendmail procedure, I am not sure what to pass as the @server parameter which is expected by this proc. Any help would be appreciated.
Thanks
-
If I understand correct: This can be used to send notification mails based on job failures and success'?
How do I invoke the mail sending from my jobs? I guess it's the last procedure (EXEC @rc...) that needs to be called from the job? How can i do that?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply