DTS package to send alert e-mails
-
A stored procedure that sends CDOSYS SMTP mail as alert notifications is
not working on a new server. It fails on the sp_OASetProperty 'To'. We
can successfully send e-mail using an ASP script though, so I am trying
to setup a DTS package to send alerts to the DBA when replication fails.
I have created a package and it seems to work, but I want to format my
error messages now and would like to use the agent tokens if possible.
How can I format the body of my e-mail to use the tokens? For instance,
the stored procedure formats the body of the e-mail like this:
@Body = "Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG]
Check the [SRVR] SQL Server ErrorLog and the Application event log on the server for additional details"
Can I do this in the ActiveX Script of a DTS package? If not, is there
a way to pass this information to the ActiveX Script step?
TIA,
Michelle
TIA,
Michelle
-
What server are you running this on? How are you creating the CDO Message?
SQL Agent tokens are only relevant for SQL Agent Alerts. Also, I think you're complicating things using a DTS package.
We have alerts setup to execute a seperate SQL Agent Job. This job uses the tokens to build an error message and sends it using xp_smtp_sendmail from http://www.sqldev.net
--------------------
Colt 45 - the original point and click interface
-
Hi Phill,
Thank you for your response. Actually, we have our alerts setup similarly on our other servers. The alerts execute a sql server job that sends a formatted e-mail with the agent tokens using a stored procedure that send SMTP mail using CDOSYS. Unfortunately, this is not working on this server. It was originally, but stopped working all of a sudden and I can't figure out why. In trying to debug the problem it seems the procedure is failing on executing the sp_OASetProperty 'To' step, with this error:
Executed as user: XWCA\upsfsqlsvc. Description: Exception 0xc0000005 was generated at address 0x0850f264 [SQLSTATE 01000]
I am not giving up on trying to figure out why it is failing, but in the meantime would like to receive an alert when replication fails. Here is the DTS package I created to send the e-mail, I would just like to be able to add the agent tokens if possible. Can I pass them to the package as global variables?
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim Errmsg, AdCn, SQL1
Set AdCn = CreateObject("ADODB.Connection")
AdCn.Open = "Provider=SQLOLEDB.1; Initial Catalog=master; Data Source=servername; Integrated Security=SSPI;"
Errmsg = "Replication failed on 'SERVERNAME', attempting restart."
SQL1 = "exec msdb.dbo.sp_start_job 'SERVERNAME-DBNAME-XXX-2'"
AdCn.execute SQL1
If Err.Number <> 0 Then
Errmsg = Errmsg & " Restart failed."
Else
Errmsg = Errmsg & " Restart successful."
End If
Call EmailReport(Errmsg)
Main = DTSTaskExecResult_Success
End Function
Function EmailReport(message)
Set objCDOSYS = CreateObject("CDO.Message")
'With objCDOSYS
objCDOSYS.To = "alert.sql@ups-scs.com"
objCDOSYS.From = "admin.sql@ups-scs.com"
objCDOSYS.Subject = "DOCGEN Replication Failure"
objCDOSYS.TextBody = message
'This section provides the configuration information for the remote SMTP server.
'Normally you will only change the server name or IP.
objCDOSYS.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objCDOSYS.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "IP address"
objCDOSYS.Configuration.Fields.Update
'End remote SMTP server configuration section==
objCDOSYS.Send
'End With
Set objCDOSYS = Nothing
End Function
Believe me, I would much rather use the stored procedure that has been working on all our other servers. I have opened threads on both the stored procedure problem and also on a replication problem we have been having since moving to this new server which is in a DMZ. I haven't gotten any feedback on the other two threads and my research and debugging haven't been very fruitful, so I need something to cover us for now. I will take a look at the xp_smtp_sendmail, but if it is using the sp_OA* procedures I don't think it will work for me. Thanks again for your reply.
Michelle
-
Michelle, as I said in my earlier post I think you're unesscessarily complicating things by running a DTS package. Also, the sp_OA* procedures are know for there quirks and can have large memory leaks.
If you must go this way, then yes you could pass in the values from the tokens as global variables. It'd be very messy though. you'd end up with something like,
DTSRun /S "<yourserver>" /N "<yourpackagename>" /W "0" /E /A "errorcode":"8"="[A-ERR]" /A "severity":"8"="[A-SEV]" etc... etc...
I strongly recommend you take a good look at xp_smtp_sendmail. Not just for this server but for all the servers. The peace of mind is worth it. We've been running it on our servers for years without any problems at all. If you run a smtp server on the SQL Server, as part of IIS, then you just send the emails to localhost and they're past through to your smarthost.
From the http://www.sqldev.net website,
"It does not require any software to be installed, just a SMTP email server that can handle the outgoing mail request. XPSMTP is using TCP/IP sockets to communicate to port 25. XPSMTP does not spawn additional threads, the xp_smtp_sendmail request is handled on the same thread as it is being called on by SQL Server. Each call to xp_smtp_sendmail establishes a connection to the SMTP server and disconnects when done sending the email. The connection is created using asynchronous communication and aborts based on a timeout value (@timeout which by default is 10000 milliseconds, so 10 seconds)."
--------------------
Colt 45 - the original point and click interface -
Phill,
I agree that I am probably making it more complicated than it needs to be, but I am just trying to find something that will work on this server that has been driving me crazy
. You're suggestion of xp_smtp_sendmail may be the option I have been looking for. If we need IIS and SMTP services installed on the server though, that may be another issue. Here is the stored procedure we use to send e-mail alerts from all our other servers without a problem, and that had been working on the server in question for about a week, before it just stopped working. The only thing I know that changed for sure since it was working is that BUILTIN\Adminstrators had been removed. I tried executing the procedure under different accounts though that all have system administrator access, but with no luck. CREATE PROCEDURE [dbo].[usp_send_cdosysmail]
(
@From varchar(100),
@To varchar(100),
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
)
/******************************************
This stored procedure takes the parameters and sends
an e-mail. All the mail configurations are hard-coded
in the stored procedure. Comments are added to the
stored procedure where necessary. References to the
CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp
?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
*******************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--***** Create the CDO.Message Object *****
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp
-- ?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields ("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing"
.Value'">http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver"
.Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','IP Address'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <> 0
SELECT @hr
BEGIN
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
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
Do you have any idea why this stored procedure would just stop working all of a sudden? I will check on xp_smtp_sendmail and see if we can use it.
Thanks again,
Michelle
-
We had piles of problems with SQLMail and our SQL folks don't want to enable xp_smtp_sendmail so we wrote a DTS package that has one ActiveX component in it to send a mail message. All the fields are filled in from Global Variables. We then execute this package in other packages using the Execute Package task and setting the appropriate Global Variables.
The ActiveX code is:
Function Main()
Set objMessage = CreateObject("CDO.Message")
objMessage.Sender = DTSGlobalVariables("gvFrom").Value
objMessage.To = DTSGlobalVariables("gvTo").Value
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DTSGlobalVariables("gvSMTPServer").Value
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
objMessage.Subject = DTSGlobalVariables("gvSubject").Value
objMessage.HTMLBody = DTSGlobalVariables("gvMessage").Value
objMessage.Send
Main = DTSTaskExecResult_Success
End Function
You could easily modify this to your standard format and to include attachments if you desired.
-
Thank you so much for the reply, this is exactly what I want to do. The only global variable I want to define is the message though and I want it to include this information:
Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG]
I have not used global variables very much though and can't seem to get it to work. Can you give me some assistance on how I can pass this information as a global variable to my DTS package?
Phill, I have installed the xp_smtp_sendmail on one of our development servers that is running the same OS, windows 2003, but it doesn't seem to be working. When I use xp_smtp_sendmail to send a test message or ping the smtp server it returns RC = 0, but I don't receive any mail. Does xp_smtp_sendmail require the IIS and SMTP services be installed?
Thanks,
Michelle
-
In your package properties you define new Global Variables. If you don't know how to do that I suggest you Google the topic or check a good SQL DTS book.
Your code would probably look something like:
- Function Main()
Dim msg
- Set objMessage = CreateObject("CDO.Message")
objMessage.Sender = "your From address"
objMessage.To = "Your To Address"
- objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DTSGlobalVariables("gvSMTPServer").Value
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
- objMessage.Subject = "Your Subject"
- msg="Error: ["&DTSGlobalVariables("gvAErr").Value&"]"&chr(10)&chr(13)
- msg=msg&"Severity: ["&DTSGlobalVariables("gvSev").Value&"]"&chr(10)&chr(13)
- .... etc through
- msg=msg&"Message: ["&DTSGlobalVariables("gvmsg").Value&"]"&chr(10)&chr(13)
objMessage.TextBody = msg
objMessage.Send
Main = DTSTaskExecResult_Success
End Function
In the calling package you have an Execute DTS Package step that executes your Notification package and in that step you set the Inner Package Variables to the values you want to pass to your Notification (Email) package. If those values are variable (i.e. the output of a previous step) you can use the Dynamic Properties task to set the Inner Package Variables of the Execute DTS Package step.
Sounds a little daunting, but try just building the Notification package first, then a simple DTS job that Executes the Notification package with hardcoded Inner Package Variables to get your feet wet. Build up from there.
- Function Main()
-
Michelle, you don't need IIS setup on the machine, that was just an example. All you need to provide is a valid smtp server as a parameter to the stored procedure. The removal o
rschaeferhig , I'd be interested in the reasoning behind your SQL folks not wanting to use xp_smtp_sendmail. We had a few DTS packages setup using CDONTS to send emails on Windows 2000. When we upgraded to Windows 2003 we had to edit each of those packages because CDONTS doesn't work for Windows 2003.
Conversely, all the processes that used xp_smtp_sendmail continued to work without any problems. We've been running it on all our servers for years and upgrades to the O/S and to our Exchange Server have not caused any problems.
--------------------
Colt 45 - the original point and click interface -
Michelle, Here's an example of what the job step could look like with xp_smtp_sendmail.
DECLARE @Subj varchar(100), @Msg varchar(8000), @Srvr varchar(100)
SET @Srvr = '[SRVR]' SET @Subj = 'Severity [A-SEV] Alert on [SRVR]'
SET @Msg = 'Code: [A-ERR]' + CHAR(10) SET @Msg = @Msg + 'Date: [STRTDT], Time: [STRTTM]' + CHAR(10) SET @Msg = @Msg + 'Database: [A-DBN]' + CHAR(10) SET @Msg = @Msg + 'Message: ' + REPLACE("[A-MSG]", '''','') + CHAR(10) + CHAR(10) SET @Msg = @Msg + 'Check the [SRVR] SQL Server Log and Application event log for details'-- send email message EXEC master.dbo.xp_smtp_sendmail @FROM = 'sql@somwhere.net', @MESSAGE = @Msg , @FROM_NAME = @Srvr, @SERVER = 'mysmtpserver', @TYPE = 'plain/text' , @PRIORITY = 'HIGH', @TO = 'sqlguys@somewhere.net', @SUBJECT = @Subj
At our site, we actually execute a wrapper stored procedure that extracts the email parameters, SMTP Server, From email address, To email address, etc... from a central table.
In my spare time
I'm actually working on another stored procedure that sends out a much more concise message via our SMS gateway.--------------------
Colt 45 - the original point and click interface -
Good Morning Phill,
Thanks again for your help and advice to my problem. As I said earlier I did install xp_smtp_sendmail on one of our development servers that is running the same OS, windows 2003. When I attempt to send a test message though, it runs successfully, but I don't receive any mail. IIS and SMTP services don't exist on that server. I installed xp_smtp_sendmail on another of our developemnt servers that runs Windows 2000 AS and has both IIS and SMTP services running and sending a test message works from there. That is why I asked whether IIS and SMTP services were required to use it. If they're not, than can you give me any ideas on why it wouldn't be working on the other server?
TIA,
Michelle
-
I believe xp_smtp_sendmail requires SMTP server. I don't think it requires IIS.
Our SQL folks are very picky about enabling extended SP's. I think xp_smtp_sendmail has prerequisite xSP's they don't like. I don't remember the exact reason. It becomes a moot point with SQL2005 anyway, they've already agreed that all the mail functions will be enabled when we upgrade.
The whole point of having a single Notification package that is driven entirely by global variables is that when you need to change something you can do it mostly through the global variables. We had to change the SMTP server name and only had to edit the Notification package to change the server name coded as a global variable in that package. Had it been written prior to CDOSYS (using CDONTS) we'd only have had to rewrite the one package and all our emails would have been changed from CDONTS to CDOSYS.
-
I may be missing something here, but why not just use the "Send Mail Task" in the dts package.
Saves a LOT of messing about and is easy to use. A simple form!
------------------------------
The Users are always right - when I'm not wrong! -
Except that it requires a MAPI client and pre-defined profile.
Michelle, I've no idea why the email wouldn't have been received. Maybe a firewall/port mapping issue ??
rschaeferhig, I'm not aware of any extra requirements for xp_smtp_sendmail. It's actually pretty self contained. Personally I much prefer running something that is handled within SQL Server rather than spawning an additional process.
Haven't had much opportunity to play with Database Mail in SQL 2005, but I'm hoping at a minimum it provides the same functionality as xp_smtp_sendmail.
--------------------
Colt 45 - the original point and click interface -
Hi Michelle,
This is really very helpful .Shared with my team members.
Thanx for ur efforts and keep posting ur best.
from
Killer
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply