Counting number of selects made

[A Seel]

COUNTING NUMBER OF SELECTS MADE


table mytable {
id, data, hits
}

users view data from the table:

SELECT data FROM mytable WHERE id=1 --for example
SELECT data FROM mytable WHERE id=20 --for example
....
How do increment the hits column without replacing the above with the below?

update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=1
update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=20
....
I believe triggers can't be used as they only trigger on update/delete events.

I'm using sql server 2000 (latest patches) with asp

Thanks
Alex

[Anith Sen]

There is no reliable way to do this. One option is to use the profiler to
track the SELECT statements and dump them into a file. This can be exported
in a SQL Server table later on.

--
- Anith
( Please reply to newsgroups only )

[Jay Allard]

"Anith Sen" <an***@bizdatasolutions.com> wrote in message news:<M4*********************@bgtnsc05-news.ops.worldnet.att.net>...

There is no reliable way to do this. One option is to use the profiler to
track the SELECT statements and dump them into a file. This can be exported
in a SQL Server table later on.

I use stored procedures for everything. This is one reason why. You
can add auditting information whenever you like.

In a related story, I also have an USERID parameter on every stored
procedure for future use. (Then you can log who queried what).

[Erland Sommarskog]

A Seel (al*********@hotmail.com) writes:

COUNTING NUMBER OF SELECTS MADE

table mytable {
id, data, hits
}
users view data from the table:
SELECT data FROM mytable WHERE id=1 --for example
SELECT data FROM mytable WHERE id=20 --for example
...
How do increment the hits column without replacing the above with the
below?

As Anith said, there is no supported way to this, other than to run a
Profiler trace.

Lumigent has a tool called Entegra, which they say will include a module
that audits who views which data. Their web site says that this module
is planned for release Q2, 2003, so one would expect it to appear soon.
See www.lumigent.com for more information.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp