CDOSYS from a Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CDOSYS from a Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-21 : 10:09:27

Karl writes "I'm using SQL 2000 SP2 on a Windows 2000 SP2 server. I have IIS installed, but the SMTP Virtual Server is NOT running. I do NOT have Exchange or Outlook installed, nor do I want to. I found the following documents on Microsoft's websites: Q312839 and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wss/wss/_clb_sending_smtp_mail_by_port_25_using_cdosys_vb.asp

The second document, entered as VBScript in a DTS package, works beautifully. It creates and sends a message through an external SMTP server.

However, I cannot seem to get the CDOSYS stored procedure working. A message is created, and put into the pickup folder for IIS. I don't want this. I want it to send the message directly to the external SMTP server, like the VBScript does.

It looks to me that the CDO configuration is picking up the default cdoSendUsingMethod of 1, for the local SMTP virtual server. I want it to be 2. However, no matter what I try, I can't seem to change the configuration fields for this beast from the stored procedure.

Can someone PLEASE give me some assistance?"

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-01-21 : 10:28:00

Posting your stored procedure would help us determine what the problem is.

Stored procedures are not the ideal place to run COM objects. I noticed a collection in the SMTP object is used in the VB sample. SQL Server may have a problem with collections. If possible, I suggest moving the SMTP code from the stored procedure to a VB program or the client app.

kjkrueger
Starting Member

2 Posts

Posted - 2002-01-22 : 11:05:39

quote:

Posting your stored procedure would help us determine what the problem is.

Here is the code that I used from Microsoft's Q312839 document:
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312839[/url]
The results I see look like only the To, From, Subject, and HTMLBody fields are populated correctly, but not the Configuration(*) fields.

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000)
AS
Declare @iMsg int
Declare @hr int

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration(cdoSendUsingMethod)', 2 -- use remote SMTP server
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration(cdoSMTPServerName)', 'SMTPserver' -- name of your SMTP mail server
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration(cdoSMTPConnectionTimeout)', 10
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
EXEC @hr = sp_OADestroy @iMsg

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-01-22 : 11:59:06

You might want to look at this article:

[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q151602[/url]

kjkrueger
Starting Member

2 Posts

Posted - 2002-01-22 : 16:22:34

Okay, here's my new SP. I've also included the statement I use to call the SP, and the results from the results pane. What happens is that an email is created in the C:\INETPUB\MAILROOT\PICKUP directory. Since I put a 2 for the cdoSendUsingMethod, this is not supposed to happen. It is supposed to send the email directly to the SMTP server. It's acting as though cdoSendUsingMethod is a 1, which is the default. @hr never returned anything other than 0.

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000)
AS
Declare @iMsg int
Declare @hr int

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration(cdoSendUsingMethod)', 2 -- use remote SMTP server
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration(cdoSMTPServerName)', 'SMTPServer' -- name of your SMTP mail server
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration(cdoSMTPConnectionTimeout)', 10
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OAMethod @iMsg, 'Send', null
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr

EXEC @hr = sp_OADestroy @iMsg
if @hr <> 0 EXEC sp_displayoaerrorinfo @iMsg, @hr
GO

Statement:
exec sp_send_cdosysmail 'myaddr@myserver.com', 'myotheraddr@myserver.com', 'Test CDO Subject', 'Test CDO Message'

Here are the entire contents of the results pane:
The command(s) completed successfully.

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-01-23 : 10:17:06

Try converting

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration(cdoSendUsingMethod)', 2 -- use remote SMTP server

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration', 2,cdoSendUsingMethod -- use remote SMTP server

Check the syntax of the sp_OASetProperty here:
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_02rd.asp[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources