Hello,
I need to execute certain procedures automatically when databases are create, renamed or deleted. I looked around in this forum and Internet but couldn't find anything. This is for SQL 2000.
Well, in SQL 2000 it's not as easy as in SQL 2005 where you could use DDL triggers to achieve this.
You could configure different Alerts in SQL Server 2000 to fire when certain messages are logged to the error log, in this case you could try possibly firing an alert when the error message # 1805 fires ("The CREATE DATABASE process is allocating 1.00 MB on disk 'blah'), but that would only capture newly created DBs. You could use the same method for databases that are attached, restored, deleted, etc., you'd just have to be sure to get error messages that are fired for each 'event'. Once you have the appropriate Alert configured, just set the alert to execute a SQL Agent job when it fires, and setup the Agent job to run the SQL code you want.
If you don't need the code to run exactly when the DB is created/deleted/attached/etc., you could run a trace that captures the appropriate events, then load the trace file nightly and scan for the appropriate event classes, then executing code appropriately as needed.
HTH,
|||Thanks a lot Chad. This is very helpful. I didn't think about alerts.
sql
No comments:
Post a Comment