Solved

Setup SQL 2005 Express to email me when job completes sucessfully

Posted on 2009-04-07
5
260 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
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

831 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