Table Managment

Table Managment

Post by Peter Nola » Sat, 19 Jul 2003 12:13:23



Hello,

I have posted this before however I was not too clear on
what I want.

We have a team of 7 programmers and myself. The
programmers can make changes to the database structure,
this has been agreed by our managment and cannot by
changed. We do not have funds to buy in a third part tool
and our profile is used for another process.

Anyway here is the question. Is there a way of monitoring
structure changes that the programmers make ?

i.e some after triggers in the msdb database ?, or some
other methods ?

All I need to know is what has been changed rather than
who made the change.

Thanks
Peter

 
 
 

Table Managment

Post by Jacco Schalkwij » Sat, 19 Jul 2003 13:14:07


You can't create triggers on system tables in SQL Server 2000, although this
functionality will be available in the next version of SQL server.

What you need is a proper development process where developers check their
changes into a source control system, and the database is build on a regular
basis from the source. If you can't achieve that because management won't
allow that, as it looks like in your case, I would advise you to buy SQL
Compare from www.red-gate.com, and schedule that at night to make snapshots
of your database so you can compare it from day to day.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:> Hello,

> I have posted this before however I was not too clear on
> what I want.

> We have a team of 7 programmers and myself. The
> programmers can make changes to the database structure,
> this has been agreed by our managment and cannot by
> changed. We do not have funds to buy in a third part tool
> and our profile is used for another process.

> Anyway here is the question. Is there a way of monitoring
> structure changes that the programmers make ?

> i.e some after triggers in the msdb database ?, or some
> other methods ?

> All I need to know is what has been changed rather than
> who made the change.

> Thanks
> Peter


 
 
 

Table Managment

Post by Amy » Sat, 19 Jul 2003 14:36:38


You can't create triggers on system tables.

You can run a trace using SQL profiler (you can run simultanious multiple
traces with SQL profiler) and filter the trace so that it will record only
DDL statements and the execution of relevant system stored procedures that
you want to monitor.
That will give you all the details you need but you'll pay the price of the
overhead...

HTH

Amy


Quote:> Hello,

> I have posted this before however I was not too clear on
> what I want.

> We have a team of 7 programmers and myself. The
> programmers can make changes to the database structure,
> this has been agreed by our managment and cannot by
> changed. We do not have funds to buy in a third part tool
> and our profile is used for another process.

> Anyway here is the question. Is there a way of monitoring
> structure changes that the programmers make ?

> i.e some after triggers in the msdb database ?, or some
> other methods ?

> All I need to know is what has been changed rather than
> who made the change.

> Thanks
> Peter

 
 
 

Table Managment

Post by mark baekda » Sat, 19 Jul 2003 16:50:46


check out www.dbghost.com - it's like red-gates sql
compare only better as it can be used to monitor your
source effectively with code verification, daily builds,
test deploys etc. It doesn't just compare, it makes sure
your database code is correct and used with a source-safe
becomes the ultimate in database change management.

Mark Baekdal

Quote:>-----Original Message-----
>You can't create triggers on system tables in SQL Server
2000, although this
>functionality will be available in the next version of
SQL server.

>What you need is a proper development process where

developers check their
>changes into a source control system, and the database is
build on a regular
>basis from the source. If you can't achieve that because
management won't
>allow that, as it looks like in your case, I would advise
you to buy SQL
>Compare from www.red-gate.com, and schedule that at night
to make snapshots
>of your database so you can compare it from day to day.

>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.


message

>> Hello,

>> I have posted this before however I was not too clear on
>> what I want.

>> We have a team of 7 programmers and myself. The
>> programmers can make changes to the database structure,
>> this has been agreed by our managment and cannot by
>> changed. We do not have funds to buy in a third part
tool
>> and our profile is used for another process.

>> Anyway here is the question. Is there a way of
monitoring
>> structure changes that the programmers make ?

>> i.e some after triggers in the msdb database ?, or some
>> other methods ?

>> All I need to know is what has been changed rather than
>> who made the change.

