Missing SP Alert - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Missing SP Alert

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-13 : 07:04:47

Is there a way I can execute an SP (to email a warning to myself) if an SP is not found for any reason?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-13 : 07:23:21

i guess the only way that you can determine if an sproc is missing if it was invoked?

you can either integrate the mailing system you already have in sql in your application or monitor system errors

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-13 : 07:35:16

There are articles on SQL Team about how to send an email from SQL.

What I don't have is something like an http "404 not found" handler.

Any way to implement something similar in SQL when an SP is not found?

Sam

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-13 : 09:04:40

You could create a procedure that you call before calling any other procedure, passing in the name of the procedure you are about to call.
Something like this:

CREATE PROCEDURE dbo.uspCheckProcedure
(
@ProcName VARCHAR(255),
@Result BIT OUTPUT,
@ErrMsg VARCHAR(255) OUTPUT
)
AS
IF EXISTS
(
SELECT *
FROM sysobjects
WHERE xtype ='P' AND name LIKE @ProcName
)
BEGIN
SET @Result = 1
SET @ErrMsg = ''
END
ELSE
BEGIN
SET @Result = 0

SET @ErrMsg = @ProcName + ' Doesn''t exist.'
END

Mark

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-13 : 09:20:31

Thanks Mark but... Ack!

I was hoping I may be able to monitor or trap system errors and report via email if an SP not found error occured.

Sam

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-13 : 09:26:15

Yeah, I know it was a bit obvious and wouldn't be nice to implement!!
Bit of an unusual requirement! What's the background?

Mark

Kristen
Test

22859 Posts

Posted - 2004-09-13 : 13:24:34

What does your client-layer (e.g. ADO) say if you do

EXEC sp_NonExistentSProc

Perhaps it gives an error message/number unique to this issue?

Or were you meaning that you also need to catch:

EXEC MyGoodSProc

where "MyGoodSProc" does

CREATE MyGoodSProc
AS
EXEC sp_NonExistentSProc
IF @@ERROR <> 0 PRINT 'Error in sp_NonExistentSProc'
GO

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-13 : 13:43:58

I revised an proc - added a couple of new parameters, added a revision number to the name. I thought I caught all the references and deleted the old proc.

About a week later, I noticed a SQL job wasn't running properly. After considerable debugging, I noticed the job was calling the old proc.

Missed one.

Kristen
Test

22859 Posts

Posted - 2004-09-13 : 13:58:27

Well, this would be a bit of a bind to retro fit, but.

We have an SProc which lets call "LogIt"

This takes the name of the current SProc and anything you would like to log (we log a concatenation of all the parameters).

"LogIt" makes an entry in a Logging Table. It returns the ID (Identity) of the row it inserted. The newly inserted row is given an Error Code of -999.

The calling SProc stores the return code (ID of the Log record)

The calling SProc does its stuff and at the end calls "LogItUpdate" with the ID of the logged record, and an ErrorNo (0=No error). The log record is updated with the error code.

Any record in the log with ErrorNo=-999 indicates an SProcs which was called but had uncontrolled failure (syntax error; deadlock; SQL Server internal error). Other error codes, as assigned within each Sproc, indicate controlled failed - i.e. something trackable failed.

You do need to check @@ERROR after each "EXEC MySProc", and the MySProc return value, within an SProc to be sure to catch everything.

We have a report which lists all non-zero ErrorNo code in the Log Table. Its amazing how many thing we pick up with this - mostly things which do not impact the application, but which very well might in the future (e.g. if the application is changed such that the error is a) trapped or b) become ssignificant!

(We also store the Create and Update times on teh Log record - this gives us elapsed time for an SProc, and thus we can report on SProcs which are "Slow Running")

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-14 : 00:03:38

you can create an alert using error number 2812, this is invoked if a stored procedure is not found...
then include a mailing job as response?

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-14 : 08:52:58

How do I "Create an Alert" ?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-14 : 08:59:33

sp_add_alert

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-14 : 09:08:12

I've glanced at BOL. Not clear if Jen's error number 2812 maps into @message_id below?

sp_add_alert [ @name = ] 'name' 
    [ , [ @message_id = ] message_id ] 
    [ , [ @severity = ] severity ] 
    [ , [ @enabled = ] enabled ]
    [ , [ @delay_between_responses = ] delay_between_responses ] 
    [ , [ @notification_message = ] 'notification_message' ] 
    [ , [ @include_event_description_in = ] include_event_description_in ] 
    [ , [ @database_name = ] 'database' ] 
    [ , [ @event_description_keyword = ] 'event_description_keyword_pattern' ] 
    [ , { [ @job_id = ] job_id | [ @job_name = ] 'job_name' } ] 
    [ , [ @raise_snmp_trap = ] raise_snmp_trap ] 
    [ , [ @performance_condition = ] 'performance_condition' ] 
    [ , [ @category_name = ] 'category' ]

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-14 : 22:59:21

yap that's the message id...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources