How can I user SQL Server Profiler to view the TSQL ran in a CLR trigger?
I have a CLR trigger in the a MS SQL Server DB, that checks the Inserts, Updates, Deletes on a table and then conditionally does inserts to another table. Is there a setting to get those T-SQL inserts that are made from within the CLR Trigger to show the SQL Server Profiler trace results?
It depends on how you are executing the code. Are you using a SqlCommand? If so, are you setting the CommandType to be StoredProcedure or Text?
If you are specifying a CommandType of Text or not specifying a CommandType in the first place (since Text is the default), then the statements are dynamic SQL and you need to use the SQL:StmtStarting and SQL:StmtCompleted events in the TSQL group. This is probably the case for your Trigger.
If you are specifying a CommandType of StoredProcedure, then try the SP:Starting and SP:Completed events in the Stored Procedures group.
In the event selection tab, after you click "Show all events" there is a CLR section. There is only one event in it "Assembly Load". This event is happens when there is a request to load the CLR assembly. Any SQL that comes from the assembly should register as a standard TSQL event, so I would go to the TSQL section and pick every "SQL:" event. Also check out the "Errors and Warnings" section. I am assuming there is something wrong in the setup of the assembly or a .NET exception is happening before the SQL is called.
If your CLR trigger is just a conditional insert into another table I would not use an assembly. This is especially true if the table is selected, updated, or deleted from frequently. Assemblies run in the servers application memory space, not in the dedicated sql server memory space so all of the sudden you have a sql server that is using more application memory than normal. I've crashed my fair share of sql servers this way.
It also introduces more complexity in source control, builds, and has some security issues that you should understand. All in all, I'd say using the SQL Server CLR should be a last ditch effort.
