SQL Server Performance Forum – Threads Archive
Child Application Name in Profiler
Hi:I have a problem to trace a sql statement file by which dll in my application. The environment is like this:
Let say I have 3 dll==>A.DLL, B.DLL and C.DLL
A.dll call B.dll and B.dll call C.dll
When B.dll is being call, it may issue a sql statement by itself and when c.dll being call, it will issue another sql as well.
But the problme i face is in the profiler, when I try to capture the sql statement using the Application Name column, the application name always is the parent object, that is A.dll even though the profiler capable to capture the sql statement issue by otehr dll. So, i wonder is there a way I can capture correctly which target issue the sql statement in profiler? If profiler cannot do it, is there any other free tool that offer this kind of functionality? Thanks
Chan
At home, without documentation, I think there is no way.
If all application are compiled under one name, this is the name you capture with profiler.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
The only way is to change the dll so that each command has a comment prepended with the name of the dll i.e. instead of running
exec myproc you’d submit /* B.dll */
exec myproc this would then appear in profiler in the SQL Statement Cheers
Twan
All you have to do is to modify the connection string to include the Application Name like this. "PROVIDER=SQLOLEDB;Server=YourServer;Database=YourDB;Integrated Security=SSPI;Application Name=YourDLL;" Every DLL or child application should have the correponding ‘Application Name’ parameter in its connection string. If your are using a globally declared connection string, all you have to do is append the application name to it before using it to open a connection, thus:
For ADO/VB
Dim cn as ADODB.Connection
Set cn=New ADODB.Connection
cn.ConnectionString = strConnection & ";Application Name=" & myDLL_Name
cn.open For ADO.NET/VB.NET Dim cn as SQLConnection=New SQLConnection(strConnection & ";Application Name=" & myDLL_name)
cn.open Nathan H.O.
Moderator
SQL-Server-Performance.com
NOTE that this assumes that the DLLs are not passing connection object references to each other, and that you don’t mind forgoing connection pooling for those connections Cheers
Twan
The solution might miss out on all the stuff mentioned above but it ensures that the Profiler will pick out the application name form the connection and display it in the ‘application name’ column of the trace output. Using comments within the commands/stored procs is fine, but the ‘Application Name’ will still be recorded as that of the primary process (executable or DLL) and not of the individual child processes. To pick out the comments from the trace might require a little more parsing than necessary. There is always a price we have to pay for something we want.
Nathan H.O.
Moderator
SQL-Server-Performance.com
totally agree Nathan. It’s great to have this forum with so many smart people contributing Cheers
Twan
]]>