April 25, 2005 at 11:10 am
Hi,
I have a SPROC that uses SMTP to send email alerts instead of SQL Mail. The alret is raised for level 16 error (just for testing), I just run the following in Query Analyzer:
raiserror ('job id 1 expects the default level of 10.', 16, 1) with log
The SPROC runs successfully but nothing happens, in other words I don't get the email and I don't get any errors.
I have registered the SMTP DLL on the server, tested the SPROC through Query Analyzer and Sever Agent (executed the job), and checked the server's logs for error. Nothing.
The job's output log has this:
Job 'Email DBA (test)' : Step 1, 'Email DBA' : Began Executing 2005-04-25 12:49:58
(It appears as though it's hanging…it doesn't finish the execution...)
It seems like it's working but it's not. Is there anywhere else I can check for errors…Any ideas?
April 25, 2005 at 1:24 pm
It might be looking for a var that was not supplied.
Can you provide your email code?
April 26, 2005 at 7:09 am
Here is my SPROC:
CREATE PROCEDURE dbo.usp_sendmail (@recipients varchar(200), @message varchar(2000))
AS
declare @object int, @hr int, @v_returnval varchar(1000), @serveraddress varchar(100)
Set @serveraddress = 'xxx.xxx.xxx.xxx'
exec @hr = sp_OACreate 'SimpleCDO.Message', @object OUT
exec @hr = sp_OAMethod @object, 'SendMessage', @v_returnval OUT, @recipients, @recipients, 'test', @message, @serveraddress, @v_returnval
exec @hr = sp_OADestroy @object
GO
Here is the Job step:
exec dbo.usp_sendmail @recipients = 'me@mycomp.com',
@message = '
Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Database: [A-DBN]
Message: [A-MSG]
Check the [SRVR] SQL Server ErrorLog and the application event log on the server for additional details'
This job is executed everytime a severity 16 error happens.
April 26, 2005 at 8:52 am
I've added the following error checking to my SPROC:
EXEC @hr = sp_OAGetErrorInfo @object, @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 - sp_OAMethod was a success.'
RETURN
END
and when I run the SPROC through Query Analyzer it doesn't give me any results...doesn't print success or failure messages...
Any ideas?
April 26, 2005 at 8:52 am
I had problems initially configuring my email to interact with a similar circumstance.
I tried sp_send_cdontsmail, sp_send_cdosysmail and some other sp_sendmail with out much success.
I then tried using xpsmtp80.dll for my SQL email notifications and it works great!
It can be found at: http://www.sqldev.net/xp/xpsmtp.htm
Hope this helps
April 26, 2005 at 8:59 am
Thanks Martin!
I'll check it out.
Sandra
April 26, 2005 at 12:05 pm
Turns out the problem was the server address I was inputing. It was wrong! I put the wrong smtp ip...
So it all works great now.
Thanks for everyone's help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply