Monitoring sql statement ran against a table

I need to know if there is a way to either record all queries ran again
a particular table or to fire off a trigger if a particualr row is
selected from that table in sql server 2005. The main issue I am
having is "select" statements. The reason I am attempting to do this is
its full fill part of some requirements for a project. I was hoping to
do this with sql server and not a third party solution if possible.
Thanks for any help,
Marty
--[vbcol=seagreen]
A free community forum for resolving all of your high tech issues.maznblue@.gmail.com wrote:

> I need to know if there is a way to either record all queries ran again
> a particular table or to fire off a trigger if a particualr row is
> selected from that table in sql server 2005. The main issue I am
> having is "select" statements. The reason I am attempting to do this is
> its full fill part of some requirements for a project. I was hoping to
> do this with sql server and not a third party solution if possible.
> Thanks for any help,
> Marty
> --
> A free community forum for resolving all of your high tech issues.
Use profiler
You can filter data as per your requirement.
use sp:stmtcompleted , sp:completed events and objecttype , objectname
column, which will show you details about triggers when event will be
triggered
Regards
Amish Shah|||Marty,
Try using SQL Profiler and put a filter on TextData column like
'SELECT%XXXX%' where XXXXis the table which you are looking for..
Jayesh
<maznblue@.gmail.com> wrote in message
news:1149817748.504429.74260@.i40g2000cwc.googlegroups.com...
>I need to know if there is a way to either record all queries ran again
> a particular table or to fire off a trigger if a particualr row is
> selected from that table in sql server 2005. The main issue I am
> having is "select" statements. The reason I am attempting to do this is
> its full fill part of some requirements for a project. I was hoping to
> do this with sql server and not a third party solution if possible.
> Thanks for any help,
> Marty
> --
> A free community forum for resolving all of your high tech issues.
>|||Jayesh -
Thanks for the info, it worked like a champ.
Marty
Jayesh Antony Jose wrote:[vbcol=seagreen]
> Marty,
> Try using SQL Profiler and put a filter on TextData column like
> 'SELECT%XXXX%' where XXXXis the table which you are looking for..
> Jayesh
>
> <maznblue@.gmail.com> wrote in message
> news:1149817748.504429.74260@.i40g2000cwc.googlegroups.com...

Monitoring sql statement ran against a table

I need to know if there is a way to either record all queries ran again
a particular table or to fire off a trigger if a particualr row is
selected from that table in sql server 2005. The main issue I am
having is "select" statements. The reason I am attempting to do this is
its full fill part of some requirements for a project. I was hoping to
do this with sql server and not a third party solution if possible.
Thanks for any help,
Marty
--
>> www.techmentors.net <<<<<
A free community forum for resolving all of your high tech issues.maznblue@.gmail.com wrote:
> I need to know if there is a way to either record all queries ran again
> a particular table or to fire off a trigger if a particualr row is
> selected from that table in sql server 2005. The main issue I am
> having is "select" statements. The reason I am attempting to do this is
> its full fill part of some requirements for a project. I was hoping to
> do this with sql server and not a third party solution if possible.
> Thanks for any help,
> Marty
> --
> >> www.techmentors.net <<<<<
> A free community forum for resolving all of your high tech issues.
Use profiler
You can filter data as per your requirement.
use sp:stmtcompleted , sp:completed events and objecttype , objectname
column, which will show you details about triggers when event will be
triggered
Regards
Amish Shah|||Marty,
Try using SQL Profiler and put a filter on TextData column like
'SELECT%XXXX%' where XXXXis the table which you are looking for..
Jayesh
<maznblue@.gmail.com> wrote in message
news:1149817748.504429.74260@.i40g2000cwc.googlegroups.com...
>I need to know if there is a way to either record all queries ran again
> a particular table or to fire off a trigger if a particualr row is
> selected from that table in sql server 2005. The main issue I am
> having is "select" statements. The reason I am attempting to do this is
> its full fill part of some requirements for a project. I was hoping to
> do this with sql server and not a third party solution if possible.
> Thanks for any help,
> Marty
> --
>> www.techmentors.net <<<<<
> A free community forum for resolving all of your high tech issues.
>|||Jayesh -
Thanks for the info, it worked like a champ.
Marty
Jayesh Antony Jose wrote:
> Marty,
> Try using SQL Profiler and put a filter on TextData column like
> 'SELECT%XXXX%' where XXXXis the table which you are looking for..
> Jayesh
>
> <maznblue@.gmail.com> wrote in message
> news:1149817748.504429.74260@.i40g2000cwc.googlegroups.com...
> >I need to know if there is a way to either record all queries ran again
> > a particular table or to fire off a trigger if a particualr row is
> > selected from that table in sql server 2005. The main issue I am
> > having is "select" statements. The reason I am attempting to do this is
> > its full fill part of some requirements for a project. I was hoping to
> > do this with sql server and not a third party solution if possible.
> >
> > Thanks for any help,
> >
> > Marty
> >
> > --
> >> www.techmentors.net <<<<<
> > A free community forum for resolving all of your high tech issues.
> >

