2 Scripts for enabling mail sending from sql server, not working in 2005
-
Hi, I have set up a trigger and a stored procedure that sends mail when a job has failed. This works fine in Ms SQL 2000, but i receive no mails from Ms SQL 2005. Can someone see if there is any errors that will not work in 2005 in these scripts?
Script 1, The stored procedure:
USE MSDB
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_SQLTriggerEmail' AND type = 'P')
DROP PROCEDURE sp_SQLTriggerEmail
GO
CREATE PROCEDURE [dbo].[sp_SQLTriggerEmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) = "SQL Mail frå triggeren trg_stepfailures for tabellen sysjobhistory i msdb-basen - Svein Erik og Britt Randi -kode"
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '10.110.100.7' -- her skal
-- alias eller ipadresse for epost-server som skal brukast inn
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
PRINT 'Mail Sent!'
GO
-- END ---------------------------------------------------
Script 2, The trigger:
USE MSDB
GO
-- Sjekker om triggeren allerede finnes, om den gjer det, så sletter vi den før den nye opprettes
if exists (select * from dbo.sysobjects where id = object_id(N'[trg_stepfailures]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [trg_stepfailures]
GO
CREATE trigger trg_stepfailures
on sysjobhistory -- trigger for tabellen sysjobhistory
for insert -- insert => trigger køyrer kvar gang ny rad vert lagt
-- til i tabellen sysjobhistory
as
declare @strcmd varchar(1000),@strRecipient varchar(500),@strMsg varchar(3000), @subject varchar(100), @NewLine varchar(2)
set @strRecipient = 'ses@ourdomain.no;brm@ourdomain.no' --mottakarar av evt epost
set @NewLine = char(10) -- linjeskift (brukt i @message, dvs Body-feltet)
-- job and step has failed:
if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')
begin
select @strMsg = 'Info om steg og jobb som feila: ' + @NewLine + @NewLine +
'Server: ' + @@servername + @NewLine +
'Jobb: ' + sysjobs.name +char(13) + @NewLine +
'Steg: ' + inserted.step_name +char(13) + @NewLine + @NewLine +
'Feilmelding: ' + @NewLine +
inserted.message
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0
select @subject = 'Eit steg i følgande jobb har feila: ' + sysjobs.name from inserted join sysjobs on inserted.job_id = sysjobs.job_id where inserted.run_status = 0
exec msdb.dbo.sp_SQLTriggerEmail @From = 'sqlmail@ourdomain.no',
@To = @strRecipient,
@Subject = @subject,
@Body = @strMsg
end
-
Hello,
These both work fine for me in 2005 standard. Looks like a handy proc.
Make sure you check "Enable OLE Automation" in SQL Server Surface Area Configuration for Features.
Also, make sure that you can connect to the server you are using to send out the mail (in this case 10.110.100.7) from the SQL Server machine on port 25.
On a side note - stay away from the sp_ prefix for stored procedures... Using it can lead to trouble especially if there is a proc of the same name in the master db.
Sincerely,
Dan B
-
Ah! Now I remember, i totally forgot to check "Enable OLE Automatation"! Thank you very much for your help!
I will make a new name for the sp as well, thanks!
Svein Erik.
-
Hi
I am getting an error "Failed at sp_OASetProperty sendusing"
did i miss something?
-
Why would you go to all this trouble? Just setup Database Mail instead it uses SMTP.
--------------------
Colt 45 - the original point and click interface
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply