CDOSys and Identical code

Question

CDOSys and Identical code

Viewing 12 posts - 16 through 26 (of 26 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

What context are you executing ActiveX in?

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Oh, I see , it's DTS(Or SSIS?). Hmm...

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Is Outlook installed? Or is this relying on IIS for CDO as Gift says?

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Jeff Moden (1/6/2009)
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...

I am not that familiar with the OA sprocs, but don't they execute with the Username and within the context of the SQL Server? And don't DTS ActiveX scripts execute with the Username of the logged on user (if run directly) or with the Username of the SQL Agent (if scheduled)?

[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]

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

Yes Barry but those instructions are not correct for IIS6 which Jeff runs. The reason IIS6 comes not installed and smtp is a sub component of IIS so you need to add IIS6 in add remove Windows component and click on details. When you click on details you see smtp as one of the IIS6 features you can add. These are some of the reasons Windows now just uses IIS but it is owned by the developer division.

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

Hey Barry I don't know anything about Outlook I don't use it only employers make me use it.

Kind regards,
Gift Peddie

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

Gift Peddie (1/7/2009)
Yes Barry but those instructions are not correct for IIS6 which Jeff runs. The reason IIS6 comes not installed and smtp is a sub component of IIS so you need to add IIS6 in add remove Windows component and click on details. When you click on details you see smtp as one of the IIS6 features you can add. These are some of the reasons Windows now just uses IIS but it is owned by the developer division.
http://msdn.microsoft.com/en-us/library/8b83ac7t.aspx

That all sounds good and thank you very much for spending time helping me... but, I have to ask again... The ActiveX script (via SQL Server Job Steps) that uses CDO.Message works just fine... why doesn't the sp_OA script also work fine? And the user I'm logged in as in both instances is the same user... and that user has "SA" privs.

Man, what a pain... thanks again for trying to help.

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

That is becuase it is not running in the context of your permissions but the SQL Server Agent. And SQL Server Agent also runs under limited access account.

http://articles.techrepublic.com.com/5100-10878_11-5164094.html

Kind regards,
Gift Peddie

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Yeah, that article confirms what I thought about DTS: unlike SQL commands which execute in Server context, DTS executes in Client context, which is to say that it executes in the context of the client process that is running it. Usually, this means the SQL Agent's context.

SQL commands, on the other hand, execute in the context of the Server process, that is, sqlservr.exe and whatever windows Username it is executing under. Now, like xp_CmdShell, the sp_OM methods might make an entirely separate context, but that seems unlikely, so then they would have to be executing in the context of the SQL Server itself.

All of which would lead me to conclude that the problem was a difference in the Agents Username & context, versus the SQLServer's Username & context.

[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 9

Sorry guys... looking at this stuff now... just got out of a really long meeting...

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

Heh... OK... first of all, I'm not using DTS for any of this. Second, the SQL Server Agent, the Server itself, and I are all logging in as the same user. The ActiveX is being executed as a step in an SQL Server job and it too is using the same login as all the other stuff. And, that's true on all of the servers.

Again, the ActiveX script I first posted works on ALL the servers... the sp_OA script I first posted works on all those same servers except 1 and I can't figure out why. This is driving me absolutely nuts because it just doesn't make sense expecially since I'm using the same login across the board and that login has SA privs on all the 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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

My gut feel is that the sqlservr.exe on the failing box does not have some DLL loaded that it needs to make the CDO..smtp stuff work. This would be something that the SQLAgent.exe and the sqlservr.exe on the other box do have loaded, so maybe you can use Process Explorer to figure out if that is it?

That's about all I can think of... 🙁

[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 12

BWAAAAA_HAAAAA-HAAAAA!!!!!! Oh shoot! I hate "nuances". I got it to work on all servers and I'm trying to figure out which of the differences between the old and new code made the difference. Again, the original code I posted above worked on all my servers but 1. I verified that CDOSYS.dll was on all the servers in the same folder including the 1 that didn't work.

After a shedload of some pretty dedicated "Googling" (2-1/2 frigging days worth :P), I finally found a post right here on SSC (but the custom search on THIS sight didn't find it... yep, pretty ticked about that ;)). Here's the thread that finally solved my dilema...

http://www.sqlservercentral.com/Forums/Topic285765-9-1.aspx#bm631992

Barry and Gift... thank you folks for trying. Considering the symptoms of the problem, this was a real jaw breaker. Like I said, I'll post my final code once I found out what the "catch" was for that one bloody server.

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