CDO.Message.1The "SendUsing" configuration value is invalid.
-
I am trying to Send Email from SQL 2000 on Win 2003 using a remote SMTP.
i am not running smtp serive on that machine... and have no iis, but CDOSYS.dll is present...
Please help......
CODE------------------------------
DECLARE @From as varchar(50); DECLARE @To as varchar(50); DECLARE @Subject as varchar(255); DECLARE @Body as varchar(512);
DECLARE @message int; DECLARE @config int; DECLARE @hr int; DECLARE @src varchar(255), @desc varchar(255)
SELECT @From = 'a@b.com';SELECT @to = 'c@d.com'; SELECT @Subject = 'Subjecttext 3';
SELECT @Body ='Messagetext
EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object
EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration object
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort' -- Send the message using the network
EXEC @hr = sp_OASetProperty @config, 'Fields(cdosmtpconnectiontimeout)', '10' -- Send the message using the network
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'mail.smtpserver.com' -- SMTP Server
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServerPort)', 25 -- Server SMTP Port
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoBasic' -- Anonymous SMTP
EXEC @hr = sp_OASetProperty @config, 'Fields(cdosendusername)', 'user' -- Anonymous SMTP
EXEC @hr = sp_OASetProperty @config, 'Fields(cdosebdpassword)', 'pass' -- Anonymous SMTP
EXEC sp_OAMethod @config, 'Fields.Update'
-- Message Object
EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set message.configuration = config
EXEC @hr = sp_OASetProperty @message, 'To', @To
EXEC @hr = sp_OASetProperty @message, 'From', @From
EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body
EXEC sp_OAMethod @message, 'Send()'
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
-- Destroys the objects
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @config
-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
--RETURN
END
-
This was removed by the editor as SPAM
-
Grasshopper,
I am experiencing the same issues that you are. What is more troubling for me is that we have an .asp page with almost identical code on the same server that sends emails with no trouble. In my testing, I also found that from a local install of SQL Server on my desktop I am able to send emails using the same code that fails on the server.
Have you had any luck in the last couple of days?
Thank you.
-
Grasshopper,
I now have this working on our server. Here is what you need to do to get your Stored Procedure to work:
Comment out/remove: EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration object
Comment out/remove: EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set message.configuration = config
Comment out/remove: EXEC @hr = sp_OADestroy @config
Change all references to ‘@config’ to ‘@message’.
Please give this a try and let me know if it works. I have tested it on two separate servers and it works.
-
Hi Stacy, thanks for the help but i still get the error here is the code that i have now...
It works gret on my localInstall but on server it just doesn't want to...
Any help would be appreciated....
Borik
------ CODE I HAVE NOW...
DECLARE @From as varchar(50); DECLARE @To as varchar(50); DECLARE @Subject as varchar(255); DECLARE @Body as varchar(512);
DECLARE @message int; DECLARE @config int; DECLARE @hr int; DECLARE @src varchar(255), @desc varchar(255)
SELECT @From = 'a@b.com'; SELECT @to = 'A@b.com'; SELECT @Subject = 'Subjecttext New Live';
SELECT @Body ='Messagetext 2';
EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object
EXEC @hr = sp_OASetProperty @message, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort' -- Send the message using the network
EXEC @hr = sp_OASetProperty @message, 'Fields(cdosmtpconnectiontimeout)', '10' -- Send the message using the network
EXEC @hr = sp_OASetProperty @message, 'Fields(cdoSMTPServer)', 'mail.smptserver.com' -- SMTP Server
EXEC @hr = sp_OASetProperty @message, 'Fields(cdoSMTPServerPort)', 25
EXEC @hr = sp_OASetProperty @message, 'Fields(cdoSMTPAuthenticate)', 'cdoBasic'
EXEC @hr = sp_OASetProperty @message, 'Fields(cdosendusername)', 'USER'
EXEC @hr = sp_OASetProperty @message, 'Fields(cdosebdpassword)', 'password'
EXEC sp_OAMethod @message, 'Fields.Update'
EXEC @hr = sp_OASetProperty @message, 'To', @To
EXEC @hr = sp_OASetProperty @message, 'From', @From
EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body
EXEC sp_OAMethod @message, 'Send()'
---***********------- error happens right here
--EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
--SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descEXEC @hr = sp_OADestroy @message
-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@desc--RETURN
END
-
Borik,
Why don't you try these changes below as well? Hopefully that will do it.
EXEC @hr = sp_OACreate 'CDO.Message', @Message OUT -- create the message object
-- Configuration Object
EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', @EmailSMTPSendUsing
EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @EmailSMTPServer
EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', @EmailSMTPServerPort
EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', @EmailSMTPAuthenticate
EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @EmailSMTPUsername
EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @EmailSMTPPassword
EXEC @hr = sp_OAMethod @Message, 'Configuration.Fields.Update', NULL
-- Message Object
EXEC @hr = sp_OASetProperty @Message, 'To', @To
EXEC @hr = sp_OASetProperty @Message, 'From', @From
EXEC @hr = sp_OASetProperty @Message, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @Message, 'TextBody', @Body
EXEC @hr = sp_OAMethod @Message, 'Send', NULL
-
Stacy thanks again for a quick reply... i am still getting the error...
here is the code that i am executing... any other suggestions would be really apreciated...
Borik
-----------------------*******CODE
DECLARE @From as varchar(50); DECLARE @To as varchar(50); DECLARE @Subject as varchar(255); DECLARE @Body as varchar(512);
DECLARE @message int; DECLARE @hr int; DECLARE @src varchar(255), @desc varchar(255)
SELECT @From = 'A@B.com'; SELECT @to = 'A@B.com'; SELECT @Subject = 'Subjecttext';
SELECT @Body ='Messagetext';
EXEC @hr = sp_OACreate 'CDO.Message', @message OUT
EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing"
.Value'">http://schemas.microsoft.com/cdo/configuration/sendusing").Value', 'cdoSendUsingPort'EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver"
.Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail.remoteSMPTserver.com'EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserverport"
.Value'">http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', '25'EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
.Value'">http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', 'cdoBasic'EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendusername"
.Value'">http://schemas.microsoft.com/cdo/configuration/sendusername").Value', 'username'EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendpassword"
.Value'">http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'password'EXEC @hr = sp_OAMethod @Message, 'Configuration.Fields.Update', NULL
EXEC @hr = sp_OASetProperty @message, 'To', @To
EXEC @hr = sp_OASetProperty @message, 'From', @From
EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body
EXEC sp_OAMethod @message, 'Send', NULL
--Error happens right here....
--EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
--SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descEXEC @hr = sp_OADestroy @message
-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@desc--RETURN
END
-
Borik,
I ran your code and received the error. After changing 'cdoSendUsingPort' & 'cdoBasic' to '2' & '1' respectively, the email was sent correctly. Do you know what values are being utilized for these settings? Are these values the same as what your SMTP server requires? Give these two values a try.
Other than these two settings, everything else seems to be the same as what we have.
Hopefully this works!
-
Stacy,
Thank you so much!!!
It's finnally working... I got my first emails from productions...Borik
Let hope this will help others as well...
-
I am getting following error
Source: CDO.Message.1
Description: The "SendUsing" configuration value is invalid
I know if we recycle the SQL server serice, it can fix the error. But I am getting this error very frequently. I cannot recycle the Production server so often. Any coments will be highly appreciated.
Source Code: Declare @iMsg int, @hr int
Declare @source varchar(255), @description varchar(500), @output varchar(1000)
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'SMTP server'
("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', '25'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '1'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/cdoNTLM").Value', '2'
("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', 'username '
("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'pwd'
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, 'TextBody', @Body
EXEC @hr = sp_OASetProperty @iMsg, 'AttachFile',@AttachFile
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
If @@Error <> 0
Return (-1)
Else
Print 'Succesfully E-Paged'
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
EXEC @hr = sp_OADestroy @iMsg
-
I have follow the same coding and put in the my value to the respective parameter, like smtpserver, sendusername and sendpassword, i have also change the sendusing and smtpauthenticate to '2' and '1' accordingly. FYI, my smtp server is require the authentication.
no error has prompt and sys give the message with 'The command(s) completed successfully.' But didnt receive any mail.
what should i check..?
-
Stacy (6/13/2006)
Borik,
I ran your code and received the error. After changing 'cdoSendUsingPort' & 'cdoBasic' to '2' & '1' respectively, the email was sent correctly. Do you know what values are being utilized for these settings? Are these values the same as what your SMTP server requires? Give these two values a try.
Other than these two settings, everything else seems to be the same as what we have.
Hopefully this works!
[font="Arial Black"]YYYEEEEEEEEEEE-HAAAAAAAA!!!! [/font]Yep... I know... wicked old post you put here and I was darned lucky to find it. This is the code that I was using and it worked on ALL of my servers except 1. After change to the code you and Borik made (with the correct IP address and, of course, the correct values as above), it worked just fine if not a bit slower.
So, thank you Stacey and Borik. I've got to figure out what in the code actually made the difference. I didn't have any of the authentication stuff in my code and that may be it. When I find out, I'll post the final code here...
Thanks again, 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) -
Great stuff.
Thank you guys. This helped me in sending out mail from sql server 2000.
As I didnt have outlook in my server(have only outlook express), I am opted this choice.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply