HELP - Sending email using CDOSYS in SQL 2k

Question

HELP - Sending email using CDOSYS in SQL 2k

Rob Smith

SSC Journeyman

Points: 84
More actions
March 19, 2007 at 7:20 am

#153484

Hi
I have a problem which is driving me nuts.
The stored procedure (listed below) runs OK on existing 2000 and 2003 servers.
I am trying to get this to run on a new 2003 server but get the error message 'The "SendUsing" configuration value is invalid'.
This simple vb script runs from the desktop fine and sends email but the stored procedure throws this error. The SMTP server is running OK.
Dim ObjSendMail

Set ObjSendMail = CreateObject("CDO.Message")
ObjSendMail.To = "rob.smith@cronersoftware.co.uk"
ObjSendMail.Subject = "this is the subject"
ObjSendMail.From = "someone@someone.net"
objSendMail.TextBody = "this is the body"

ObjSendMail.Send

Set ObjSendMail = Nothing
Any ideas where I start looking?
Thanks
Rob Smith
---- stored procedure
CREATE PROCEDURE [dbo].[NWsp_SendMail]
@From nvarchar(256) , @To nvarchar(256) , @Subject nvarchar(256)=" ", @Body nvarchar(4000) =" "
/******************************************
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 nvarchar(255) Declare @description nvarchar(500) Declare @output nvarchar(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 (".Value','2'">http://schemas.microsoft.com/cdo/configuration/sendusing.asp").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(".Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '127.0.0.1'
-- 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

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
IF @hr <>0 select @hr BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

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

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

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

in the sp you are setting the smtp server to 127.0.0.1 localhost. Is smtp running on the server and does SQL login have permissions to relay on it?

The desktop script does not specify a host so assuming it is local it is running under the logged in account.

Rob Smith SSC Journeyman Points: 84 More actions · Answer 2

Hi

Yes SMTP is running. I have tested this via telnet on the command prompt. Also the vbscript runs direct from the desktop fine.

How to I tell/change the SQL login permissioons to relay to it?

thanks

Rob

nigel. SSChampion Points: 11636 More actions · Answer 3

You may have mistyped the line where you are setting the sendusing parameter. I don't think you should have the .asp at the end of the http://schemas.microsoft.com/....../sendusing.asp url.

At least it isn't in my stored proc that does a similar job.

Nigel
How to post questions

Rob Smith SSC Journeyman Points: 84 More actions · Answer 4

Thanks

Progress - I think!

I now get a different message at least:

The transport failed to connect to the server.

The SMTP server is running, I can send email from SQL using a vb script but the sp still fails - any ideas?

Rob

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

did you read http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=510 by Clinton Herring ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Rob Smith SSC Journeyman Points: 84 More actions · Answer 6

Very interesting.

I have managed to get it working by pointing the sp to another smtp server rather than the one running on the same server. So I guess it must be related to local pernmissions - somewhere!