CDOSys and Identical code

Question

CDOSys and Identical code

Jeff Moden

SSC Guru

Points: 1004434
More actions
January 6, 2009 at 1:44 pm

#71127

I have two SQL Servers... Server "A" and Server "B". The following AxtiveX script works just perfectly on both from a scheduled job... (smtpserver IP address and email addresses are ficticious here)...
Function Main()
Main = DTSTaskExecResult_Success
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "NoReply@nospam.com"
objEmail.To = "System.Admin@nospam.com"
objEmail.Subject = "Successful: CLIENT DB Compressed Backup NON GSK 01"
objEmail.Textbody = "The SQL job 'CLIENT DB Compressed Backup NON GSK 01' has successfully completed."
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "111.22.33.44"
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
set objEmail = nothing
End Function
So, to me, it would appear that CDOSys is setup and working just fine on both servers. I've also verified that the jobs run as the same user and the user on both machines has identical privs (system administrators role)...
When I run the following code logged in as that same user, it works on Server "A" but Server "B" produces the "[font="Arial Black"]CDO.Message.1, The "SendUsing" configuration value is invalid[/font]." on the "send" in the code below... (again... the email addresses and the IP address has been changed just for display here... I've also removed all the error code just to make it more readable here)...
--===== Declare the I/O parameters
DECLARE @From VARCHAR( 256),
@To VARCHAR(1000),
@Subject VARCHAR( 256),
@TextBody VARCHAR(8000)
--===== Declare local variables
DECLARE @objEMailID INT --OLE automation object identification for email
--===== Variable presets
SELECT @From = 'NoReply@BeyondInteraction',
@To = 'System.Admin@nospam.com',
@Subject = 'Successful: CLIENT DB Compressed Backup NON GSK 01',
@TextBody = 'The SQL job ''CLIENT DB Compressed Backup NON GSK 01'' has successfully completed.'
--===== Constuct and send the email according to the input parameters
EXEC dbo.sp_OACreate 'CDO.Message', @objEMailID OUT
EXEC dbo.sp_OASetProperty @objEMailID, 'From', @From
EXEC dbo.sp_OASetProperty @objEMailID, 'Textbody', @TextBody
EXEC dbo.sp_OASetProperty @objEMailID, 'Subject', @Subject
EXEC dbo.sp_OASetProperty @objEMailID, 'To', @To
EXEC dbo.sp_OASetProperty @objEMailID, 'Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing")',2
EXEC dbo.sp_OASetProperty @objEMailID, 'Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver")','111.22.33.44'
EXEC dbo.sp_OASetProperty @objEMailID, 'Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport")',25
EXEC dbo.sp_OAMethod @objEMailID, 'Configuration.Fields.Update'
EXEC dbo.sp_OAMethod @objEMailID, 'Send', NULL --<<<<LOOK! THIS LINE PRODUCES A CDO.Message.1, The "SendUsing" configuration value is invalid.
EXEC dbo.sp_OADestroy @objEMailID
Both machines are on the same domain, too! The @From and @To are also identical in the code I'm running and so is the smtp IP address. I've been searching the WEB and I've found all sorts of hints, but the fact that the ActiveX stuff works just fine on both machines says all that stuff is good! AND, the only step that fails on one of the servers is the "send". Everything else works just dandy (no error codes)...
Why does the ActiveX work fine on both machines but the OLEDB automation script fails on only 1 step on one of the servers? And, how can I fix it? I realize this is a heck of a thing to ask, but I've pretty much run out of tricks.
Thanks 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 15 posts - 1 through 15 (of 26 total)

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

Jeff,

The answer maybe one server is using Exchange which still support CDO to run your code and the second server being Win2003 is using IIS6 to run your code but CDO is obsolete in IIS since 2005 because IIS uses CDO with system.web which is depreciated in 2005 so IIS just gives strange errors. So you need to make sure you are using Exchange to run both code. But then again I could be wrong.

:Whistling:

Kind regards,
Gift Peddie

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Found this reply to a virtually identical problem description:

Your in luck! I just figured this out last night. Go to Control Panel, add
remove programs, add remove windows components, find SMTP services. Add it
in. No need to reboot.
Fred

I found this at http://www.tech-archive.net/Archive/Windows/microsoft.public.windows.server.scripting/2005-02/0377.html

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Heh... we're running Lotus Notes so not sure any of the servers are running Exchange. I'll take a look, though.

It would really be cool if Barry's suggestion were all it is.

Thanks for the help, guys. I sure do appreciate it. I'll let you know what happens...

--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)

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

Oh yeah... meant to ask. Barry, do you remember what you did a search on? I did one on the error message itself and got just about everything except the one you pointed out.

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Heh. I almost gave you this:http://www.letmegooglethatforyou.com/?q=cdo+sendusing+configuration+value, but then I thought that it might not be nice. 😀

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Heh... yeah, I've see that before... Funny and nasty all at the same time. 🙂

My concern is that those all say pretty much the same thing... either here's the code, which I already have, or you have to enable SMTP or Exchange Server, yada-yada... the problem I'm having is that the code works when it's executed as ActiveX code... when it's executed as an automation script, it all works on all but one server even though the ActiveX works on that server too!

I can't login to the work servers from home (just consulting for now) so I've gotta wait until tomorrow before I can try the suggestions you folks were kind enough to give me.

--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)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

Jeff,

The new owners of IIS Asp.net team told me to tell all users that CDO code will not run I have also talked to at least two people using VBscript this last year that CDO code is not running in IIS smtp service. If one version runs then you need to use that.

Kind regards,
Gift Peddie

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

Thanks, Gift Peddie... You sure? I ask because both versions of the code run on all my servers but one and they're all Windows 2003 servers.

--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)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 9

Here is one of the threads but I think one of the threads I referenced is SQL Server 2000 and he says he got his code to run again. So you may get it to run again but yes the new owners of IIS don't want to run CDO code any more for performance reason.

http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/2f809fa3-370b-4e1b-b2a5-03615b5db20d/

Kind regards,
Gift Peddie

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

Thanks. Those are some pretty good links on the new stuff. Bad part about all the .Net stuff is that I really, really need this to run from a stored procedure.

And, just to reiterate my confusion... the ActiveX script works which says all the components are there and working correctly for the CDO thingy... it's just the equivalent sp_OA script isn't.

--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)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 11

Hi Jeff,

I found an alternative that uses stored proc that may be better than CDO I say may be because I have not used it.

http://msdn.microsoft.com/en-us/library/ms190307.aspx

Kind regards,
Gift Peddie

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 12

RBarryYoung (1/6/2009)
Heh. I almost gave you this:http://www.letmegooglethatforyou.com/?q=cdo+sendusing+configuration+value, but then I thought that it might not be nice. 😀

If you are going to do something like that you should put it in a tinyurl ie http://tinyurl.com/885con

Shamless self promotion - read my blog http://sirsql.net

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

Gift Peddie (1/6/2009)
Hi Jeff,
I found an alternative that uses stored proc that may be better than CDO I say may be because I have not used it.
http://msdn.microsoft.com/en-us/library/ms190307.aspx

Thanks, Gift.

--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)

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

RBarryYoung (1/6/2009)
Found this reply to a virtually identical problem description:
Your in luck! I just figured this out last night. Go to Control Panel, add
remove programs, add remove windows components, find SMTP services. Add it
in. No need to reboot.
Fred
I found this at http://www.tech-archive.net/Archive/Windows/microsoft.public.windows.server.scripting/2005-02/0377.html

Bugger... followed these instructions and "SMTP services" doesn't appear even on the systems that do work.

This is driving me nuts... why would the ActiveX work and not the identical sp_OA calls?

--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)