April 11, 2009 at 11:56 pm
I was using one stored procedure in one agent job and it was working but yesterday it failed and error i checked was stored procedure not found how do i checked that my sotred procedure is droped at so and so date
April 12, 2009 at 4:40 am
You can run an if exists query on information_schema.rutines that checks if there is a stored procedure with a specific name, but I don’t think that you should. Stored procedure shouldn’t disappear. Something like that should be very rare and I don’t think that you should check for the existence of a procedure each time that you want to activate one.
As for getting information about stored procedure that was dropped – By default SQL Server doesn’t keep track on dropped objects, so unfortunately I don’t think that you can get this information without creating a mechanism that stores this information first. You can create a DDL trigger that will log each DDL statements, but this will help you in the future and won’t help you to find out about something that already happened.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 13, 2009 at 6:57 am
Also cross check if you using the correct database under the database option in the SQL agent job. There is always a possibility that you are looking for a stored procedure in a different database.
However if thats not the case, we do not have any option in place to know who dropped the objects other than setting the DDL triggers in place as adviced by Adi, but will come in handy only once implemented.
Regards,
Rajini
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply