SQL Server Performance

Fail to send mail using CDOSYS

Discussion in 'General Developer Questions' started by fifa, Sep 18, 2004.

  1. fifa New Member

    i am a newbies in SQL Server. I have been assigned a job to send (only send) e mail using SQL Server 2000. I use CDOSYS stored procedure and have successfully tried it in LAN environment. (The SMTP Server is another computer in LAN). However, when i want to use a SMTP Server over the Internet, i failed. I have tried many methods. Is it because of the proxy problem? Or firewall? Here's my code:<br /><br />CREATE PROCEDURE [dbo].[sp_send_cdosysmail] <br /> @From varchar(100) ,<br /> @To varchar(100) ,<br /> @Subject varchar(100)=" ",<br /> @Body varchar(4000) =" ",<br /> @server varchar(100)=" ",<br />@username varchar (20)=" ",<br />@password varchar (20)=" "<br />/*********************************************************************<br /><br />This stored procedure takes the parameters and sends an e-mail. <br />All the mail configurations are hard-coded in the stored procedure. <br />Comments are added to the stored procedure where necessary.<br />References to the CDOSYS objects are at the following MSDN Web site:<br /<a target="_blank" href=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp</a><br /><br />***********************************************************************/ <br /> AS<br /> Declare @iMsg int<br /> Declare @hr int<br /> Declare @source varchar(255)<br /> Declare @description varchar(500)<br /> Declare @output varchar(1000)<br /><br />--************* Create the CDO.Message Object ************************<br /> EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT<br /><br />--***************Configuring the Message Object ******************<br />-- This is to configure a remote SMTP server.<br />-<a target="_blank" href=http://schemas.microsoft.com/cdo/configuration/sendusing>http://schemas.microsoft.com/cdo/configuration/sendusing</a><br /><br /><br />--<a target="_blank" href=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp</a><br />--1 for localhost<br />--2 for remote SMTP<br /> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'<br />-- This is to configure the Server Name or IP address. <br />-- Replace MailServerName by the name or IP of your SMTP Server<br /> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @server<br /><br /><br /><br /> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout").Value', 600 --cdoBasic<br /><br /> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', 1 --cdoBasic<br /> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @username<br /> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @password<br /><br /> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/urlproxyserver").Value', '161.139.66.103:6588' --ourserver<img src='/community/emoticons/emotion-4.gif' alt=':p' />ort<br /> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/urlproxybypass").Value', '&lt;local&gt;' --if local IP, no need proxy<br /> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/urlgetlatestversion").Value', true<br /><br /><br /><br /><br /><br />-- EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory").Value', 'c:program FilesSMTPServer'<br /><br /><br />--To configure the port<br />-- EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', 25<br /><br />-- Save the configurations to the message object.<br /> EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null<br /><br />-- Set the e-mail parameters.<br /> EXEC @hr = sp_OASetProperty @iMsg, 'To', @To<br /> EXEC @hr = sp_OASetProperty @iMsg, 'From', @From<br /> EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject<br /><br />-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.<br /> EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body<br /> EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL<br /><br />-- Sample error handling.<br /> IF @hr &lt;&gt;0 <br /> select @hr<br /> BEGIN<br /> EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT<br /> IF @hr = 0<br /> BEGIN<br /> SELECT @output = ' Source: ' + @source<br /> PRINT @output<br /> SELECT @output = ' Description: ' + @description<br /> PRINT @output<br /> END<br /> ELSE<br /> BEGIN<br /> PRINT ' sp_OAGetErrorInfo failed.'<br /> RETURN<br /> END<br /> END<br /><br />-- Do some error handling after each step if you have to.<br />-- Clean up the objects created.<br /> EXEC @hr = sp_OADestroy @iMsg<br />GO<br /><br /><br /><br />This is how i execute it:<br />declare @password varchar(20)<br /> select @Body = 'This is a Test Message'<br /> select @Server = 'smtp.myserver.com'<br /> select @username = 'username'<br /> select @password = 'password'<br /> exec sp_send_cdosysmail 'sender@mail.com','receiver@mail.com','Test of CDOSYS',@Body,@Server, @username, @password
  2. Argyle New Member

    What is the error you get?
    Do you connect to the correct server IP?
    Is the SQL Server allowed to send traffic to the internet?
    Does the email server allow requests from your SQL server?

    Do test if you can reach the standard mail port on an external server open a command prompt and type:
    telnet 123.123.123.123 25

    where 123.123.123.123 is the mail server IP and 25 is the SMTP port. If it works it should respond with it's name.

  3. fifa New Member

    My error is:

    "Source: CDO.Message.1
    Description: The transport failed to connect to the server."

    i tried to telnet the smtp server, it responds:
    "220 rblsmtpd.local"
    so i guess there is no problem with this
    but i am not sure whether the smtp server allow request from SQL Server

    How to allow or configure my SQL Server 2000 to send traffic to the internet?
  4. Twan New Member


    SQL Server itself doesn't care what ip address or server name you put in. It is blissfully unaware of the location or type of server to realy mail off.

    Did you run the telnet command on the sql server itself? Is the username/password combination that you are trying to log on with valid? Most smtp servers will allow anonymous sending of email, so no authentication would be used

    you do set a lot of configuration stuff in your proc.. Try commenting out all of the configuration stuff except:
    - sendusing
    - smtpserver
    - smtpserverport

    Cheers
    Twan
  5. satya Moderator

    Try to use telnet against the SMTP server for connection clearance.
    You probably need to setup the relay to allow the local machine to send emails.

    If you set up the SMTP server to use an IP address you only have to make a couple of changes. First set the relay to that address. Next set the SmtpServer value to that address as well.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. fifa New Member

    This is rather strange.
    As i said, if i use the mail server in LAN, the stored proc works, it can send to email from any domain.
    But, when i change the smtp server to an Internet based company smtp, it failed. I have try out 5 smtp server in malaysia.
    Can anyone suggest a smtp server that can be used to send email to all domain(@yahoo, @hotmail,...).
    I want to try my stored proc using it to check whether it is my SQL Server setting problem or the smtp server problem.
  7. fifa New Member

    Twan, r u sure that anonymous sending mail is still allowed?
    I search for many SMTP Server, but they have enforced authentication to eliminate spamming via their SMTP Sever.
    If u have one, please let me know. Just to try my stored proc only.

    Satya, as i said b4, i have successfully send the e mail out via Mail Server in LAN environment. Thanks for ur suggestion anyway.

    Argyle, i have stated my problem in detail. Hope u can help me, i have been trying to solve this stuff for 1 week. Headache looking at the same sp for so long..
  8. Argyle New Member

    Did you actually telnet from the sql server to the SMTP server? Do you have a valid SMTP server to use? You should have your own or another SMTP setup at your company. Do not rely on some random SMTP server on the internet since it could be gone tomorrow and most of them won't allow anonymous connections anyway.

    But if you already have a mail server on your LAN why not use it. What is it you want to test by using another mail server that you can't test using the mail server on your LAN?

    You could always install the SMTP service on your SQL server and allow the server to send traffic on port 25 (SMTP) and 53 (DNS) and test on that one.


  9. Twan New Member


    ah yes I'd assumed that you were talking about one of your own SMTP servers that you are trying to access over the Internet. I'd concur with Argyle, it is better to use your own SMTP server or set one up on a server (whether it is the sql box or an iis box)

    Cheers
    Twan
  10. fifa New Member

    My company thinks that setting up a mail server will cost a lot, as it need configuration. So, i am assigned a job to find SMTP server that can send. OK, i accept the point that anonymous mail server will be gone unexpectedly, that's why i plan to use authorised SMTP mail server.

    So, is it possible to use Hotmail or yahoo SMTP server? (of course with authentication.)That's why i setup so many parameters like userword, password++
  11. derrickleggett New Member

    Tell your company to buy a desktop and use that. At least it will be onsite so you can backup the PC and configure it how you need. That's ridiculous!

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  12. fifa New Member

    I want to use IIS 5.0 to be the mail server. Is it possible? I have configured it at the server but it dont work. My problem is, after i configure it, i try to telnet the server in LAN environment, but failed. Any good article to setup mail server using IIS?
  13. Twan New Member

    Hi ya,

    You should be able to use Add/Remove programs to add the SMTP Server service, this will also add some components from IIS. After that you can use the admin tool Internet Services Manager to check the config of the SMTP server. By default the server will support outbound emailing, so you won't need to do anything to that part of it.

    You will need to make sure that the server has DNS servers set in the TCP/IP config to allow it to lookup domains and get their MX records to find out where to send things to

    also if there is a firewall between the SMTP server and the Net then it would need to allow port 25 outbound in order to send the email

    Cheers
    Twan
  14. fifa New Member

    Thanks Twan, i can send mail to the IIS Mail Server.
    But then got new problem.
    IIS Mail Server dump the mail into Badmail folder, and thus
    do not reach the receiver.

    The *.BDR file looks like this:

    Unable to deliver this message because the follow error was encountered: "This message is a delivery status notification that cannot be delivered.".

    The specific error code was 0xC00402C7.


    The message sender was <>.


    The message was intended for the following recipients.
    sender@mail.com

    Please help me..
  15. Argyle New Member

    The server that has the mail service installed need to have a DNS server or two configured. Otherwise it will not be able to lookup the domain name in the email and they will end up in the BadMail folder. So verify that DNS is configured and works and that there is no firewall blocking DNS traffic (port 53).

    You can test by opening a command prompt on the mail server and type:
    nslookup www.hotmail.com

    If it works you should get something like this:
    Non-authoritative answer:
    name: www.hotmail.com
    address: 218.50.2.211

  16. stevem123 New Member

    Also make sure that if you system has multiple NICs that you set the binding order on the card with External access and a DNS server to be higher than the internal card.

    Go into the properties for your network places. Then use menu to access the Advanced / Advanced Settings and promote the external card to the top.

    Otherwise on Windows 2003 and possibly with 2000 as well, mail may just sit in the queue and never get delivered if the internal card is listed first in the binding order.

    When using CDOSYS make sure the user has rights to the Pickup folder in the IIS Mailroot area.
  17. fifa New Member

    Argyle,
    Thanks for the suggestion.
    No firewall is detected.
    The server that has the mail service is not the gateway to Internet connection. So, does that mean i have to set the DNS lookup at
    Network Neighbourhood(right click),
    then TCP/IP ->(NIC Name) properties
    ->DNS Configuration (Tab)
    ->DNS Order Search Order, type in the IP of the gateway computer?
  18. fifa New Member

    The gateway to internet connection is running Win 98,so it does not support DNS Server.
    The mail server is located in the same LAN, but it is running Win2K.
    So, should i point the DNS server setting in this computer to the gateway's IP? (It didnt work)

    Or i should put an ISP DNS Server address? (Same, didnt work)
    Error message:
    "The server interfaces cannot be updated. The IP address is invalid"
  19. Argyle New Member

    Well I don't know how you're network is set up. How do your other servers/workstations do DNS lookups? Do you have a DHCP server sending out this information maybe. Check what it looks like on other computers where dns lookup works from a command prompt.
  20. fifa New Member

    my company LAN got 10 comp.
    This LAN is connected to University network via a computer, let's name it as GATEWAY.
    GATEWAY got 2 NIC, 1 for LAN, 1 for proxy setting so that it can connect to the University.
    GATEWAY is running Win 98, and it only set the DNS at Internet Explorer-> LAN setting, that's all.

    The comp with IIS 5.0 is BETA.
    BETA is using Win2K OS but it connects to Internet via GATEWAY.
    So, if DNS Server is to be set, should it be at BETA or GATEWAY?
  21. fifa New Member

    There's no DHCP server. Every comp connects to Internet by setting Network-> (TAB) Configuration -> TCP/IP (NIC) Properties,
    then choose (TAB) DNS Configuration, then enable it with DNS Server refer to GATEWAY IP address.
  22. Argyle New Member

    Never heard of a Win 98 running a DNS service. Most likely you are using a DNS server at the university. Check what DNS servers the Win 98 computer has and try those on your IIS server as well.
  23. fifa New Member

    I try nslookup after setup the DNS server.
    But got this error:

    *** Can't find server name for address XXX.XXX.XXX.XXX: Non-existent domain
    Server: localhost
    Address:127.0.0.1

    DNS request timed out.
    timeout was 2 seconds
    DNS request timed out.
    timeout was 2 seconds


  24. Argyle New Member

    What do you mean "after set up the DNS server"?

    Did you set up a new DNS server (server as in physical server)?
    Did you install the DNS service on an existing server?
    Did you type in DNS IP address on an existing server under network properties?

    It's not clear to me what you did. Anyway you seem to be doing nslookup against a DNS service on the same machine since it responds with localhost:
    *** Can't find server name for address XXX.XXX.XXX.XXX: Non-existent domain
    Server: localhost
    Address:127.0.0.1

    Computers on your network should be using a DNS service already be it on another server on your network or one on the university network. Find out which DNS IPs are used and enter the same DNS IPs under network properties on the server running the mail (SMTP) service.
  25. fifa New Member

    Thanks Argyle, but i have tried another method and it works.
    I used xp_sendmail in SQL Mail and i can send the mail now.
    Thanks for your advise.

    For those having problem like me, i suggest setting up
    your own SMTP Server by using "1st SMTP Server" if you want
    to use CDOSYS method. The server will configure itself and
    can search for the appropiate DNS Server automatically.
    But it is a shareware, check it out athttp://www.emailarms.com/.
    I am not advertising, just tell my experience on using it.

    If u want to use SQL Mail(xp_sendmail), then configure Microsoft Outlook.
    Follow the guideline here:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;263556

    Please take note that the method that i use is for "SEND EMAIL" only.
    If u want full function mail server, this is not for you.

    Thanks for all of u who send suggestion for me..
  26. Santanu Roy New Member

    I used the code to send mail. But I am geting an error "Description: The message could not be sent to the SMTP server. The transport error code was 0x80070057. The server response was not available".
    So, pls help me to resolve the problem.
    Thanks.
  27. satya Moderator

    Instead of posting the error have you referred through the links about on this thread?

Share This Page