471,082 Members | 795 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,082 software developers and data experts.

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 1844
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.