Monitoring of Replication using dev tools

What are the methods by which I can monitor replication/data row changes
programmatically?
I have looked at the Mergexlib but perhaps I've missed something but that
*appears* to be for setting up and then monitoring (which is "ok" except how
do I monitor previously configured subscribers? I cant see how I can set my
mergeobj to an existing subscription as the DMO/MergeLib appear to be very
different...). Will RMO in SQL2005 solve this and if so how?
Is there something I can do with the notifications services when rows are
inserted/updated/deleted?
I'm looking at SQL2000 (and will be moving to SQL2005) and using the .net
environment and looking for an event based solution - I am not interested in
polling tables and running arcane sp's.
tiaYou could modify the merge replication triggers to write to an audit table,
or simple use new triggers which will write to an audit table.
Another option is to query msmerge_contents and msmerge_tombstone.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nigel" <nige66_spam_free@.hotmail.com> wrote in message
news:ecYkuTrYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> What are the methods by which I can monitor replication/data row changes
> programmatically?
> I have looked at the Mergexlib but perhaps I've missed something but that
> *appears* to be for setting up and then monitoring (which is "ok" except
> how do I monitor previously configured subscribers? I cant see how I can
> set my mergeobj to an existing subscription as the DMO/MergeLib appear to
> be very different...). Will RMO in SQL2005 solve this and if so how?
> Is there something I can do with the notifications services when rows are
> inserted/updated/deleted?
> I'm looking at SQL2000 (and will be moving to SQL2005) and using the .net
> environment and looking for an event based solution - I am not interested
> in polling tables and running arcane sp's.
> tia
>|||Thanks but I do not see how that meets the requirements as I *still*
have to poll tables (although they are now tables that I create rather than
using system tables in the case of msmerge_contents which is a no no).
I did look at triggers but the problem is *how* to get the trigger to fire
an event on an application outside of SQL. I know the CLR support in SQL2005
lets me use the SQLpipe to send data back upto the server, but how about
raising events on other applications'
(Note that I'm *not* interested in yuckky solutions with triggers writing
marker
files/event logs with .Net doing file or event log watching).
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6KR%23xRZFHA.1040@.TK2MSFTNGP10.phx.gbl...
> You could modify the merge replication triggers to write to an audit
> table, or simple use new triggers which will write to an audit table.
> Another option is to query msmerge_contents and msmerge_tombstone.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nigel" <nige66_spam_free@.hotmail.com> wrote in message
> news:ecYkuTrYFHA.3132@.TK2MSFTNGP09.phx.gbl...
>|||Nigel -
SQLNS provides a couple of functionalities that may do what you're
sing.
First the built-in SQL Server Event Provider periodically checks a
table to see if there have been any changes to the rows. (Yes, it's
technically polling but you don't have to worry with the interval,
etc, SQLNS handles it for you.) Using T-SQL you as the developer
determine how those changes are to be recognized. The second way
involves using some SQLNS provided procedures in a trigger for the
data table.
Either way, events are raised in the SQLNS application and subscribers
can be notified via the built-in SMTP protocol or a custom protocol
that you develop (i.e. a protocol that would communicate some
application).
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 31 May 2005 16:42:34 +0100, "Nigel"
<nige66_spam_free@.hotmail.com> wrote:

