CDOsys mail stored proc - SQL Server Forums

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

SQL Server Administration (2005)

CDOsys mail stored proc

Author

Topic

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-06-24 : 06:58:36

Hi Guys, i got a problem, i've create the stored proc that i found on the net and altered the server name. But then when i try to excute the stored proc (this see blow the first one) and pass in the parameter no mail is sent

Any help would be great

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)= NULL,
@Body varchar(4000) = NULL

/******************************************
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 ("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas. microsoft.com/cdo/configuration/smtpserver").Value', 'exchange.cname.net'

-- 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
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

EXEC @hr = sp_OADestroy @iMsg

---------------

exec sp_send_cdosysmail @to = 'ng@test.com',
@From = 'ng@test.com',
@subject = 'Test',
@body = 'Check'

Can anyone help please, is it the mail server part i need to be working with

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-06-24 : 07:08:16

Please

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-24 : 07:20:10

Can your database server resolve the smtp servers name? You've exchange.cname.net as the smtp server. Is that correct or do you maybe need to give a private IP instead?

Could be firewall also.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-06-24 : 08:27:59

Thanks for that charlie, I've sorted the problem now by changing the server name and then creating the proc in the master instead of the user database

thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources