If you’re experiencing issues with CDOSYS under a stored procedure, you may be running into one of the issues I'll mention here.
Outlook is usually installed on SQL Servers in order to provide mail sending capability (using xp_sendmail). However, xp_sendmail does not work in a cluster configuration. So CDOSYS is used from a stored procedure to send the mail.
Here is the article which covers sending email using CDOSYS under a stored procedure.
How to send e-mail without using SQL Mail in SQL Server
Note: Be sure to read the section "MORE INFORMATION". If you use the big sample in that article, be sure to change it to send via pickup directory if at all possible.
If CDOSYS is used from a stored procedure, then it should send by pickup directory and not port. If you send email by port, you may see a hang/crash of SQL Server at some point. If you hooked a debugger, you would probably see an access violation occurring when CDOSYS is setting one of the fields in the configuration object of the message. This is due to a bug in one of the DLLs used by CDOSYS. This problem is not encountered when you send via pickup directory or call CDOSYS outside of a stored procedure.
Here are some options:
1) Send email via pickup directory: This is probably the best solution by far. This allows the code to fire-and-forget, which means that it won’t tie-up or slow things down because CDOSYS will simply drop the message as a file in the local SMTP pickup folder. This provides the quickest and most reliable way to send the message. The local SMTP server will handle relaying the message. If the network or remote SMTP server has problems (i.e. is down), your stored procedure won't have to worry. If you send via port and there is a problem with the network or remote SMTP server then your stored procedure code won’t be able to send the message - it will get an error and the SP code will need to account for that. Of course the SMTP server must be installed on the SQL server box and setup for relay. However, remember that SMTP is a system component on the install CD and is optional. So, with sending via pickup to a local SMTP server: Faster, more reliable and less problematic.
2) Send the message the message by VB Script using DTS: You can use a VB Script file to send the message using CDOSYS to send via port or by pickup folder. You can use DTS to run the .VBS file. Such code would probably use ADO code to read the mail content from a table.
3) Some people have tried to work-around this issue by registering CDOSYS to run out of process. The problem here is that using CDOSYS in way was never tested and is not advised - possible problems with doing this are unknown. A service pack or hot fix could cause a failure or crash.
If you run into an issue such saying it cannot connect to host or relay, then try a VB Script file from the desktop and be logged-in as the account which would normally run the CDOSYS code.
When sending via pickup folder, be sure that the account which runs the stored procedure or VB Script has permission to the folder if sending by pickup or permission to relay if sending via port.
If your sending via port and tell the code to send via pickup and don't specify the pickup folder in code, then CDOSYS will need to get its location from the IIS metabase. If the account running the code does not have permission, then you will get an error. You can get past this by either specifying the pickup folder in code or by modifying the IIS metabase permission to give the account permission to read IIS metabase settings.