>> Thanks
>> Peter

>.

 
 
 

Table Managment

Post by mark baekda » Sat, 19 Jul 2003 16:54:44


check out www.dbghost.com - it's like red-gates sql
compare only better as it can be used to monitor your
source effectively with code verification, daily builds,
test deploys etc. It doesn't just compare, it makes sure
your database code is correct and used with a source-safe
becomes the ultimate in database change management.

Mark Baekdal

Quote:>-----Original Message-----
>You can't create triggers on system tables in SQL Server
2000, although this
>functionality will be available in the next version of
SQL server.

>What you need is a proper development process where

developers check their
>changes into a source control system, and the database is
build on a regular
>basis from the source. If you can't achieve that because
management won't
>allow that, as it looks like in your case, I would advise
you to buy SQL
>Compare from www.red-gate.com, and schedule that at night
to make snapshots
>of your database so you can compare it from day to day.

>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.


message

>> Hello,

>> I have posted this before however I was not too clear on
>> what I want.

>> We have a team of 7 programmers and myself. The
>> programmers can make changes to the database structure,
>> this has been agreed by our managment and cannot by
>> changed. We do not have funds to buy in a third part
tool
>> and our profile is used for another process.

>> Anyway here is the question. Is there a way of
monitoring
>> structure changes that the programmers make ?

>> i.e some after triggers in the msdb database ?, or some
>> other methods ?

>> All I need to know is what has been changed rather than
>> who made the change.

>> Thanks
>> Peter

>.

 
 
 

Table Managment

Post by Jacco Schalkwij » Sat, 19 Jul 2003 17:00:58


I'll ahve a look Mark :-)

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


> check out www.dbghost.com - it's like red-gates sql
> compare only better as it can be used to monitor your
> source effectively with code verification, daily builds,
> test deploys etc. It doesn't just compare, it makes sure
> your database code is correct and used with a source-safe
> becomes the ultimate in database change management.

> Mark Baekdal
> >-----Original Message-----
> >You can't create triggers on system tables in SQL Server
> 2000, although this
> >functionality will be available in the next version of
> SQL server.

> >What you need is a proper development process where
> developers check their
> >changes into a source control system, and the database is
> build on a regular
> >basis from the source. If you can't achieve that because
> management won't
> >allow that, as it looks like in your case, I would advise
> you to buy SQL
> >Compare from www.red-gate.com, and schedule that at night
> to make snapshots
> >of your database so you can compare it from day to day.

> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.


> message

> >> Hello,

> >> I have posted this before however I was not too clear on
> >> what I want.

> >> We have a team of 7 programmers and myself. The
> >> programmers can make changes to the database structure,
> >> this has been agreed by our managment and cannot by
> >> changed. We do not have funds to buy in a third part
> tool
> >> and our profile is used for another process.

> >> Anyway here is the question. Is there a way of
> monitoring
> >> structure changes that the programmers make ?

> >> i.e some after triggers in the msdb database ?, or some
> >> other methods ?

> >> All I need to know is what has been changed rather than
> >> who made the change.

> >> Thanks
> >> Peter

> >.

 
 
 

Table Managment

Post by Jacco Schalkwij » Sat, 19 Jul 2003 17:53:45


Mark,

SQLCompare might not promise as much as DBGhost, but at least it installs on
my machine, and DBGhost doesn't (I'm running Windows 2000 SP2 and SQL Server
2000 SP3a)

