[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Setup SQL 2005 Express to email me when job completes sucessfully

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

649 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