Solved

Setup SQL 2005 Express to email me when job completes sucessfully

Posted on 2009-04-07
5
256 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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 100 total points
Comment Utility
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
Comment Utility
That does work well... Used to use it some time back, and had almost forgotten all about it.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Return rows that match the first row 3 38
Trigger for audit 26 48
Sql query 12 65
Sql Permission 6 42
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now