From the messages you have posted on this newsgroup, I assume you work at
DBGhost. So if you can arrange that:
1) You start using a decent installer that uses Windows installer if
available. You can either buy Wise (www.wise.com) or InstallShield
(www.installshield.com). What you now use look to me like the wizard that
comes with Visual Studio 6, and that's just not good enough for commercial
software.
2) Make sure that your installation doesn't restart my computer. It is that
I was really interested to see what it looks like, but normally the "You
will need to restart Windows..." message means the end of the installation
process and my interest in the product in question.
3) DBGhost doesn't try to overwrite system files. I think that DBGhost is
trying to install an old version of MDAC, but doesn't check correctly if the
version that is on the machine might be newer than the one it tries install
itself. MDAC is a notoriously difficult beast regarding versioning, as it
was almost impossible to determine which version you are on or to uninstall.
Things have improved somewhat with version 2.7, but not very greatly. The
best plan might be to use the version that comes with SQL Server 2000 SP3a
(2.7.1) and check before installation that the local machine is indeed on
that version.

If you think that I am moaning, please remember that _you_ are trying to
sell software and I am a potential customer who can't even install the
evaluation version of your product :-)

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


> check out www.dbghost.com - it's like red-gates sql
> compare only better as it can be used to monitor your
> source effectively with code verification, daily builds,
> test deploys etc. It doesn't just compare, it makes sure
> your database code is correct and used with a source-safe
> becomes the ultimate in database change management.

> Mark Baekdal
> >-----Original Message-----
> >You can't create triggers on system tables in SQL Server
> 2000, although this
> >functionality will be available in the next version of
> SQL server.

> >What you need is a proper development process where
> developers check their
> >changes into a source control system, and the database is
> build on a regular
> >basis from the source. If you can't achieve that because
> management won't
> >allow that, as it looks like in your case, I would advise
> you to buy SQL
> >Compare from www.red-gate.com, and schedule that at night
> to make snapshots
> >of your database so you can compare it from day to day.

> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.


> message

> >> Hello,

> >> I have posted this before however I was not too clear on
> >> what I want.

> >> We have a team of 7 programmers and myself. The
> >> programmers can make changes to the database structure,
> >> this has been agreed by our managment and cannot by
> >> changed. We do not have funds to buy in a third part
> tool
> >> and our profile is used for another process.

> >> Anyway here is the question. Is there a way of
> monitoring
> >> structure changes that the programmers make ?

> >> i.e some after triggers in the msdb database ?, or some
> >> other methods ?

> >> All I need to know is what has been changed rather than
> >> who made the change.

> >> Thanks
> >> Peter

> >.

 
 
 

Table Managment

Post by navs » Sat, 19 Jul 2003 17:57:34


what u need to do is contral the process
add comments at the top of the stored procedure and makes
sure when someone is rolling out sp on live systems they
add appropriate comments,trace is not a good idea coz it
cosumes quite lot resources.
something like this
alter pro test
as
/*
history
dated           changed by       comments
12/jul/2003     AB               new col in select list
13/jul/2003     cd               where clause changed.
.
.
.  
*/
Quote:>-----Original Message-----
>Hello,

>I have posted this before however I was not too clear on
>what I want.

>We have a team of 7 programmers and myself. The
>programmers can make changes to the database structure,
>this has been agreed by our managment and cannot by
>changed. We do not have funds to buy in a third part tool
>and our profile is used for another process.

>Anyway here is the question. Is there a way of monitoring
>structure changes that the programmers make ?

>i.e some after triggers in the msdb database ?, or some
>other methods ?

>All I need to know is what has been changed rather than
>who made the change.

>Thanks
>Peter
>.

 
 
 

Table Managment

Post by mark baekda » Sat, 19 Jul 2003 21:04:47


fair points, although it doesn't try to install MDAC and
I am getting wise ;-) You should follow it through
though, if you've read the NTL testimonial maybe you can
imagine the value this can bring to any database.

Quote:>-----Original Message-----
>Mark,

>SQLCompare might not promise as much as DBGhost, but at

least it installs on
Quote:>my machine, and DBGhost doesn't (I'm running Windows

2000 SP2 and SQL Server
Quote:>2000 SP3a)

>From the messages you have posted on this newsgroup, I
assume you work at
>DBGhost. So if you can arrange that:
>1) You start using a decent installer that uses Windows
installer if
>available. You can either buy Wise (www.wise.com) or
InstallShield
>(www.installshield.com). What you now use look to me

