SQL SERVER 2005 Structural changes monitor with C#

[Graville]

All,

OK wasn't sure where to post this one but this should hopefully be ok.
I am looking for a way to streamline some of the process within our
dev team. One of the areas that often takes time and gets missed by
the guys is the generation of SQL Scripts and checking them into
sourcecontrol.

I know that using SQL Server profiler you directly trace ALTER, DELETE
and CREATE statements on all objects in your database. You can also
record these to a table and replay at a later date. What I would like
to do is create a windows service that I can install on the DB server
which will monitor these events (not from the table but as they
happen). When a stored procedure is altered a notification of the
change is sent to the dev team who will have a client installed and
the change will be automatically commited to source control. The idea
is more rounded than that but you get the idea.

Anyways, the part I am having trouble on is understanding how I can
hook into Object:Altered events. Is there a .NET API? Is it WMI?

Hope someone can point me in the right direction,

Dave Hanson

 

[Robbe Morris - MVP C#]

For stored procs, you could just have something set up
to generate a single script for all the procs in order
by dependency. Then, just let your devs run
it when ever you notify them.

http://www.eggheadcafe.com/articles/20030609.asp

Does your process of communicating database changes
really need to be this elaborate?

Should your database layer have some sort of
self updater in its own code? Check for version
and run various scripts to go from version 1.0
up through 1.4.1?

Isn't your end client app for the end user going
to need something like this anyway?

 

[Graville]

For stored procs, you could just have something set up
to generate a single script for all the procs in order
by dependency. Then, just let your devs run
it when ever you notify them.

http://www.eggheadcafe.com/articles/20030609.asp

Does your process of communicating database changes
really need to be this elaborate?

Should your database layer have some sort of
self updater in its own code? Check for version
and run various scripts to go from version 1.0
up through 1.4.1?

Isn't your end client app for the end user going
to need something like this anyway?

--
Robbe Morris
EggHeadCafe.comhttp://www.eggheadcafe.com/articles/adonet_source_code_generator.asp











- Show quoted text -

Ok let me put it another way. Let say that I want to monitor all data
inserts/updates/deletes as well as structural changes automatically
using the same technique that SQL Profiler seems to be able to
acheive. Then at some user specified time or event I can take all the
scripts logged on my development database and execute them on a new
database say on a build server. The end purpose of this say is that I
could plug this mechanism into an automated build manager like
CruiseControl or MSBuild.

So the key issue I am trying to figure out is how SQL Server Profiler
is intercepting the commited SQL batch statements. Any ideas?