>Thanks but I do not see how that meets the requirements as I *still*
>have to poll tables (although they are now tables that I create rather than
>using system tables in the case of msmerge_contents which is a no no).
>I did look at triggers but the problem is *how* to get the trigger to fire
>an event on an application outside of SQL. I know the CLR support in SQL200
5
>lets me use the SQLpipe to send data back upto the server, but how about
>raising events on other applications'
>(Note that I'm *not* interested in yuckky solutions with triggers writing
>marker
>files/event logs with .Net doing file or event log watching).
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:u6KR%23xRZFHA.1040@.TK2MSFTNGP10.phx.gbl...
>

Monitoring of Replication using dev tools

What are the methods by which I can monitor replication/data row changes
programmatically?
I have looked at the Mergexlib but perhaps I've missed something but that
*appears* to be for setting up and then monitoring (which is "ok" except how
do I monitor previously configured subscribers? I cant see how I can set my
mergeobj to an existing subscription as the DMO/MergeLib appear to be very
different...). Will RMO in SQL2005 solve this and if so how?
Is there something I can do with the notifications services when rows are
inserted/updated/deleted?
I'm looking at SQL2000 (and will be moving to SQL2005) and using the .net
environment and looking for an event based solution - I am not interested in
polling tables and running arcane sp's.
tia
You could modify the merge replication triggers to write to an audit table,
or simple use new triggers which will write to an audit table.
Another option is to query msmerge_contents and msmerge_tombstone.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nigel" <nige66_spam_free@.hotmail.com> wrote in message
news:ecYkuTrYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> What are the methods by which I can monitor replication/data row changes
> programmatically?
> I have looked at the Mergexlib but perhaps I've missed something but that
> *appears* to be for setting up and then monitoring (which is "ok" except
> how do I monitor previously configured subscribers? I cant see how I can
> set my mergeobj to an existing subscription as the DMO/MergeLib appear to
> be very different...). Will RMO in SQL2005 solve this and if so how?
> Is there something I can do with the notifications services when rows are
> inserted/updated/deleted?
> I'm looking at SQL2000 (and will be moving to SQL2005) and using the .net
> environment and looking for an event based solution - I am not interested
> in polling tables and running arcane sp's.
> tia
>
|||Thanks but I do not see how that meets the requirements as I *still*
have to poll tables (although they are now tables that I create rather than
using system tables in the case of msmerge_contents which is a no no).
I did look at triggers but the problem is *how* to get the trigger to fire
an event on an application outside of SQL. I know the CLR support in SQL2005
lets me use the SQLpipe to send data back upto the server, but how about
raising events on other applications?
(Note that I'm *not* interested in yuckky solutions with triggers writing
marker
files/event logs with .Net doing file or event log watching).
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6KR%23xRZFHA.1040@.TK2MSFTNGP10.phx.gbl...
> You could modify the merge replication triggers to write to an audit
> table, or simple use new triggers which will write to an audit table.
> Another option is to query msmerge_contents and msmerge_tombstone.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nigel" <nige66_spam_free@.hotmail.com> wrote in message
> news:ecYkuTrYFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
|||Nigel -
SQLNS provides a couple of functionalities that may do what you're
seeking.
First the built-in SQL Server Event Provider periodically checks a
table to see if there have been any changes to the rows. (Yes, it's
technically polling but you don't have to worry with the interval,
etc, SQLNS handles it for you.) Using T-SQL you as the developer
determine how those changes are to be recognized. The second way
involves using some SQLNS provided procedures in a trigger for the
data table.
Either way, events are raised in the SQLNS application and subscribers
can be notified via the built-in SMTP protocol or a custom protocol
that you develop (i.e. a protocol that would communicate some
application).
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg...l/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 31 May 2005 16:42:34 +0100, "Nigel"
<nige66_spam_free@.hotmail.com> wrote:

>Thanks but I do not see how that meets the requirements as I *still*
>have to poll tables (although they are now tables that I create rather than
>using system tables in the case of msmerge_contents which is a no no).
>I did look at triggers but the problem is *how* to get the trigger to fire
>an event on an application outside of SQL. I know the CLR support in SQL2005
>lets me use the SQLpipe to send data back upto the server, but how about
>raising events on other applications?
>(Note that I'm *not* interested in yuckky solutions with triggers writing
>marker
>files/event logs with .Net doing file or event log watching).
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:u6KR%23xRZFHA.1040@.TK2MSFTNGP10.phx.gbl...
>
|||Thanks Joe, I'm looking at your proposed method at the moment.
(In further digging I have come across custom resolvers using the
replrec.dll but seem to have hit a wall on that because theres so little
information about it, especially to develop for it in a .Net way. If anyone
has any experience good or bad......)
"Joe Webb" <joew@.webbtechsolutions.com> wrote in message
news:7suaa15nj9qa8bpgu4ooeok9riei1530r7@.4ax.com...
> Nigel -
> SQLNS provides a couple of functionalities that may do what you're
> seeking.
> First the built-in SQL Server Event Provider periodically checks a
> table to see if there have been any changes to the rows. (Yes, it's
> technically polling but you don't have to worry with the interval,
> etc, SQLNS handles it for you.) Using T-SQL you as the developer
> determine how those changes are to be recognized. The second way
> involves using some SQLNS provided procedures in a trigger for the
> data table.
> Either way, events are raised in the SQLNS application and subscribers
> can be notified via the built-in SMTP protocol or a custom protocol
> that you develop (i.e. a protocol that would communicate some
> application).
> HTH...
> --
> Joe Webb
> SQL Server MVP
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/tg...l/-/0972688811
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
> On Tue, 31 May 2005 16:42:34 +0100, "Nigel"
> <nige66_spam_free@.hotmail.com> wrote:
>

Monitor Table Activity

Dear All,
I want to Monitor a table or multiple table activities with the follwing
results.
1. Previous Value
2. New Value
3. Table Row Changed
Thanks in advanceHi,
You have write your own triggers for Insert and Update actions.
Thanks
Hari
MCDBA
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:OB4LyN#FEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I want to Monitor a table or multiple table activities with the follwing
> results.
> 1. Previous Value
> 2. New Value
> 3. Table Row Changed
> Thanks in advance
>|||THere is also a tool that Lumigent makes which analyzes the T-Log, etc which
( I beleive) can do what you wish... Integra ( www.lumigent.com)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:OB4LyN%23FEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I want to Monitor a table or multiple table activities with the follwing
> results.
> 1. Previous Value
> 2. New Value
> 3. Table Row Changed
> Thanks in advance
>|||I have just been playing around with the demo if this software and it seems
really good. Certainly look slike it will save me the many hours of
building an audit trail into my application!
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OmW4dk%23FEHA.576@.TK2MSFTNGP11.phx.gbl...
> THere is also a tool that Lumigent makes which analyzes the T-Log, etc
which
> ( I beleive) can do what you wish... Integra ( www.lumigent.com)
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
> news:OB4LyN%23FEHA.3188@.TK2MSFTNGP10.phx.gbl...
>

Monitor Table Activity

Dear All,
I want to Monitor a table or multiple table activities with the follwing
results.
1. Previous Value
2. New Value
3. Table Row Changed
Thanks in advance
Hi,
You have write your own triggers for Insert and Update actions.
Thanks
Hari
MCDBA
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:OB4LyN#FEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I want to Monitor a table or multiple table activities with the follwing
> results.
> 1. Previous Value
> 2. New Value
> 3. Table Row Changed
> Thanks in advance
>
|||THere is also a tool that Lumigent makes which analyzes the T-Log, etc which
( I beleive) can do what you wish... Integra ( www.lumigent.com)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
news:OB4LyN%23FEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I want to Monitor a table or multiple table activities with the follwing
> results.
> 1. Previous Value
> 2. New Value
> 3. Table Row Changed
> Thanks in advance
>
|||I have just been playing around with the demo if this software and it seems
really good. Certainly look slike it will save me the many hours of
building an audit trail into my application!
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OmW4dk%23FEHA.576@.TK2MSFTNGP11.phx.gbl...
> THere is also a tool that Lumigent makes which analyzes the T-Log, etc
which
> ( I beleive) can do what you wish... Integra ( www.lumigent.com)
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Syed Zulfiqar" <zulfiqar_syed@.hotmail.com> wrote in message
> news:OB4LyN%23FEHA.3188@.TK2MSFTNGP10.phx.gbl...
>