SQLServerCentral / SQL Server 2005 / SQL Server 2005 Integration Services / Email Automation / Latest Posts

SQLServerCentral / SQL Server 2005 / SQL Server 2005 Integration Services / Email Automation / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 00:46:42 GMT20RE: Email Automationhttp://www.sqlservercentral.com/Forums/Topic921156-148-1.aspxHere is a script I use that you can get the concept from. It sends out a nicely formated html e-mail.While I have made quite a few modifications to it I do not take credit for the script as I got this from a web site all though I do not recall were. This runs as a job and sends out notification of any errors in the sql server errorlog.[code="sql"]DECLARE @Error_Message varchar(75)DECLARE @Error_Date datetimeDECLARE @SPID varchar(15)DECLARE @Cnt1 intDECLARE @Row_Count intDECLARE @MxDate DatetimeDECLARE @ErrorMsg_Message varchar(255)DECLARE @TableHTML varchar(8000) DECLARE @StrSubject VARCHAR(100) DECLARE @Oriserver VARCHAR(100)DECLARE @Version VARCHAR(250)DECLARE @Edition VARCHAR(100)DECLARE @ISClustered VARCHAR(100)DECLARE @SP VARCHAR(100)DECLARE @ServerCollation VARCHAR(100)DECLARE @SingleUser VARCHAR(5)DECLARE @LicenseType VARCHAR(100)DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMEDECLARE @Cnt intDECLARE @URL varchar(1000)DECLARE @Str varchar(1000)SELECT @Version = @@versionSELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + '-' + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + '-01' AS DATETIME)SELECT @StartDate = @StartDate - 1SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME) SET @Cnt = 0IF serverproperty('IsClustered') = 0 BEGIN SELECT @ISClustered = 'No'ENDELSEBEGIN SELECT @ISClustered = 'YES'ENDSELECT @SP = CONVERT(VARCHAR(100), SERVERPROPERTY ('productlevel'))SELECT @ServerCollation = CONVERT(VARCHAR(100), SERVERPROPERTY ('Collation')) SELECT @LicenseType = CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType')) SELECT @SingleUser = CASE SERVERPROPERTY ('IsSingleUser') WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE 'null' ENDSELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) SELECT @strSubject = 'DB Server Daily Error Log Checks ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')' CREATE TABLE ##Errors1 (ERRORLOG varchar(8000),continuationRow varchar(5))INSERT into ##Errors1 (ERRORLOG,continuationRow)Exec master..xp_readerrorlog CREATE TABLE ##Errors (LogDate DateTime,ProcessInfo varchar(15),errortxt varchar(4000),[text] varchar(4000))INSERT into ##Errors (LogDate,ProcessInfo,errortxt)select substring(ERRORLOG,0,23) as LogDate, substring(ERRORLOG,24,8) as ProcessInfo, substring(ERRORLOG,34,Len(ERRORLOG)-34) as [Text]from ##Errors1where ERRORLOG LIKE '%Error:%' AND ERRORLOG NOT LIKE '%15457%' AND ERRORLOG NOT LIKE '%1073759%'Update ##Errorsset Text= (select description from master..sysmessages where error=Substring(errortxt,charindex('Error:',errortxt)+6,(charindex(',',errortxt)-1)-(charindex('Error:',errortxt)+6)+1) and msglangid=1033)WHERE errortxt LIKE '%Error:%' AND errortxt NOT LIKE '%15457%' AND errortxt NOT LIKE '%1073759%'AND errortxt NOT LIKE '%0x%'SELECT @Row_Count = count(*) FROM ##Errors where logDate>GetDate()-1If @Row_Count<>0BEGINSET @TableHTML = 'Server Info <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50"> <tr> <td width="39%" height="22" bgcolor="#000080"> Server Name</td> </tr> <tr> <td width="39%" height="27">' + @OriServer +'</td> </tr> </table> <table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1"> <tr> <td width="50%" bgColor="#000080" height="15"> Version</td> <td width="17%" bgColor="#000080" height="15"> Edition</td> <td width="18%" bgColor="#000080" height="15"> Service Pack</td> <td width="93%" bgColor="#000080" height="15"> Collation</td> <td width="93%" bgColor="#000080" height="15"> LicenseType</td> <td width="30%" bgColor="#000080" height="15"> SingleUser</td> <td width="93%" bgColor="#000080" height="15">Clustered</td> </tr> <tr> <td width="50%" height="27">'+@version +'</td> <td width="17%" height="27">'+@edition+'</td> <td width="18%" height="27">'+@SP+'</td> <td width="17%" height="27">'+@ServerCollation+'</td> <td width="25%" height="27">'+@LicenseType+'</td> <td width="25%" height="27">'+@SingleUser+'</td> <td width="93%" height="27">'+@isclustered+'</td> </tr> </table> 'SELECT @TableHTML = @TableHTML + '</table> Error Log<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1"> <tr> <td width="15%" bgColor="#000080" height="15"> Error Date</td> <td width="18%" bgColor="#000080" height="15"> Process Info</td> <td width="50%" bgColor="#000080" height="15"> Error Message</td> </tr>'SELECT @TableHTML = @TableHTML + '<tr><td width="15%" height="15">'+CONVERT(varchar(25),LogDate)+'</td>'+'<td width="18%" height="27">'+ProcessInfo+'</td>'+'<td width="50%" height="27">'+Text+'</td></tr>'from ##ErrorsWHERE Text LIKE '%Error:%' ANDText NOT LIKE '%15457%' AND Text NOT LIKE '%1073759%'and logDate>GetDate()-1DECLARE @From varchar(50)DECLARE @To varchar(50)DECLARE @CC varchar(50)DECLARE @Subject as varchar(200)DECLARE @vcBody varchar(8000)DECLARE @CurrDate datetimeDECLARE @MailServerName VARCHAR(100)DECLARE @BodyType varchar(100)DECLARE @iMsg intDECLARE @hr intDECLARE @source varchar(255)DECLARE @description varchar(500)DECLARE @output varchar(1000)SET @From = 'Bud@weiser.com'SET @To = 'Billy@goat.com'--SET @CC = 'cc@yahoo.com'SET @MailServerName = 'vrtintex02'SET @Subject = @strSubjectSET @BodyType ='HTMLBODY'SET @vcBody = @TableHTMLEXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUTEXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'-- Replace MailServerName by the name or IP of your SMTP Server.EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @MailServerName EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', nullEXEC @hr = sp_OASetProperty @iMsg, 'To', @ToEXEC @hr = sp_OASetProperty @iMsg, 'From', @FromEXEC @hr = sp_OASetProperty @iMsg, 'CC', @CCEXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.EXEC @hr = sp_OASetProperty @iMsg, @BodyType , @vcBodyEXEC @hr = sp_OAMethod @iMsg, 'Send', NULL--PRINT @hr-- Sample error handling.IF @hr <>0 BEGIN select @hr EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN ENDEND-- Do some error handling after each step if you need to.-- Clean up the objects created.EXEC @hr = sp_OADestroy @iMsgENDDrop table ##ErrorsDrop table ##Errors1[/code]Thu, 13 May 2010 10:02:49 GMTDan.HumphriesRE: Email Automationhttp://www.sqlservercentral.com/Forums/Topic921156-148-1.aspxYou can use database mail to send emails from the database.You can use SQL Agent to schedule jobs.However, the ease of making all this possible is dependent upon what you want to send and how easy it is to get that information.CEWIIThu, 13 May 2010 09:26:06 GMTElliott WhitlowEmail Automationhttp://www.sqlservercentral.com/Forums/Topic921156-148-1.aspxI am trying to automate Email form SQL Server .I am a newbie to DB activities .Actually i am using a Testing Tool connected to SQL Server .I want to mail the results to the management at frequent intervals ;is there any way through which it can be automated .Else in simple words i just want to send an automated email at scheduled hours .Thanks in Advance .Thu, 13 May 2010 05:43:55 GMTarunmoulic