如何跟踪T-SQL函数调用 [英] How to trace T-SQL function calls

问题描述

我正在尝试调试一个非常复杂的公式评估程序，用T-SQL UDF编写（不要求）递归（但间接通过中间函数）调用自己，blah，blah。

当然，我们有一个错误。

现在，使用PRINT语句通过实现InfoMessage事件的处理程序从ADO.NET读取），我可以模拟存储过程的跟踪。

对于UDF，执行相同操作会导致编译时消息：

 在函数中的PRINT中无效使用副作用或时间相关的运算符。 
  

我收到消息（PRINT有些东西像重置 @@ ROWCOUNT 这是UDF中的一个no-no，但是如何跟踪通过调用？我想要打印出这个跟踪，所以我可以学习它，而不用分心，通过调试器中的调用...

编辑：我尝试使用SQL Profiler（这是我第一次），但我可以'找出要跟踪的内容：虽然我可以获取跟踪来输出发送到数据库的查询，但是它们是不透明的，因为我无法深入到所谓的表达式UDF：我可以跟踪实际的存储过程被调用，但是这个程序调用的UDF没有列出，我错过了什么？我猜不是...

编辑＃2： Allthough（自动）接受的答案确实跟踪函数调用 - 非常有帮助，谢谢 - 它无助于找出传递的函数到functio n。这当然在调试递归函数中是至关重要的。我会发布，如果我发现任何解决方案...

解决方案

为什么不使用SQL Profiler添加语句级事件？ / p>

编辑：为存储过程添加事件：SP：Stmt启动或SP：Stmt已完成
如果需要，使用变量进行调试，即set @ debug ='我在这里'; UDF虽然没有技术存储的过程，但会跟踪语句级事件。

I'm trying to debug a rather complicated formula evaluator written in T-SQL UDFs (don't ask) that recursively (but indirectly through an intermediate function) calls itself, blah, blah.

And, of course, we have a bug.

Now, using PRINT statements (that can then be read from ADO.NET by implementing a handler for the InfoMessage event), I can simulate a trace for stored procedures.

Doing the same for UDF results in a compile time message:

Invalid use of side-effecting or time-dependent operator in 'PRINT' within a function.

I get the message (PRINT does some stuff like resetting @@ROWCOUNT which definitly is a no-no in UDFs, but how can I trace through the calls? I want to have this trace printed out, so I can study it without getting distracted by stepping through the calls in the debugger...

EDIT: I have tried to use the SQL Profiler (this was a first time one for me), but I can't figure out what to trace for: Although I can get the trace to output the queries sent to the database, they are opaque in the sense that I can't drill down to the Expression-UDFs called: I can trace the actual Stored Procedure invoked, but the UDFs called by this procedure are not listed. Am I missing something? I guess not...

EDIT #2: Allthough the (auto-)accepted answer does trace the function calls - very helpful, thanks - it does not help in finding out what parameters were passed to the function. This, of course, is essential in debugging recursive functions. I will post if I find any sollution at all...

解决方案

Why not use SQL Profiler with statement level events added?

Edit: Add events for Stored Procedures : SP:Stmt Starting or SP:Stmt Completed Use variables to debug if needed, i.e. set @debug='i am here'; UDF's, while not technically stored procedures, will get traced with the statement level events.

这篇关于如何跟踪T-SQL函数调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！