Capturing text of result messages

Question

Capturing text of result messages

JMarx

Default port

Points: 1435
More actions
June 13, 2006 at 10:48 am

#115463

I need to capture the text that results from an error when I use an extended stored procedure.
I'm using XPSMTP to send mail from an update trigger. When it errors out, it returns a code of 1, along with an error message. In Query Analyzer, that message shows up in the Messages pane. How can I (programmatically) grab the error message so I can store it in a log table?
Here's a snippet of my code:
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail ...
select RC = @rc
GO
Thanks in advance..
..jeremy.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Jeremy,

read BOL article "Using @@ERROR" It has good examples. Basically you have to create a local variable to store results of @@ERROR function RIGHT after the statement. It returns the result from the last statement. If no errors then 0. In case of erors it returns an error number.

Regards,Yelena Varsha

JMarx Default port Points: 1435 More actions · Answer 2

Unfortunately, it appears XPSMTP returns successful even if it had an error.

declare @rc int

exec @rc = master.dbo.xp_smtp_sendmail ...

select Error = @@ERROR

select RC = @rc

GO

@@ERROR returns 0, while @rc is 1 (indicating an error) and the Messages pane shows the error: "Error: connecting to server 172.x.x.x"

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Did you check the Event Viewer Application Log if this error was logged? In this case you can run a daily/hourly job to read the event log and populate your table? it is only so much that could be done in case of the third-party plug-ins. The Query Analyzer as a front end probably gets a bubbled unhandled (or handled) error from XPSMTP.DLL but does not store it.

Regards,Yelena Varsha

Lowell SSC Guru Points: 323518 More actions · Answer 4

you should change your design a bit;

your update trigger should populate a table of emails to be sent, and a job that runs every minute or five minutes or whatever should go thru the email table for unsent emails. that way the trigger runs for milliseconds.

a trigger waiting for an email to complete might cause locks on the table you would not anticipate, and cause issues in the future. if you are sending an email to a slow server, or something that has huge attachments, the email might take seconds or minutes tosend.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JMarx Default port Points: 1435 More actions · Answer 5

Yelena, the application log doesn't have any related entries.

I noticed xpsmtp can output errors to a file. By naming the file based on a unique identifier, I can match it with the email. The question then, is how much of a performance hit might I see? This particular trigger won't see much action (dozens of hits a week), but will be a template for others in the future..

JMarx Default port Points: 1435 More actions · Answer 6

Good idea, Lowell. Thankfully other projects have kept me away, so I don't have too much re-engineering to do. 🙂