June 1, 2006 at 3:17 pm
Hi all,
I have one server that is on SQL 2000 Std edition. I want to get email from server if the job fails or something else fails. But there is no SQL mail running also. How can i do that to get message for the job fail!!
Thanks
June 2, 2006 at 4:54 am
I use this to notify me of a successful job. Just call it on Job Fail in your DTS package. I use a slightly different one that I pass a parameter to no my job fail, so I call it with a different parameter depending on which part of the job failed.
Hope it works Ok for you.
CREATE PROCEDURE [dbo].[SendSuccessMail]
--Adapted from a Microsoft KnowledgeBase article Jan 16th 2006.
-- @From varchar(100) ,
-- @To varchar(100) ,
-- @Subject varchar(100)=" ",
--@Body varchar(4000) =" "
/*********************************************************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References 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 @From varchar(100) --origninally passed as parameter above. We want to hard-code it.
Declare @To varchar(100) --origninally passed as parameter above. We want to hard-code it.
Declare @Subject varchar(100) --origninally passed as parameter above. We want to hard-code it.
Declare @Body varchar(4000) --origninally passed as parameter above. We want to hard-code it.
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
Set @From = 'put the from mail address here'
Set @To = 'put the to mail address here'
Set @Subject = 'put the subject here'
Set @Body = 'put the body of the msg here'
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
IF @hr 0 BEGIN
print 'sp_OACreate failed'
END
--***************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', 'put your SMTP server here'
-- 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
IF @hr 0
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
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
send_cdosysmail_cleanup:
If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
BEGIN
EXEC @hr=sp_OADestroy @iMsg
END
ELSE
BEGIN
PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
RETURN
END
GO
June 2, 2006 at 7:42 am
getting Error:
Source: CDO.Message.1
Description: The transport failed to connect to the server.
June 2, 2006 at 2:01 pm
SQLRep,
did you put the name of your SMTP server instead of words 'put your SMTP server here' in Neil's script? This message is coming up when the script can not find SMTP server. If you did put the SMTP server name there make sure you qualified it with the domain name. Also make sure the Firewall on port 25 is open from your SQL Server to SMTP server. We do get this message with SMTP mail when the same script runs fine on our site and would not run from the computer on another site because the Firewall is not open to this SMTP server from that computer.
Regards,Yelena Varsha
June 2, 2006 at 11:04 pm
Hi all,
I am using SQLAgent mail with Outlook 2000. Configure Outlook to use internal exchange server for sending mails by setting up Operator(s).
If Outlook 2000 is out of your budget, there is an option - using Outlook express, as suggested by one of the forum topic.
Hope this help!
June 5, 2006 at 6:30 am
Yelena is spot on, it's either an incorrect server name or Port 25 not opened.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply