如何使用 SQL Server Profiler 查看 CLR 触发器中运行的 TSQL？
如何使用 SQL Server Profiler 查看 CLR 触发器中运行的 TSQL？
我在 MS SQL Server 数据库中有一个 CLR 触发器，它检查表上的插入、更新、删除，然后有条件地插入到另一个表。是否有设置可以获取从 CLR 触发器内进行的 T-SQL 插入以显示 SQL Server Profiler 跟踪结果？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在事件选择选项卡中，单击“显示所有事件”后，会出现一个 CLR 部分。其中只有一个事件“Assembly Load”。当请求加载 CLR 程序集时，会发生此事件。来自程序集的任何 SQL 都应注册为标准 TSQL 事件，因此我将转到 TSQL 部分并选择每个“SQL:”事件。另请查看“错误和警告”部分。我假设程序集的设置有问题，或者在调用 SQL 之前发生了 .NET 异常。
如果您的 CLR 触发器只是对另一个表的条件插入，我不会使用程序集。如果频繁选择、更新或删除表，则尤其如此。程序集在服务器应用程序内存空间中运行，而不是在专用的 sql server 内存空间中运行，因此突然间您的 sql server 使用的应用程序内存比正常情况多。我已经用这种方式使我的 sql 服务器崩溃了。
它还引入了源代码控制、构建方面的更多复杂性，并且存在一些您应该了解的安全问题。总而言之，我认为使用 SQL Server CLR 应该是最后的努力。
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.
这取决于您如何执行代码。您使用的是
SqlCommand吗？如果是这样，您是否将CommandType设置为StoredProcedure或Text？如果您指定 CommandType 为
Text或一开始就没有指定 CommandType（因为Text是默认值），则语句是动态 SQL，您需要使用TSQL 组中的 SQL:StmtStarting 和 SQL:StmtCompleted 事件。您的触发器可能就是这种情况。如果您指定 CommandType 为
StoredProcedure，请尝试存储过程中的 SP:Starting 和 SP:Completed 事件强>组。It depends on how you are executing the code. Are you using a
SqlCommand? If so, are you setting theCommandTypeto beStoredProcedureorText?If you are specifying a CommandType of
Textor not specifying a CommandType in the first place (sinceTextis 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.