Solved

Setup SQL 2005 Express to email me when job completes sucessfully

Posted on 2009-04-07
5
263 Views
Last Modified: 2012-05-06
I have a couple SQL Express 2005 installs and have a backup script that runs on a daily basis without any issues.  I would like the job to email me when it finished regardless if it completes sucessfully or fails.  Is this possible without SQL mail and installing outlook?
DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200) 
SET @BackupDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\'
--Add a list of all databases you don't want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'Northwind'
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0
 
    BEGIN
    	SET @Name = @DB + '( Daily BACKUP )'
    	SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
    	SET @BackupFile = @BackupDirectory + + @DB + '_' + 'Full' + '_' + 
    		CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
    	SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
 
    	IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
    		BEGIN
    			SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' + 
    				CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
    			--SET some more pretty stuff for sql server.
    			SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
    		END	
    	ELSE
    		BEGIN
    			SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' + 
    				CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
    			--SET some more pretty stuff for sql server.
    			SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
    		END
    		BACKUP DATABASE @DB TO DISK = @BackupFile 
    		WITH NAME = @Name, DESCRIPTION = @Description , 
    		MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description , 
    		STATS = 10
    	FETCH next FROM Database_CURSOR INTO @DB
 
EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup',N'bak',N'2008-09-16T12:40:57' 
 
 
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

Open in new window

0
Comment
Question by:HNMIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24095549
Can try using CDO...   and Office Automation procedures





Declare @OA_Status int
Declare @MailID int
Declare @SenderAddress varchar(100)
Declare @RecipientAddress varchar(100)
Declare @Subject varchar(200)
Declare @Body varchar(8000)
declare @CC varchar(100)
declare @BCC varchar(100)
 
set @SenderAddress='mark.wills@somewhere.else' 
set @RecipientAddress = 'mark.wills@somewhere.else' 
set @Subject='SQL Mail Test'
set @body='Hello, this is a test email from SQL Server'
set @CC  = null
set @BCC  = null
 
EXEC @OA_Status = sp_OACreate 'CDO.Message', @MailID OUT
EXEC @OA_Status = sp_OASetProperty @MailID, 'From',@senderaddress
EXEC @OA_Status = sp_OASetProperty @MailID, 'TextBody', @Body
EXEC @OA_Status = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @OA_Status = sp_OASetProperty @MailID, 'CC', @CC
EXEC @OA_Status = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @OA_Status = sp_OASetProperty @MailID, 'To', @recipientAddress
EXEC @OA_Status = sp_OAMethod @MailID, 'Send', NULL
EXEC @OA_Status = sp_OADestroy @MailID

Open in new window

0
 

Author Comment

by:HNMIS
ID: 24096772
ok, I ran the above script and the command completes successfully but I never recieve an email.  How do I tell it to use a specific exchange server?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24098515
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 100 total points
ID: 24101820
You may want to look at xp_smtp_sendmail -- open source app. It was designed back for SQL 7 and 2000, but will still work with 2005. You just have to change the path.

-----------------------------------------
XPSMTP provides a SMTP based SQL Mail solution for sending MIME based email over SMTP, implemented as an Extended Stored Procedure.

XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
-----------------------------------------
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24101967
That does work well... Used to use it some time back, and had almost forgotten all about it.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question