?
Solved

Setup SQL 2005 Express to email me when job completes sucessfully

Posted on 2009-04-07
5
Medium Priority
?
290 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 400 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard 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.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 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