Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mesha
Starting Member
2 Posts |
 Posted - 2002-06-04 : 10:04:55
|
| i'm trying to use the following code to send email:----------------------------------------------------DECLARE @bodytext varchar(1000) DECLARE @object int DECLARE @hr int DECLARE @count int SET @bodytext = 'test email' EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT if @hr <> 0 print 'create failed'EXEC @hr = sp_OASetProperty @object, 'From', 'test@domain.com' if @hr <> 0 print 'set from property failed'EXEC @hr = sp_OASetProperty @object, 'Body', @bodytext if @hr <> 0 print 'set body property failed'EXEC @hr = sp_OASetProperty @object, 'Subject','test subject' if @hr <> 0 print 'set subject property falied'EXEC @hr = sp_OASetProperty @object, 'To', 'test@domain.com' if @hr <> 0 print 'set to property failed'EXEC @hr = sp_OAMethod @object, 'Send', NULL if @hr <> 0 print 'send failed' print @hrEXEC @hr = sp_OADestroy @object ----------------------------------------------------Send fails, and sp_OAMethod fails returns an error code of -2147221164. Does anyone know why and how can I fix this?Also, what are the advantages of using CDONTS over SQL Mail?I'm trying to send an email notification to a selected group using an update/insert trigger. Any comments or suggestions are greatly appreciated..... Thanks! |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
|
|
mesha
Starting Member
2 Posts |
Posted - 2002-06-04 : 11:27:23
|
| thank you very much! |
 |
|
|
cbrinson
Starting Member
36 Posts |
Posted - 2002-06-04 : 16:32:06
|
quote:
the main advantage with cdo over sql mail is that sql main requires a mapi profile, thus running exchange server.
I have never understood this at all. I have read a half dozen articles or more on how to get SQL Mail to work and I have never understood it. I just find it very hard to believe that in order to simply use SQL Mail you have to be running Exchange server. Hell, I have actually read articles that seemed to imply that you need to have a full version of Exchange server running on the same damn machine as the SQL Server?!!As I understand it, it is not possible to send email alerts of SQL Server problems from within SQL Server without having a full version of Exchange at least running on your network and a copy of Microsoft Outlook on the SQL Server machine or perhaps even a full version of Exchange on the same box! Also, it is not possible to send an email using CDONTS if the email contains more than 256 characters. I have never in my programming life found something more difficult than sending email from SQL Server. It just should not be that difficult. Does anyone know of any really knowledgeable article dealing with this subject? For example, I would like my SQL Server to email me before the transaction log fills up or when a scheduled task fails. But I don't want to devote an entire server (that I don't own) to running Exchange just to do that.Thanks,A very frustrated Chris :-( |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-04 : 16:38:50
|
| It's not much of a mystery when you consider that MS wants you to use the entire BackOffice suite...Exchange, SQL Server, Terminal Server, etc. Why should they support 3rd party email or SMTP? It's not their protocol! I'll tell ya something, if they incorporate SMTP support in Yukon, NO ONE will use SQL Mail ever again.I know I've seen some articles on making SQL Mail work with other non-Exchange MAPI setups, but every one of them was a dog. They worked, but JEEEEEEEEEEEEEEESUS, my circumcision was less painful. It seems that the only effective setup that won't make you commit suicide is to use Exchange.Take a look on http://www.swynk.com or http://www.sqlservercentral.com and see what they've got on it. If anyone has figured it out they have. |
|
|
|
|
|
|
|
|
Sending E-mail using SQL and CDONTS doesn't work