Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bpgupta
Yak Posting Veteran
75 Posts |
 Posted - 2007-05-31 : 02:57:17
|
| Hi All,We having the SQL SERVER 2000 Production server. We need to cofigure the alert on the production server that if any objects schema or SP got changed then server should fire the alert to all of DBA mailid.Is there any way to sent the alert.Thanks in advance.BPG |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-31 : 03:44:37
|
| Don't think so, but this is possible with SQL 2005Kristen |
 |
|
|
bpgupta
Yak Posting Veteran
75 Posts |
Posted - 2007-05-31 : 04:25:57
|
| you mean to say that there is no way in sql server 2k.We have upgraded the prod.Server to 2k5 , but we want to test the same it on staging server /Dev. server first which is still on 2k.So we will first upgrade the staging server to 2k5 then we can configure it.Please let us know how can we achive the things in 2k5.Thanks in advance. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-31 : 05:07:50
|
You have a Production Server on SQL 2005 and a Dev/QA server on SQL 2000? That sounds pretty scary  "Please let us know how can we achive the things in 2k5"You need to create "triggers" on the system tables in SQL 2005 to monitor any changesKristen |
|
|
|
bpgupta
Yak Posting Veteran
75 Posts |
Posted - 2007-05-31 : 08:46:57
|
| We are maintain the couple of production server without having the staging server which is very serious issue for us as DBA. But the DB size of production server is very large , so we can't afforad to have staging server for this kind of server. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-31 : 18:37:48
|
quote:
Originally posted by Kristen
You need to create "triggers" on the system tables in SQL 2005 to monitor any changes
I don't what is recommended but you don't have to do it this way. You can use DDL triggers. Not done it myself though keen to do so (if I can talk the senior DBA round  ).Just lifted from BoL:CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK ; I confess to a little yellow streak when it comes to interfering with system tables - a trigger on one is just a little too close to comfort for me. No evidence it is not a good idea it just gives me the heebee jeebies. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-31 : 18:38:23
|
quote:
Originally posted by pootle_flump
quote:
Originally posted by Kristen
You need to create "triggers" on the system tables in SQL 2005 to monitor any changes
I don't know what is recommended but you don't have to do it this way. You can use DDL triggers. Not done it myself though keen to do so (if I can talk the senior DBA round ).Just lifted from BoL:CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK ; I confess to a little yellow streak when it comes to interfering with system tables - a trigger on one is just a little too close to comfort for me. No evidence it is not a good idea it just gives me the heebee jeebies.
|
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-31 : 18:39:06
|
Ooops - wrong icon  |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-05-31 : 19:41:23
|
a DDL trigger is definitely the way to go here I think. again, they exist in 2k5 only.
www.elsasoft.org |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-01 : 02:50:35
|
| Sorry, I agree, that was what I meant. |
|
|
|
bpgupta
Yak Posting Veteran
75 Posts |
Posted - 2007-06-07 : 06:54:38
|
| In SQL2000 Can i use repository view to check is any thing change in tables or SP or View .If changed in any of the objects then it will sent alter mail to us. |
|
|
|
|
|
|
Get alert on modification of database objects