468,525 Members | 2,130 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,525 developers. It's quick & easy.

System event triggers

Hi, I have several independent, system components that write audit
events to a database locally via ODBC and remotely via TCP, and I have
a requirement to audit when the database is available for modification.
First, does SQL Server have a concept of starting/stopping specific
database instances? Or does my database instance "stop" only when the
NT service MSSQLSERVER stops?

Second, Oracle has system event triggers, e.g. AFTER STARTUP, but I
can't find an equivalent in SQL Server 2000. What alternatives are
there?

Note that I can't rely on the MSSQLSERVER start/stop events in the NT
Application Event log, as the events need to be inserted "in-band" into
my database's event table.

TIA,
Josh

Jul 24 '06 #1
1 1748
you can take a database offline or make it single user only

USE master
GO
ALTER DATABASE pubs
SET OFFLINE--use ONLINE to make it available again

>>Oracle has system event triggers, e.g. AFTER STARTUP, but I
can't find an equivalent in SQL Server 2000.
Well you can create a procedure and have it execute every time the SQL
Server is restarted
The procedure has to be created in the master database, after it is
created you have to use sp_procoption to have the procedure execute
when SQL Server starts up

--Let's create our procedure
USE master
GO
CREATE PROCEDURE prStartUp
AS
SELECT GETDATE()
--You would do something real here
--like deleting the data
GO
--Make the procedure execute when the server starts up
sp_procoption prStartUp,startup,'on'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
josh wrote:
Hi, I have several independent, system components that write audit
events to a database locally via ODBC and remotely via TCP, and I have
a requirement to audit when the database is available for modification.
First, does SQL Server have a concept of starting/stopping specific
database instances? Or does my database instance "stop" only when the
NT service MSSQLSERVER stops?

Second, Oracle has system event triggers, e.g. AFTER STARTUP, but I
can't find an equivalent in SQL Server 2000. What alternatives are
there?

Note that I can't rely on the MSSQLSERVER start/stop events in the NT
Application Event log, as the events need to be inserted "in-band" into
my database's event table.

TIA,
Josh
Jul 24 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Mats-Lennart Hansson | last post: by
19 posts views Thread by Daniela Roman | last post: by
3 posts views Thread by SimonG via AccessMonster.com | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.