SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Missing SP Alert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SamC
White Water Yakist

USA
3467 Posts

Posted - 09/13/2004 :  07:04:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/13/2004 :  07:23:21  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 09/13/2004 :  07:35:16  Show Profile  Reply with Quote
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
Go to Top of Page

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 09/13/2004 :  09:04:40  Show Profile  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 09/13/2004 :  09:20:31  Show Profile  Reply with Quote
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
Go to Top of Page

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 09/13/2004 :  09:26:15  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/13/2004 :  13:24:34  Show Profile  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 09/13/2004 :  13:43:58  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/13/2004 :  13:58:27  Show Profile  Reply with Quote
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
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/14/2004 :  00:03:38  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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?

Edited by - jen on 09/14/2004 00:04:33
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 09/14/2004 :  08:52:58  Show Profile  Reply with Quote
How do I "Create an Alert" ?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 09/14/2004 :  08:59:33  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
sp_add_alert

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 09/14/2004 :  09:08:12  Show Profile  Reply with Quote
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' ]
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/14/2004 :  22:59:21  Show Profile  Send jen a Yahoo! Message  Reply with Quote
yap that's the message id...

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000