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="mailtoescription=@desc">Description=@desc

    EXEC @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="mailtoescription=@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&quot.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&quot.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&quot.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&quot.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&quot.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&quot.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="mailtoescription=@desc">Description=@desc

    EXEC @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="mailtoescription=@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