like the wizard that
Quote:>comes with Visual Studio 6, and that's just not good

enough for commercial
Quote:>software.
>2) Make sure that your installation doesn't restart my

computer. It is that
Quote:>I was really interested to see what it looks like, but
normally the "You
>will need to restart Windows..." message means the end
of the installation
>process and my interest in the product in question.
>3) DBGhost doesn't try to overwrite system files. I

think that DBGhost is
Quote:>trying to install an old version of MDAC, but doesn't

check correctly if the
Quote:>version that is on the machine might be newer than the

one it tries install
Quote:>itself. MDAC is a notoriously difficult beast regarding
versioning, as it
>was almost impossible to determine which version you are
on or to uninstall.
>Things have improved somewhat with version 2.7, but not
very greatly. The
>best plan might be to use the version that comes with

SQL Server 2000 SP3a
Quote:>(2.7.1) and check before installation that the local

machine is indeed on
>that version.

>If you think that I am moaning, please remember that
_you_ are trying to
>sell software and I am a potential customer who can't
even install the
>evaluation version of your product :-)

>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.



>> check out www.dbghost.com - it's like red-gates sql
>> compare only better as it can be used to monitor your
>> source effectively with code verification, daily
builds,
>> test deploys etc. It doesn't just compare, it makes
sure
>> your database code is correct and used with a source-
safe
>> becomes the ultimate in database change management.

>> Mark Baekdal
>> >-----Original Message-----
>> >You can't create triggers on system tables in SQL
Server
>> 2000, although this
>> >functionality will be available in the next version of
>> SQL server.

>> >What you need is a proper development process where
>> developers check their
>> >changes into a source control system, and the
database is
>> build on a regular
>> >basis from the source. If you can't achieve that
because
>> management won't
>> >allow that, as it looks like in your case, I would
advise
>> you to buy SQL
>> >Compare from www.red-gate.com, and schedule that at
night
>> to make snapshots
>> >of your database so you can compare it from day to
day.

>> >--
>> >Jacco Schalkwijk MCDBA, MCSD, MCSE
>> >Database Administrator
>> >Eurostop Ltd.


>> message

>> >> Hello,

>> >> I have posted this before however I was not too
clear on
>> >> what I want.

>> >> We have a team of 7 programmers and myself. The
>> >> programmers can make changes to the database
structure,
>> >> this has been agreed by our managment and cannot by
>> >> changed. We do not have funds to buy in a third part
>> tool
>> >> and our profile is used for another process.

>> >> Anyway here is the question. Is there a way of
>> monitoring
>> >> structure changes that the programmers make ?

>> >> i.e some after triggers in the msdb database ?, or
some
>> >> other methods ?

>> >> All I need to know is what has been changed rather
than
>> >> who made the change.

>> >> Thanks
>> >> Peter

>> >.

>.

 
 
 

Table Managment

Post by Jacco Schalkwij » Sat, 19 Jul 2003 23:02:53


I would be very happy with a tools that does automated database builds. We
have one product, but it is customized for 20 customers or so, with ongoing
development. So we actually really really need one. Drop me a  mail when you
have the fixed installation, we plan to take some time out from development
to put a proper development/build/test process in place in the next month or
so (or at least make a start with it).

I thought it was MDAC because that always screwed up the VB installations I
did some years ago. Ah, the bad ole days :-)


> fair points, although it doesn't try to install MDAC and
> I am getting wise ;-) You should follow it through
> though, if you've read the NTL testimonial maybe you can
> imagine the value this can bring to any database.

> >-----Original Message-----
> >Mark,

> >SQLCompare might not promise as much as DBGhost, but at
> least it installs on
> >my machine, and DBGhost doesn't (I'm running Windows
> 2000 SP2 and SQL Server
> >2000 SP3a)

