I'm still trying to decide the "right" solution for auditing, but here's one
solution I've been looking at. It uses dynamic SQL in order to be able to
handle changes to the underlying tables without changes. There's also some
code (not included) that sets up the auditing table based on the underlying
table.
My question: I notice this results in lots of "updates" where nothing
changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
statement", which is not the same thing at all!
Any suggestions on the best way to say "insert this record, but only if
something really changed"?
-- start off the SQL
select @.sql = 'insert mauryaudit (transactionType, transactionUser,
transactionDate' + CHAR(13) + CHAR(10)
-- add the field list
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close the field list and start the select statement
select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
@.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
-- continue the select statement
select @.field = 0
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close it off
select @.sql = @.sql + ' from #ins i'
exec (@.sql)
See
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html
And probably all the articles unser triggers on www.nigelrivett.net.
To only log changes you will have to check the before and after values of
each field.
Not that you will have problems if there is a blob in the table.
http://www.nigelrivett.net/Columns_updated.html
And has bigger problems if the column order has moved due to an alter table.
columns_updated also gets a bit awkward for more than 32 columns
"Maury Markowitz" wrote:
> I'm still trying to decide the "right" solution for auditing, but here's one
> solution I've been looking at. It uses dynamic SQL in order to be able to
> handle changes to the underlying tables without changes. There's also some
> code (not included) that sets up the auditing table based on the underlying
> table.
> My question: I notice this results in lots of "updates" where nothing
> changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
> statement", which is not the same thing at all!
> Any suggestions on the best way to say "insert this record, but only if
> something really changed"?
> -- start off the SQL
> select @.sql = 'insert mauryaudit (transactionType, transactionUser,
> transactionDate' + CHAR(13) + CHAR(10)
> -- add the field list
> select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
> INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close the field list and start the select statement
> select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
> @.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
> -- continue the select statement
> select @.field = 0
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close it off
> select @.sql = @.sql + ' from #ins i'
> exec (@.sql)
|||"Nigel Rivett" wrote:
> See
> http://www.nigelrivett.net/AuditTrailTrigger.html
> http://www.nigelrivett.net/GenerateTriggerForAudit.html
If I am reading them correctly, the "trick" is to join on a per-column
basis, thereby generating no inserts for those fields that do not have an
actual change. However it is not so clear how to do this in my trigger, where
the entire change "set" is entered as a single row instead of looping.
I could add another loop to check to see if any columns actually changed
(comparing inserted and deleted as you do), but it would see that it would be
much more effeceint to do this in a single SQL statement. I could write a
loop/exec to do that, but I'm curious if you could suggest a better way to
handle that? Is there some SQL that will compare one entire row with another
without listing every column?
Maury
|||"Nigel Rivett" wrote:
I tried the obvious and expanded the loop to build three portions of the
insert...
1) the INSERT portion with the field list
2) the SELECT portion taking the values from inserted
3) the WHERE section comparing inserted and deleted
Sadly the result is >8000 chars, and fails. Is there some way to make the
statement significantly shorter? The checks for before/after nulls takes up
most of the room, is there some easier way to do this with the equality
check? Perhaps using ANSI/non-ANSI nulls?
Showing posts with label auditing. Show all posts
Showing posts with label auditing. Show all posts
Monday, March 19, 2012
Auditing: some useful (?) code and a question
I'm still trying to decide the "right" solution for auditing, but here's one
solution I've been looking at. It uses dynamic SQL in order to be able to
handle changes to the underlying tables without changes. There's also some
code (not included) that sets up the auditing table based on the underlying
table.
My question: I notice this results in lots of "updates" where nothing
changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
statement", which is not the same thing at all!
Any suggestions on the best way to say "insert this record, but only if
something really changed"?
-- start off the SQL
select @.sql = 'insert mauryaudit (transactionType, transactionUser,
transactionDate' + CHAR(13) + CHAR(10)
-- add the field list
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close the field list and start the select statement
select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
@.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
-- continue the select statement
select @.field = 0
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close it off
select @.sql = @.sql + ' from #ins i'
exec (@.sql)See
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html
And probably all the articles unser triggers on www.nigelrivett.net.
To only log changes you will have to check the before and after values of
each field.
Not that you will have problems if there is a blob in the table.
http://www.nigelrivett.net/Columns_updated.html
And has bigger problems if the column order has moved due to an alter table.
columns_updated also gets a bit awkward for more than 32 columns
"Maury Markowitz" wrote:
> I'm still trying to decide the "right" solution for auditing, but here's one
> solution I've been looking at. It uses dynamic SQL in order to be able to
> handle changes to the underlying tables without changes. There's also some
> code (not included) that sets up the auditing table based on the underlying
> table.
> My question: I notice this results in lots of "updates" where nothing
> changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
> statement", which is not the same thing at all!
> Any suggestions on the best way to say "insert this record, but only if
> something really changed"?
> -- start off the SQL
> select @.sql = 'insert mauryaudit (transactionType, transactionUser,
> transactionDate' + CHAR(13) + CHAR(10)
> -- add the field list
> select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
> INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close the field list and start the select statement
> select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
> @.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
> -- continue the select statement
> select @.field = 0
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close it off
> select @.sql = @.sql + ' from #ins i'
> exec (@.sql)|||"Nigel Rivett" wrote:
> See
> http://www.nigelrivett.net/AuditTrailTrigger.html
> http://www.nigelrivett.net/GenerateTriggerForAudit.html
If I am reading them correctly, the "trick" is to join on a per-column
basis, thereby generating no inserts for those fields that do not have an
actual change. However it is not so clear how to do this in my trigger, where
the entire change "set" is entered as a single row instead of looping.
I could add another loop to check to see if any columns actually changed
(comparing inserted and deleted as you do), but it would see that it would be
much more effeceint to do this in a single SQL statement. I could write a
loop/exec to do that, but I'm curious if you could suggest a better way to
handle that? Is there some SQL that will compare one entire row with another
without listing every column?
Maury|||"Nigel Rivett" wrote:
I tried the obvious and expanded the loop to build three portions of the
insert...
1) the INSERT portion with the field list
2) the SELECT portion taking the values from inserted
3) the WHERE section comparing inserted and deleted
Sadly the result is >8000 chars, and fails. Is there some way to make the
statement significantly shorter? The checks for before/after nulls takes up
most of the room, is there some easier way to do this with the equality
check? Perhaps using ANSI/non-ANSI nulls?
solution I've been looking at. It uses dynamic SQL in order to be able to
handle changes to the underlying tables without changes. There's also some
code (not included) that sets up the auditing table based on the underlying
table.
My question: I notice this results in lots of "updates" where nothing
changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
statement", which is not the same thing at all!
Any suggestions on the best way to say "insert this record, but only if
something really changed"?
-- start off the SQL
select @.sql = 'insert mauryaudit (transactionType, transactionUser,
transactionDate' + CHAR(13) + CHAR(10)
-- add the field list
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close the field list and start the select statement
select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
@.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
-- continue the select statement
select @.field = 0
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close it off
select @.sql = @.sql + ' from #ins i'
exec (@.sql)See
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html
And probably all the articles unser triggers on www.nigelrivett.net.
To only log changes you will have to check the before and after values of
each field.
Not that you will have problems if there is a blob in the table.
http://www.nigelrivett.net/Columns_updated.html
And has bigger problems if the column order has moved due to an alter table.
columns_updated also gets a bit awkward for more than 32 columns
"Maury Markowitz" wrote:
> I'm still trying to decide the "right" solution for auditing, but here's one
> solution I've been looking at. It uses dynamic SQL in order to be able to
> handle changes to the underlying tables without changes. There's also some
> code (not included) that sets up the auditing table based on the underlying
> table.
> My question: I notice this results in lots of "updates" where nothing
> changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
> statement", which is not the same thing at all!
> Any suggestions on the best way to say "insert this record, but only if
> something really changed"?
> -- start off the SQL
> select @.sql = 'insert mauryaudit (transactionType, transactionUser,
> transactionDate' + CHAR(13) + CHAR(10)
> -- add the field list
> select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
> INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close the field list and start the select statement
> select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
> @.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
> -- continue the select statement
> select @.field = 0
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close it off
> select @.sql = @.sql + ' from #ins i'
> exec (@.sql)|||"Nigel Rivett" wrote:
> See
> http://www.nigelrivett.net/AuditTrailTrigger.html
> http://www.nigelrivett.net/GenerateTriggerForAudit.html
If I am reading them correctly, the "trick" is to join on a per-column
basis, thereby generating no inserts for those fields that do not have an
actual change. However it is not so clear how to do this in my trigger, where
the entire change "set" is entered as a single row instead of looping.
I could add another loop to check to see if any columns actually changed
(comparing inserted and deleted as you do), but it would see that it would be
much more effeceint to do this in a single SQL statement. I could write a
loop/exec to do that, but I'm curious if you could suggest a better way to
handle that? Is there some SQL that will compare one entire row with another
without listing every column?
Maury|||"Nigel Rivett" wrote:
I tried the obvious and expanded the loop to build three portions of the
insert...
1) the INSERT portion with the field list
2) the SELECT portion taking the values from inserted
3) the WHERE section comparing inserted and deleted
Sadly the result is >8000 chars, and fails. Is there some way to make the
statement significantly shorter? The checks for before/after nulls takes up
most of the room, is there some easier way to do this with the equality
check? Perhaps using ANSI/non-ANSI nulls?
Auditing without having to use SQL Profiler
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.
You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_side...sql_server.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.c om...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.
|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. More
> info and code at:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.c om...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.
You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_side...sql_server.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.c om...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.
|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. More
> info and code at:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.c om...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.
Auditing without having to use SQL Profiler
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. More
> info and code at:
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. More
> info and code at:
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.
Auditing without having to use SQL Profiler
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_sid..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK
2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. Mo
re
> info and code at:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_sid..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK
2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. Mo
re
> info and code at:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.
Auditing Users on various SQL Servers
We have about 50 SQL Servers (most 2000 but some 2005) in different domains and behind firewalls and we implementing the PCI rules for the use of credit card data. One thing we need to do is to audit users and their rights. I have a stored proc that can run each night and record users and permissions.
My main problem is trying to bring all that data together on a central box and then parse through it to see if I have any offenders. The parsing is not the issue, but the polling is. I could use the SA account or create a audit account with SA rights but both of those solutions go against the PCI mantra.
Can someone who has done this give me some guidance as to what you did. I know that there are some third party tools that would probably do this, but my department is on a shallow budget and my overtime is free.
Any suggestions would be greatly appreciated. Thank you!
Jim Youmans
St. Louis
An ideea is to write an event log in operating system ( you can view it with EventViewer) when the user use credit card data then collect that data from all 50 SQL Servers in a SQL database and analyze it (see).|||Bets thing would be the other way around. Start an application / job on the server themselves and push the data to a collecting server. Rather than providing your application with password of each server you can just provide the application with the password of the collecting server assuming you run the application in a trusted context on the remote servers.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||You may be best served to explore some of the third party auditing products. They are robust and well vetted. Some may be PCI 'approved'.
My experience with Luminigents and ApexSQL's products leads me to belief that they are very cost-effective.
Audit Tools
ApexSQL Audit http://www.apexsql.com/sql_tools_audit.asp
AuditDatabase (Free Web based trigger generation) http://www.auditdatabase.com/
Lumigent Audit DB http://www.lumigent.com/products/auditdb.html
OmniAudit http://www.krell-software.com/omniaudit/index.asp
SQLLog http://www.rlpsoftware.com/mainframe.asp?contents=SQLLog.asp&mainmenu=SQLLog&submenu=Info
Upscene SQL Log Manager http://www.upscene.com/index.htm?./products/audit/mssqllm_main.htm
DB Audit Expert http://www.softtreetech.com/dbaudit/
Auditing user creation
Hi,
Is it possible to find out ‘Who’ created a specific database user in dat
abase?
Thanks
bbHi,
You need to enable the server side trace for the event "Audit Addlogin
event". This schedule needs to run and log into a file all the time. Once a
week you could
load the trace log to a table and look for the info.
Thanks
Hari
SQL Server MVP
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:649819CB-8A29-4619-A9DE-C2A27E627B7E@.microsoft.com...
> Hi,
> Is it possible to find out 'Who' created a specific database user in
> database?
> Thanks
> bb
Is it possible to find out ‘Who’ created a specific database user in dat
abase?
Thanks
bbHi,
You need to enable the server side trace for the event "Audit Addlogin
event". This schedule needs to run and log into a file all the time. Once a
week you could
load the trace log to a table and look for the info.
Thanks
Hari
SQL Server MVP
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:649819CB-8A29-4619-A9DE-C2A27E627B7E@.microsoft.com...
> Hi,
> Is it possible to find out 'Who' created a specific database user in
> database?
> Thanks
> bb
Auditing Tools: Lumigent Entegra vs Ecora Enterprise Auditing
Has anyone used either auditing tools? If so, any info you can share about
either/both would be great.
Thanks
Doug
Guerena
> Has anyone used either auditing tools? If so, any info you can share
about
> either/both would be great.
Add another new - new on the market... MSSQLLogManager -
www.upscene.com
:-)
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
either/both would be great.
Thanks
Doug
Guerena
> Has anyone used either auditing tools? If so, any info you can share
about
> either/both would be great.
Add another new - new on the market... MSSQLLogManager -
www.upscene.com
:-)
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
Auditing Tool
The department here is looking for a 3rd party auditing tool that can monito
r
the SQL 2000 data base for changed, deleted or added records. One utility
available is Apex SQL
Log that costs about $700.00. Anyone know of this or any other such utility
?
Thanks for everyone being there to help.Hi,
See Logexplorer from www.Lumigent.com
Thanks
Hari
SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:86F241F5-03E4-4491-ACEF-5AC5943628D8@.microsoft.com...
> The department here is looking for a 3rd party auditing tool that can
> monitor
> the SQL 2000 data base for changed, deleted or added records. One utility
> available is Apex SQL
> Log that costs about $700.00. Anyone know of this or any other such
> utility?
> Thanks for everyone being there to help.
>
r
the SQL 2000 data base for changed, deleted or added records. One utility
available is Apex SQL
Log that costs about $700.00. Anyone know of this or any other such utility
?
Thanks for everyone being there to help.Hi,
See Logexplorer from www.Lumigent.com
Thanks
Hari
SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:86F241F5-03E4-4491-ACEF-5AC5943628D8@.microsoft.com...
> The department here is looking for a 3rd party auditing tool that can
> monitor
> the SQL 2000 data base for changed, deleted or added records. One utility
> available is Apex SQL
> Log that costs about $700.00. Anyone know of this or any other such
> utility?
> Thanks for everyone being there to help.
>
Auditing Tool
The department here is looking for a 3rd party auditing tool that can monitor
the SQL 2000 data base for changed, deleted or added records. One utility
available is Apex SQL
Log that costs about $700.00. Anyone know of this or any other such utility?
Thanks for everyone being there to help.
Hi,
See Logexplorer from www.Lumigent.com
Thanks
Hari
SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:86F241F5-03E4-4491-ACEF-5AC5943628D8@.microsoft.com...
> The department here is looking for a 3rd party auditing tool that can
> monitor
> the SQL 2000 data base for changed, deleted or added records. One utility
> available is Apex SQL
> Log that costs about $700.00. Anyone know of this or any other such
> utility?
> Thanks for everyone being there to help.
>
the SQL 2000 data base for changed, deleted or added records. One utility
available is Apex SQL
Log that costs about $700.00. Anyone know of this or any other such utility?
Thanks for everyone being there to help.
Hi,
See Logexplorer from www.Lumigent.com
Thanks
Hari
SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:86F241F5-03E4-4491-ACEF-5AC5943628D8@.microsoft.com...
> The department here is looking for a 3rd party auditing tool that can
> monitor
> the SQL 2000 data base for changed, deleted or added records. One utility
> available is Apex SQL
> Log that costs about $700.00. Anyone know of this or any other such
> utility?
> Thanks for everyone being there to help.
>
Auditing Tool
The department here is looking for a 3rd party auditing tool that can monitor
the SQL 2000 data base for changed, deleted or added records. One utility
available is Apex SQL
Log that costs about $700.00. Anyone know of this or any other such utility?
Thanks for everyone being there to help.Hi,
See Logexplorer from www.Lumigent.com
Thanks
Hari
SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:86F241F5-03E4-4491-ACEF-5AC5943628D8@.microsoft.com...
> The department here is looking for a 3rd party auditing tool that can
> monitor
> the SQL 2000 data base for changed, deleted or added records. One utility
> available is Apex SQL
> Log that costs about $700.00. Anyone know of this or any other such
> utility?
> Thanks for everyone being there to help.
>
the SQL 2000 data base for changed, deleted or added records. One utility
available is Apex SQL
Log that costs about $700.00. Anyone know of this or any other such utility?
Thanks for everyone being there to help.Hi,
See Logexplorer from www.Lumigent.com
Thanks
Hari
SQL Server MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:86F241F5-03E4-4491-ACEF-5AC5943628D8@.microsoft.com...
> The department here is looking for a 3rd party auditing tool that can
> monitor
> the SQL 2000 data base for changed, deleted or added records. One utility
> available is Apex SQL
> Log that costs about $700.00. Anyone know of this or any other such
> utility?
> Thanks for everyone being there to help.
>
auditing the table
Hi,
I would like to audit table but I do not want to use
profiler. I need to know NT user name not just sql server
standard login for the user. The problem is that somebody
ocasssionally deletes the data and I would like to find
out who is doing it.
Any view is appreciated.You could create a TRIGGER on the table. eg :-
CREATE TRIGGER reminder
ON titles
FOR DELETE
AS
EXEC master..xp_sendmail 'ToYou',
'Someone just deleted a row'
GO
this could easily be enhanced to log to a table, you can capture the user
name of who did the deletion by capturing USER_NAME
HTH
Ryan Waight, MCDBA, MCSE
"Mirna" <mstojsic@.hotmail.com> wrote in message
news:2739901c38f3d$83e5d340$a601280a@.phx.gbl...
> Hi,
> I would like to audit table but I do not want to use
> profiler. I need to know NT user name not just sql server
> standard login for the user. The problem is that somebody
> ocasssionally deletes the data and I would like to find
> out who is doing it.
> Any view is appreciated.
>
I would like to audit table but I do not want to use
profiler. I need to know NT user name not just sql server
standard login for the user. The problem is that somebody
ocasssionally deletes the data and I would like to find
out who is doing it.
Any view is appreciated.You could create a TRIGGER on the table. eg :-
CREATE TRIGGER reminder
ON titles
FOR DELETE
AS
EXEC master..xp_sendmail 'ToYou',
'Someone just deleted a row'
GO
this could easily be enhanced to log to a table, you can capture the user
name of who did the deletion by capturing USER_NAME
HTH
Ryan Waight, MCDBA, MCSE
"Mirna" <mstojsic@.hotmail.com> wrote in message
news:2739901c38f3d$83e5d340$a601280a@.phx.gbl...
> Hi,
> I would like to audit table but I do not want to use
> profiler. I need to know NT user name not just sql server
> standard login for the user. The problem is that somebody
> ocasssionally deletes the data and I would like to find
> out who is doing it.
> Any view is appreciated.
>
Auditing tables
HI!
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swati
swati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>
|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot
>
|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>
|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>
|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table[vbcol=seagreen]
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot
>
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swati
swati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>
|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot
>
|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>
|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>
|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table[vbcol=seagreen]
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot
>
Auditing tables
HI!
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swatiswati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot[vbcol=seagreen]
>|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot[vbcol=seagreen]
>
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swatiswati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot[vbcol=seagreen]
>|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot[vbcol=seagreen]
>
Auditing tables
Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
>
> Regards,
> Swati
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> > swati
> > create trigger tru_MyTable on MyTable after update
> > as
> >
> > if @.@.ROWCOUNT = 0
> > return
> >
> > insert MyAuditTable
> > select
> > i.ID
> > , d.MyColumn
> > , i.MyColumn
> > from
> > inserted i
> > join
> > deleted d on d.ID = o.Id
> > go
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> > > HI!
> > >
> > > I want to audit some collumns of one of my huge table . I wrote a
> trigger
> > > for copying updated data and original data in audit table.
> > > But when I try to update multiple rows , it gives error of "cannot
> > update
> > > multiple rows. "
> > >
> > > Is there any easy way of auditing tables without using triggers . I
> donot
> > > want to use C2-audit .
> > >
> > > Regards,
> > > Swati
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>What error are you getting? Please post your actual trigger code and table
DDL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>> Thanks!! It works properly .
>> Regards,
>> Swati
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
>> > swati
>> > create trigger tru_MyTable on MyTable after update
>> > as
>> >
>> > if @.@.ROWCOUNT = 0
>> > return
>> >
>> > insert MyAuditTable
>> > select
>> > i.ID
>> > , d.MyColumn
>> > , i.MyColumn
>> > from
>> > inserted i
>> > join
>> > deleted d on d.ID = o.Id
>> > go
>> >
>> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
>> > news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> > > HI!
>> > >
>> > > I want to audit some collumns of one of my huge table . I wrote a
>> trigger
>> > > for copying updated data and original data in audit table.
>> > > But when I try to update multiple rows , it gives error of "cannot
>> > update
>> > > multiple rows. "
>> > >
>> > > Is there any easy way of auditing tables without using triggers . I
>> donot
>> > > want to use C2-audit .
>> > >
>> > > Regards,
>> > > Swati
>> > >
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> > Hi ,
> > I am trying trigger for after delete /insert and update .
> > using below trigger , unable to insert data in myaudittable ..
> > Pls suggest , what changes should I make ?
> >
> > Regards
> >
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> >> Thanks!! It works properly .
> >>
> >> Regards,
> >> Swati
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> >> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> >> > swati
> >> > create trigger tru_MyTable on MyTable after update
> >> > as
> >> >
> >> > if @.@.ROWCOUNT = 0
> >> > return
> >> >
> >> > insert MyAuditTable
> >> > select
> >> > i.ID
> >> > , d.MyColumn
> >> > , i.MyColumn
> >> > from
> >> > inserted i
> >> > join
> >> > deleted d on d.ID = o.Id
> >> > go
> >> >
> >> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> >> > news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> > > HI!
> >> > >
> >> > > I want to audit some collumns of one of my huge table . I wrote a
> >> trigger
> >> > > for copying updated data and original data in audit table.
> >> > > But when I try to update multiple rows , it gives error of
"cannot
> >> > update
> >> > > multiple rows. "
> >> > >
> >> > > Is there any easy way of auditing tables without using triggers . I
> >> donot
> >> > > want to use C2-audit .
> >> > >
> >> > > Regards,
> >> > > Swati
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
>
> Regards,
> Swati
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> > swati
> > create trigger tru_MyTable on MyTable after update
> > as
> >
> > if @.@.ROWCOUNT = 0
> > return
> >
> > insert MyAuditTable
> > select
> > i.ID
> > , d.MyColumn
> > , i.MyColumn
> > from
> > inserted i
> > join
> > deleted d on d.ID = o.Id
> > go
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> > > HI!
> > >
> > > I want to audit some collumns of one of my huge table . I wrote a
> trigger
> > > for copying updated data and original data in audit table.
> > > But when I try to update multiple rows , it gives error of "cannot
> > update
> > > multiple rows. "
> > >
> > > Is there any easy way of auditing tables without using triggers . I
> donot
> > > want to use C2-audit .
> > >
> > > Regards,
> > > Swati
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>What error are you getting? Please post your actual trigger code and table
DDL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>> Thanks!! It works properly .
>> Regards,
>> Swati
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
>> > swati
>> > create trigger tru_MyTable on MyTable after update
>> > as
>> >
>> > if @.@.ROWCOUNT = 0
>> > return
>> >
>> > insert MyAuditTable
>> > select
>> > i.ID
>> > , d.MyColumn
>> > , i.MyColumn
>> > from
>> > inserted i
>> > join
>> > deleted d on d.ID = o.Id
>> > go
>> >
>> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
>> > news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
>> > > HI!
>> > >
>> > > I want to audit some collumns of one of my huge table . I wrote a
>> trigger
>> > > for copying updated data and original data in audit table.
>> > > But when I try to update multiple rows , it gives error of "cannot
>> > update
>> > > multiple rows. "
>> > >
>> > > Is there any easy way of auditing tables without using triggers . I
>> donot
>> > > want to use C2-audit .
>> > >
>> > > Regards,
>> > > Swati
>> > >
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> > Hi ,
> > I am trying trigger for after delete /insert and update .
> > using below trigger , unable to insert data in myaudittable ..
> > Pls suggest , what changes should I make ?
> >
> > Regards
> >
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> >> Thanks!! It works properly .
> >>
> >> Regards,
> >> Swati
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> >> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> >> > swati
> >> > create trigger tru_MyTable on MyTable after update
> >> > as
> >> >
> >> > if @.@.ROWCOUNT = 0
> >> > return
> >> >
> >> > insert MyAuditTable
> >> > select
> >> > i.ID
> >> > , d.MyColumn
> >> > , i.MyColumn
> >> > from
> >> > inserted i
> >> > join
> >> > deleted d on d.ID = o.Id
> >> > go
> >> >
> >> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> >> > news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> >> > > HI!
> >> > >
> >> > > I want to audit some collumns of one of my huge table . I wrote a
> >> trigger
> >> > > for copying updated data and original data in audit table.
> >> > > But when I try to update multiple rows , it gives error of
"cannot
> >> > update
> >> > > multiple rows. "
> >> > >
> >> > > Is there any easy way of auditing tables without using triggers . I
> >> donot
> >> > > want to use C2-audit .
> >> > >
> >> > > Regards,
> >> > > Swati
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
Auditing table data changes
Hi,
Is there any RDBMS concept ,If i do any DML operations in any of table i need to know how many rows are inserted/updated/deleted in particular table. I dont want write any trigger to get those information.
because the table count nearly 142.
Moving to Transact-SQL from SSIS.|||Nope. Triggers are the only current method in 2005 and earlier.
You could pretty easily write a code generator using the system tables (sys.tables, sys.columns) once you determine how to do one of them, then just follow a common pattern.
|||Yes..My last question. i have done work around solution.
just loading data into table using bulk insert concept. after that used substring function to fixed the problem.
Thanks - Phil Brammer.
Auditing table access
We regularly load data into hundreds of tables in a reporting database.
I know that many of the tables were needed years ago for short-term
projects, and are no longer used. I'm trying to find a way to identify
which tables are never accessed by users, so that we can stop those
loads and drop the tables. For this purpose, if nobody SELECTs from a
table in 30 days, I can consider that table dead. And, if anyone
SELECTs from the table even once, I need to keep it around.
This is a high-usage database, so performance is key. I'd rather not
run a Profiler session for 30 days straight, because of the potential
impact on performance. Ideally, I'd like to find something like a
one-time trigger: a table is accessed, a trigger fires and adds that
table to a "Keep Me" list somewhere, then the trigger is disabled. I
don't think SELECT triggers exist in SQL2000, though.
Anyone have creative ideas on how to generate a list of used or unused
tables? Or is there a way I could do this with Profiler without
creating too much overhead?
Cheers!Hi stavros
"stavros" wrote:
> We regularly load data into hundreds of tables in a reporting database.
> I know that many of the tables were needed years ago for short-term
> projects, and are no longer used. I'm trying to find a way to identify
> which tables are never accessed by users, so that we can stop those
> loads and drop the tables. For this purpose, if nobody SELECTs from a
> table in 30 days, I can consider that table dead. And, if anyone
> SELECTs from the table even once, I need to keep it around.
> This is a high-usage database, so performance is key. I'd rather not
> run a Profiler session for 30 days straight, because of the potential
> impact on performance. Ideally, I'd like to find something like a
> one-time trigger: a table is accessed, a trigger fires and adds that
> table to a "Keep Me" list somewhere, then the trigger is disabled. I
> don't think SELECT triggers exist in SQL2000, though.
AFAIK profiler is the only tool that is likely to do this. You could run
multiple profiles that covers the whole period and process each profile
offline rather than having one single set of outputs to process at the end o
f
the period.
> Anyone have creative ideas on how to generate a list of used or unused
> tables? Or is there a way I could do this with Profiler without
> creating too much overhead?
>
This is where source code control is very advantages, just doing textual
searches for your tables will eliminate alot of them. Alternatively if you
use stored procedures, then you could script the stored procedures on their
own and do a textual search. If want to know which procedures are called
without using profiler you could add code that audits the procedure being
run, although this will be an overhead whilst you are collecting the
information and a potential bottleneck. it will also mean a code change.
> Cheers!
>
John
I know that many of the tables were needed years ago for short-term
projects, and are no longer used. I'm trying to find a way to identify
which tables are never accessed by users, so that we can stop those
loads and drop the tables. For this purpose, if nobody SELECTs from a
table in 30 days, I can consider that table dead. And, if anyone
SELECTs from the table even once, I need to keep it around.
This is a high-usage database, so performance is key. I'd rather not
run a Profiler session for 30 days straight, because of the potential
impact on performance. Ideally, I'd like to find something like a
one-time trigger: a table is accessed, a trigger fires and adds that
table to a "Keep Me" list somewhere, then the trigger is disabled. I
don't think SELECT triggers exist in SQL2000, though.
Anyone have creative ideas on how to generate a list of used or unused
tables? Or is there a way I could do this with Profiler without
creating too much overhead?
Cheers!Hi stavros
"stavros" wrote:
> We regularly load data into hundreds of tables in a reporting database.
> I know that many of the tables were needed years ago for short-term
> projects, and are no longer used. I'm trying to find a way to identify
> which tables are never accessed by users, so that we can stop those
> loads and drop the tables. For this purpose, if nobody SELECTs from a
> table in 30 days, I can consider that table dead. And, if anyone
> SELECTs from the table even once, I need to keep it around.
> This is a high-usage database, so performance is key. I'd rather not
> run a Profiler session for 30 days straight, because of the potential
> impact on performance. Ideally, I'd like to find something like a
> one-time trigger: a table is accessed, a trigger fires and adds that
> table to a "Keep Me" list somewhere, then the trigger is disabled. I
> don't think SELECT triggers exist in SQL2000, though.
AFAIK profiler is the only tool that is likely to do this. You could run
multiple profiles that covers the whole period and process each profile
offline rather than having one single set of outputs to process at the end o
f
the period.
> Anyone have creative ideas on how to generate a list of used or unused
> tables? Or is there a way I could do this with Profiler without
> creating too much overhead?
>
This is where source code control is very advantages, just doing textual
searches for your tables will eliminate alot of them. Alternatively if you
use stored procedures, then you could script the stored procedures on their
own and do a textual search. If want to know which procedures are called
without using profiler you could add code that audits the procedure being
run, although this will be an overhead whilst you are collecting the
information and a potential bottleneck. it will also mean a code change.
> Cheers!
>
John
Sunday, March 11, 2012
Auditing table access
We regularly load data into hundreds of tables in a reporting database.
I know that many of the tables were needed years ago for short-term
projects, and are no longer used. I'm trying to find a way to identify
which tables are never accessed by users, so that we can stop those
loads and drop the tables. For this purpose, if nobody SELECTs from a
table in 30 days, I can consider that table dead. And, if anyone
SELECTs from the table even once, I need to keep it around.
This is a high-usage database, so performance is key. I'd rather not
run a Profiler session for 30 days straight, because of the potential
impact on performance. Ideally, I'd like to find something like a
one-time trigger: a table is accessed, a trigger fires and adds that
table to a "Keep Me" list somewhere, then the trigger is disabled. I
don't think SELECT triggers exist in SQL2000, though.
Anyone have creative ideas on how to generate a list of used or unused
tables? Or is there a way I could do this with Profiler without
creating too much overhead?
Cheers!
Hi stavros
"stavros" wrote:
> We regularly load data into hundreds of tables in a reporting database.
> I know that many of the tables were needed years ago for short-term
> projects, and are no longer used. I'm trying to find a way to identify
> which tables are never accessed by users, so that we can stop those
> loads and drop the tables. For this purpose, if nobody SELECTs from a
> table in 30 days, I can consider that table dead. And, if anyone
> SELECTs from the table even once, I need to keep it around.
> This is a high-usage database, so performance is key. I'd rather not
> run a Profiler session for 30 days straight, because of the potential
> impact on performance. Ideally, I'd like to find something like a
> one-time trigger: a table is accessed, a trigger fires and adds that
> table to a "Keep Me" list somewhere, then the trigger is disabled. I
> don't think SELECT triggers exist in SQL2000, though.
AFAIK profiler is the only tool that is likely to do this. You could run
multiple profiles that covers the whole period and process each profile
offline rather than having one single set of outputs to process at the end of
the period.
> Anyone have creative ideas on how to generate a list of used or unused
> tables? Or is there a way I could do this with Profiler without
> creating too much overhead?
>
This is where source code control is very advantages, just doing textual
searches for your tables will eliminate alot of them. Alternatively if you
use stored procedures, then you could script the stored procedures on their
own and do a textual search. If want to know which procedures are called
without using profiler you could add code that audits the procedure being
run, although this will be an overhead whilst you are collecting the
information and a potential bottleneck. it will also mean a code change.
> Cheers!
>
John
I know that many of the tables were needed years ago for short-term
projects, and are no longer used. I'm trying to find a way to identify
which tables are never accessed by users, so that we can stop those
loads and drop the tables. For this purpose, if nobody SELECTs from a
table in 30 days, I can consider that table dead. And, if anyone
SELECTs from the table even once, I need to keep it around.
This is a high-usage database, so performance is key. I'd rather not
run a Profiler session for 30 days straight, because of the potential
impact on performance. Ideally, I'd like to find something like a
one-time trigger: a table is accessed, a trigger fires and adds that
table to a "Keep Me" list somewhere, then the trigger is disabled. I
don't think SELECT triggers exist in SQL2000, though.
Anyone have creative ideas on how to generate a list of used or unused
tables? Or is there a way I could do this with Profiler without
creating too much overhead?
Cheers!
Hi stavros
"stavros" wrote:
> We regularly load data into hundreds of tables in a reporting database.
> I know that many of the tables were needed years ago for short-term
> projects, and are no longer used. I'm trying to find a way to identify
> which tables are never accessed by users, so that we can stop those
> loads and drop the tables. For this purpose, if nobody SELECTs from a
> table in 30 days, I can consider that table dead. And, if anyone
> SELECTs from the table even once, I need to keep it around.
> This is a high-usage database, so performance is key. I'd rather not
> run a Profiler session for 30 days straight, because of the potential
> impact on performance. Ideally, I'd like to find something like a
> one-time trigger: a table is accessed, a trigger fires and adds that
> table to a "Keep Me" list somewhere, then the trigger is disabled. I
> don't think SELECT triggers exist in SQL2000, though.
AFAIK profiler is the only tool that is likely to do this. You could run
multiple profiles that covers the whole period and process each profile
offline rather than having one single set of outputs to process at the end of
the period.
> Anyone have creative ideas on how to generate a list of used or unused
> tables? Or is there a way I could do this with Profiler without
> creating too much overhead?
>
This is where source code control is very advantages, just doing textual
searches for your tables will eliminate alot of them. Alternatively if you
use stored procedures, then you could script the stored procedures on their
own and do a textual search. If want to know which procedures are called
without using profiler you could add code that audits the procedure being
run, although this will be an overhead whilst you are collecting the
information and a potential bottleneck. it will also mean a code change.
> Cheers!
>
John
Auditing table access
We regularly load data into hundreds of tables in a reporting database.
I know that many of the tables were needed years ago for short-term
projects, and are no longer used. I'm trying to find a way to identify
which tables are never accessed by users, so that we can stop those
loads and drop the tables. For this purpose, if nobody SELECTs from a
table in 30 days, I can consider that table dead. And, if anyone
SELECTs from the table even once, I need to keep it around.
This is a high-usage database, so performance is key. I'd rather not
run a Profiler session for 30 days straight, because of the potential
impact on performance. Ideally, I'd like to find something like a
one-time trigger: a table is accessed, a trigger fires and adds that
table to a "Keep Me" list somewhere, then the trigger is disabled. I
don't think SELECT triggers exist in SQL2000, though.
Anyone have creative ideas on how to generate a list of used or unused
tables? Or is there a way I could do this with Profiler without
creating too much overhead?
Cheers!Hi stavros
"stavros" wrote:
> We regularly load data into hundreds of tables in a reporting database.
> I know that many of the tables were needed years ago for short-term
> projects, and are no longer used. I'm trying to find a way to identify
> which tables are never accessed by users, so that we can stop those
> loads and drop the tables. For this purpose, if nobody SELECTs from a
> table in 30 days, I can consider that table dead. And, if anyone
> SELECTs from the table even once, I need to keep it around.
> This is a high-usage database, so performance is key. I'd rather not
> run a Profiler session for 30 days straight, because of the potential
> impact on performance. Ideally, I'd like to find something like a
> one-time trigger: a table is accessed, a trigger fires and adds that
> table to a "Keep Me" list somewhere, then the trigger is disabled. I
> don't think SELECT triggers exist in SQL2000, though.
AFAIK profiler is the only tool that is likely to do this. You could run
multiple profiles that covers the whole period and process each profile
offline rather than having one single set of outputs to process at the end of
the period.
> Anyone have creative ideas on how to generate a list of used or unused
> tables? Or is there a way I could do this with Profiler without
> creating too much overhead?
>
This is where source code control is very advantages, just doing textual
searches for your tables will eliminate alot of them. Alternatively if you
use stored procedures, then you could script the stored procedures on their
own and do a textual search. If want to know which procedures are called
without using profiler you could add code that audits the procedure being
run, although this will be an overhead whilst you are collecting the
information and a potential bottleneck. it will also mean a code change.
> Cheers!
>
John
I know that many of the tables were needed years ago for short-term
projects, and are no longer used. I'm trying to find a way to identify
which tables are never accessed by users, so that we can stop those
loads and drop the tables. For this purpose, if nobody SELECTs from a
table in 30 days, I can consider that table dead. And, if anyone
SELECTs from the table even once, I need to keep it around.
This is a high-usage database, so performance is key. I'd rather not
run a Profiler session for 30 days straight, because of the potential
impact on performance. Ideally, I'd like to find something like a
one-time trigger: a table is accessed, a trigger fires and adds that
table to a "Keep Me" list somewhere, then the trigger is disabled. I
don't think SELECT triggers exist in SQL2000, though.
Anyone have creative ideas on how to generate a list of used or unused
tables? Or is there a way I could do this with Profiler without
creating too much overhead?
Cheers!Hi stavros
"stavros" wrote:
> We regularly load data into hundreds of tables in a reporting database.
> I know that many of the tables were needed years ago for short-term
> projects, and are no longer used. I'm trying to find a way to identify
> which tables are never accessed by users, so that we can stop those
> loads and drop the tables. For this purpose, if nobody SELECTs from a
> table in 30 days, I can consider that table dead. And, if anyone
> SELECTs from the table even once, I need to keep it around.
> This is a high-usage database, so performance is key. I'd rather not
> run a Profiler session for 30 days straight, because of the potential
> impact on performance. Ideally, I'd like to find something like a
> one-time trigger: a table is accessed, a trigger fires and adds that
> table to a "Keep Me" list somewhere, then the trigger is disabled. I
> don't think SELECT triggers exist in SQL2000, though.
AFAIK profiler is the only tool that is likely to do this. You could run
multiple profiles that covers the whole period and process each profile
offline rather than having one single set of outputs to process at the end of
the period.
> Anyone have creative ideas on how to generate a list of used or unused
> tables? Or is there a way I could do this with Profiler without
> creating too much overhead?
>
This is where source code control is very advantages, just doing textual
searches for your tables will eliminate alot of them. Alternatively if you
use stored procedures, then you could script the stored procedures on their
own and do a textual search. If want to know which procedures are called
without using profiler you could add code that audits the procedure being
run, although this will be an overhead whilst you are collecting the
information and a potential bottleneck. it will also mean a code change.
> Cheers!
>
John
Auditing Status of a Trigger in SQL Server 2005
I need to know when a DML trigger changes state (from enabled to disabled, and disabled to enabled).
I **assume** this info is not in any of the standard SQL Server 2005 loags and find no entry relating to triggers being enabled/disabled.
Therefore I also *assume* the best way to do this is create a DDL Trigger and populate a table when a trigger status is changed.
Am I on the right track?
Has anyone done this? Code samples/links to solution?
Is there a simplier way <i'm all about simple>?
Thanks in advance.
Thats exactly the right direction, if you need a quikc sample for using DDL events, take a look at the DDL event definition in the BOL and perhpas at the slightly different samples on my website: http://sqlserver2005.de/articles/2/HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)