Wacko SMTP email with ActiveX

Question

Wacko SMTP email with ActiveX

Jeff Moden

SSC Guru

Points: 1004434
More actions
December 11, 2008 at 9:24 am

#195019

Ack... I'm not even sure which forum to post this question on because it's way outside what I normally do. This is definitely for SQL Server 2000, though.
We have to "follow corporate rules" for setting up email on SQL Server... MAPI is out of the question for them and we have the added complexity of having to send emails to Lotus Notes. We cannot use the normal "notifications" that are usually allowed for scheduled jobs. I know... don't ask... it's a battle that many have fought for this company and have lost despite all reasoning. :sick:
To make a much longer story shorter, the "experts" have contrived a method for adding "failure" steps to our SQL Server Agent jobs to provide the failure notification. They are in the form of Active-X scripts like the following (this is an actual script but I've changed the IP address and email addresses to protect the server) and they actually do work...
Function Main()
Main = DTSTaskExecResult_Fail
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "somename@somecompany.com"
objEmail.To = "someothername@somecompany.com"
objEmail.Subject = "FAILED: some name of a scheduled job goes here"
objEmail.Textbody = "The scheduled job failed."
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "xxx.xxx.xxx.xxx"
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
set objEmail = nothing
End Function
What I'd like to do (I believe) is to make it possible to run this type of thing from a stored procedure. My understanding is that I just need to convert it to use the sp_OA extended stored procs... and I've never written anything original using sp_OA before. I've cleaned up lots of other people's code, but I've never written a conversion from a VB script to an sp_OA OLE Automation object before.
I'd really appreciate it if someone could show me how to convert this bad boy... and, of course, if you have a better suggestion that doesn't include launching high velocity pork chops at the local experts to accomplish the same thing via a stored procedure, I'm open to learning something new. 🙂
Thanks for the help folks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

BWAA-HAAA!!! Sorry for the previous dumb question folks... didn't realize it would be this easy. Thought I'd share my results... of course, the email and IP addresses have been changed to protect my particular bailiwick...

DECLARE @objEmail INT

EXEC dbo.sp_OACreate 'CDO.Message', @objEmail OUT

EXEC dbo.sp_OASetProperty @objEmail, 'From', 'someemail@somecompany.com'

EXEC dbo.sp_OASetProperty @objEmail, 'To' , 'someemail@somecompany.com'

EXEC dbo.sp_OASetProperty @objEmail, 'Subject', 'put subject here'

EXEC dbo.sp_OASetProperty @objEmail, 'Textbody', 'put email message here'

EXEC dbo.sp_OASetProperty @objEmail, 'Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing")',2

EXEC dbo.sp_OASetProperty @objEmail, 'Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver")','xxx.xxx.xxx.xxx'

EXEC dbo.sp_OASetProperty @objEmail, 'Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport")',25

EXEC dbo.sp_OAMethod @objEmail, 'Configuration.Fields.Update'

EXEC dbo.sp_OAMethod @objEmail, 'Send'

EXEC dbo.sp_OADestroy @objEmail

Lesson learned: OLE automation is 1 for 1 with VB Scripts (for the most part).

Programming method used: Ancient art of "Must Look Eye!" 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Overachiever IMO. You even answer your own questions!

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

Heh... well, someone had to answer it! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Jeff Moden (12/11/2008)
Heh... well, someone had to answer it! 😛

Give the world a chance!

:alien:

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

Bugger... now all I have to do is figure out why the ActiveX script on both servers work but the equivalent sp_OA based sproc doesn't work on one of the servers. Must be disabled somehow, but I'm not getting any error messages.

Anyone have any suggestions on this little problem?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)