> >From the messages you have posted on this newsgroup, I
> assume you work at
> >DBGhost. So if you can arrange that:
> >1) You start using a decent installer that uses Windows
> installer if
> >available. You can either buy Wise (www.wise.com) or
> InstallShield
> >(www.installshield.com). What you now use look to me
> like the wizard that
> >comes with Visual Studio 6, and that's just not good
> enough for commercial
> >software.
> >2) Make sure that your installation doesn't restart my
> computer. It is that
> >I was really interested to see what it looks like, but
> normally the "You
> >will need to restart Windows..." message means the end
> of the installation
> >process and my interest in the product in question.
> >3) DBGhost doesn't try to overwrite system files. I
> think that DBGhost is
> >trying to install an old version of MDAC, but doesn't
> check correctly if the
> >version that is on the machine might be newer than the
> one it tries install
> >itself. MDAC is a notoriously difficult beast regarding
> versioning, as it
> >was almost impossible to determine which version you are
> on or to uninstall.
> >Things have improved somewhat with version 2.7, but not
> very greatly. The
> >best plan might be to use the version that comes with
> SQL Server 2000 SP3a
> >(2.7.1) and check before installation that the local
> machine is indeed on
> >that version.

> >If you think that I am moaning, please remember that
> _you_ are trying to
> >sell software and I am a potential customer who can't
> even install the
> >evaluation version of your product :-)

> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.



> >> check out www.dbghost.com - it's like red-gates sql
> >> compare only better as it can be used to monitor your
> >> source effectively with code verification, daily
> builds,
> >> test deploys etc. It doesn't just compare, it makes
> sure
> >> your database code is correct and used with a source-
> safe
> >> becomes the ultimate in database change management.

> >> Mark Baekdal
> >> >-----Original Message-----
> >> >You can't create triggers on system tables in SQL
> Server
> >> 2000, although this
> >> >functionality will be available in the next version of
> >> SQL server.

> >> >What you need is a proper development process where
> >> developers check their
> >> >changes into a source control system, and the
> database is
> >> build on a regular
> >> >basis from the source. If you can't achieve that
> because
> >> management won't
> >> >allow that, as it looks like in your case, I would
> advise
> >> you to buy SQL
> >> >Compare from www.red-gate.com, and schedule that at
> night
> >> to make snapshots
> >> >of your database so you can compare it from day to
> day.

> >> >--
> >> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >> >Database Administrator
> >> >Eurostop Ltd.


> >> message

> >> >> Hello,

> >> >> I have posted this before however I was not too
> clear on
> >> >> what I want.

> >> >> We have a team of 7 programmers and myself. The
> >> >> programmers can make changes to the database
> structure,
> >> >> this has been agreed by our managment and cannot by
> >> >> changed. We do not have funds to buy in a third part
> >> tool
> >> >> and our profile is used for another process.

> >> >> Anyway here is the question. Is there a way of
> >> monitoring
> >> >> structure changes that the programmers make ?

> >> >> i.e some after triggers in the msdb database ?, or
> some
> >> >> other methods ?

> >> >> All I need to know is what has been changed rather
> than
> >> >> who made the change.

> >> >> Thanks
> >> >> Peter

> >> >.

> >.

 
 
 

Table Managment

Post by iko » Wed, 20 Aug 2003 14:05:09


I am interesting too. I download DBGhost but I can't install it.

Drop me a  mail when you have the fixed installation.

Originally posted by Jacco Schalkwijk

> I would be very happy with a tools that does automated database
> builds. We
> have one product, but it is customized for 20 customers or so,
> with ongoing
> development. So we actually really really need one. Drop me a  mail
> when you
> have the fixed installation, we plan to take some time out from
> development
> to put a proper development/build/test process in place in the next
> month or
> so (or at least make a start with it).

> I thought it was MDAC because that always screwed up the VB
> installations I
> did some years ago. Ah, the bad ole days :-)




> > fair points, although it doesn't try to install MDAC and
> > I am getting wise ;-) You should follow it through
> > though, if you've read the NTL testimonial maybe you can
> > imagine the value this can bring to any database.
> > >-----Original Message-----
> > >Mark,

