Friday, February 24, 2012

Logging use of stored procedures

Hello,
I would like to know if it is possible to log the use of stored procedures
in SQL 2000 (and later in SQL 2005)
We want to log every time if a user makes use of a stored procedure without
adding a entry in each Stored procedure.
Is this possible ? if yes, how ?
thanks,
Frank WijtenAfraid not - You would need to collect the data via a profiling session and
then take a look at the results to see your procedure usage.
"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>|||You can use SQL Trace to implement you custom tracing.
Look at Books on line under the path:
SQLServer Database Engine -> Administering the database Engine -> Monitoring
and Tuning for Performance -> Monitoring Events -> Introducing SQL Trace.
You should find it really interesting and may be a way to satisfy your
requirements
Gilberto
"Frank Wijten" wrote:
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure without
> adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
>|||"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
As others have pointed out, this IS possible using SQL trace or Profiler.
HOWEVER, it can potentially be very costly in terms of performance.
Just be aware.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

No comments:

Post a Comment