Which frontends access my Stored Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Which frontends access my Stored Procedures

Author

Topic

NBU
Starting Member

1 Post

Posted - 2007-12-17 : 16:22:37

I am working on a corporate project. I have many stored procedures(>100) and wanted to know which applications( or Frontends) in the network use or consume my stored procedures. How can i do that in SQL 2005??

NBU

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 16:24:54

You can sort of view this information in SQL Profiler, but it requires that the application name be specified in the connection string in order for the trace data to provide the information that you want to see.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-17 : 16:26:49

run profiler and be sure to include the ApplicationName column. of course for this to work there must be Application Name set in the connection string.
you could also look at the HostName, NTUserName and LoginName columns and see what apps are running on each host computer.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-12-17 : 16:29:38

Another way is to alter each stored procedure to spit out the application name (via the APP_NAME() system function) to an audit table.


create procedure blah 
as
insert into audit table (procname, appname)
values ('blah', app_name())
--rest of procedure...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-17 : 16:32:55

for APP_NAME () to work the Application Name also has to be set in the connection string.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-12-18 : 11:47:16

Same goes for profiler. They both read the same thing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources