SQLTeam.com | Weblogs | Forums

Using SQL Server Profiler to find where an SQL statement is called from

tsql
sql2008r2

#1

Hi
I'm trying to use SQL Server Profiler to determine where some SQL statments are are called from. The possible places its called from is either a Client file (maybe a dll or exe), a Stored Procedure or a Server file (maybe a dll or exe file). If I can determine which exact place it comes from, then I might be able to fix more customer faults hopefully.

I've generated an sql trace where I can see useful columns such as ObjectID, EventClass, ClientProcessID, SPID, ObjectType and TextData etc but not sure which would be most useful to help me debug further.

If I can discover that a fault stems from a Strored Procedure then I might be able write a quick fix for the customer. If its a specific dll at fault then at least I will be able refer this information onto a developer as only they can access this code in my organisation.

Please can anyone advise.

Kind Regards
James
p.s I'm using SQL Server Profiler 2008r2 to use against an SQL 2005 server but also support SQL 2000 and 2008 also.


#2

Add HostName, ApplicationName and LoginName to the trace. I prefer to use ObjectName instead of ObjectId as it saves me a step.

ApplicationName is useful if the app specified the value in the connection string, which not all do. But you might get lucky.

Add a filter to your trace to limit the resources. Trace to a file.

I'd suggest Extended Events, but then I saw you are using SQL 2005.