SQL Server - Sending Email notices in SQL?

Asked By jp2msf on 18-Sep-08 10:45 AM
I'm trying to follow Microsoft's KB Article #312839 on how to send e-mail
without SQL Mail in SQL Server.

We have SQL Server 2000 with SP2.

I entered the script they listed in their article for CDOSYS:
http://support.microsoft.com/kb/312839 (as long as the link is good)
I edited the 'cdoSMTPServerName' to be our SMTP Server: 'http://172.16.8.1'
in a new Stored Procedure, and it saved with no problems.

Whenever I run the SP in Query Analyzer, I get -2147220973. Is that an error
code or a phone number [i.e. (214) 722-0973]?

I am a Software Developer and not a DBA (we do not have DBAs here). Any help
or advice is appreciated.




jp2msf replied on 18-Sep-08 10:56 AM
Hey, this may help someone tell me what to do:

I forgot to add the data from the cdosysmail_failures table:

There are 2 entries, both from the same date and time (when I ran it):

1. The first error has:
Spid: 55
iMsg: 16711422
HR: -2147220973:
Source of Failure: <NULL>
Description of Failure: <NULL>
Output from Failure: <NULL>
Comment about Failure: "Failed at sp_OAMethod Send"

How would I debug the 'sp_OAMethod Send'? I'm guessing this is a stored
procedure on my SQL Server, but I don't know where it is.

2. The second error has the following:
Spid: 55
iMsg: 16711422
HR: 0
Source of Failure: CDO.Message.1
Description of Failure: The transport failed to connect to the server.
Output from Failure: Description: The transport failed to connect to the
server.
Comment about Failure: "sp_OAGetErrorInfo for sp_OAMethod Send"

Does this extra information help?

Could someone guide me to getting this to work?