> > >SQLCompare might not promise as much as DBGhost, but at
> > least it installs on
> > >my machine, and DBGhost doesn't (I'm running Windows
> > 2000 SP2 and SQL Server
> > >2000 SP3a)

> > >From the messages you have posted on this newsgroup, I
> > assume you work at
> > >DBGhost. So if you can arrange that:
> > >1) You start using a decent installer that uses Windows
> > installer if
> > >available. You can either buy Wise (www.wise.com) or
> > InstallShield
> > >(www.installshield.com). What you now use look to me
> > like the wizard that
> > >comes with Visual Studio 6, and that's just not good
> > enough for commercial
> > >software.
> > >2) Make sure that your installation doesn't restart my
> > computer. It is that
> > >I was really interested to see what it looks like, but
> > normally the "You
> > >will need to restart Windows..." message means the end
> > of the installation
> > >process and my interest in the product in question.
> > >3) DBGhost doesn't try to overwrite system files. I
> > think that DBGhost is
> > >trying to install an old version of MDAC, but doesn't
> > check correctly if the
> > >version that is on the machine might be newer than the
> > one it tries install
> > >itself. MDAC is a notoriously difficult beast regarding
> > versioning, as it
> > >was almost impossible to determine which version you are
> > on or to uninstall.
> > >Things have improved somewhat with version 2.7, but not
> > very greatly. The
> > >best plan might be to use the version that comes with
> > SQL Server 2000 SP3a
> > >(2.7.1) and check before installation that the local
> > machine is indeed on
> > >that version.

> > >If you think that I am moaning, please remember that
> > _you_ are trying to
> > >sell software and I am a potential customer who can't
> > even install the
> > >evaluation version of your product :-)

> > >--
> > >Jacco Schalkwijk MCDBA, MCSD, MCSE
> > >Database Administrator
> > >Eurostop Ltd.




> > >> check out www.dbghost.com - it's like red-gates sql
> > >> compare only better as it can be used to monitor your
> > >> source effectively with code verification, daily
> > builds,
> > >> test deploys etc. It doesn't just compare, it makes
> > sure
> > >> your database code is correct and used with a source-
> > safe
> > >> becomes the ultimate in database change management.

> > >> Mark Baekdal
> > >> >-----Original Message-----
> > >> >You can't create triggers on system tables in SQL
> > Server
> > >> 2000, although this
> > >> >functionality will be available in the next version of
> > >> SQL server.

> > >> >What you need is a proper development process where
> > >> developers check their
> > >> >changes into a source control system, and the
> > database is
> > >> build on a regular
> > >> >basis from the source. If you can't achieve that
> > because
> > >> management won't
> > >> >allow that, as it looks like in your case, I would
> > advise
> > >> you to buy SQL
> > >> >Compare from www.red-gate.com, and schedule that at
> > night
> > >> to make snapshots
> > >> >of your database so you can compare it from day to
> > day.

> > >> >--
> > >> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> > >> >Database Administrator
> > >> >Eurostop Ltd.

> > >> >"Peter Nolan"  wrote in
> > >> message


> > >> >> Hello,

> > >> >> I have posted this before however I was not too
> > clear on
> > >> >> what I want.

> > >> >> We have a team of 7 programmers and myself. The
> > >> >> programmers can make changes to the database
> > structure,
> > >> >> this has been agreed by our managment and cannot by
> > >> >> changed. We do not have funds to buy in a third part
> > >> tool
> > >> >> and our profile is used for another process.

> > >> >> Anyway here is the question. Is there a way of
> > >> monitoring
> > >> >> structure changes that the programmers make ?

> > >> >> i.e some after triggers in the msdb database ?, or
> > some
> > >> >> other methods ?

> > >> >> All I need to know is what has been changed rather
> > than
> > >> >> who made the change.

> > >> >> Thanks
> > >> >> Peter

> > >> >.

> > >.

--
Posted via http://dbforums.com