如何跟踪T-SQL函数调用

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.

我正在尝试调试一个用T-SQL UDF编写的相当复杂的公式求值程序(不要问)递归(但通过中间函数间接)调用自己,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.

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

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

对UDF执行相同操作会产生编译时消息:

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...

我得到了消息(PRINT做了一些像重置@@ ROWCOUNT这样的东西,在UDF中肯定是禁止的,但我如何追踪通话?我想打印出这条迹线,所以我可以研究它而不会分心通过逐步调试调试器中的调用...

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...

编辑:我曾尝试使用SQL Profiler(这是我第一次使用),但我无法弄清楚要追踪的内容:虽然我可以获取跟踪输出发送到数据库的查询,但它们是opaque在某种意义上说我无法向下钻取到被调用的Expression-UDF:我可以跟踪调用的实际存储过程,但是没有列出此过程调用的UDF。我错过了什么吗?我猜不会...

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...

编辑#2:尽管(自动)接受的答案确实跟踪了函数调用 - 非常有用,谢谢 - 它无助于找出传递给函数的参数。当然,这对于调试递归函数至关重要。如果我发现任何溶剂,我会发布...

9 个解决方案

#1

Why not use SQL Profiler with statement level events added?

为什么不添加SQL Profiler并添加语句级事件?

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.

编辑:为存储过程添加事件:SP:Stmt Starting或SP:Stmt Completed使用变量进行调试(如果需要),即设置@ debug ='我在这里'; UDF虽然不是技术上存储的过程,但会跟踪语句级事件。

#2

In the SQL profiler, you need: SP:Starting, SP:StmtStarting, SP:Completed, SQL:BatchStarting. Then, you get every entry, exit from the functions/stored procedures.

在SQL事件探查器中,您需要:SP:Starting,SP:StmtStarting,SP:Completed,SQL:BatchStarting。然后,您将获得每个条目,退出函数/存储过程。

alter FUNCTION [dbo].[ufn_mjf](@i numeric(10))
    RETURNS numeric(20) 
AS
BEGIN
declare @datapoint varchar(10)

    set @datapoint = 'hello world'

    return @i
END
go
drop table foo
go
create table dbo.foo ( foo_id numeric(10)) 
go
delete from foo
insert into foo ( foo_id ) values ( 1 )
insert into foo ( foo_id ) values ( 2 )

select foo_id, dbo.ufn_mjf(foo_id) from foo

with this, I get:

有了这个,我得到:

SQL:BatchStarting   alter FUNCTION [dbo].[ufn_mjf](@i numeric(10))
SQL:BatchStarting   drop table foo
SQL:BatchStarting   create table dbo.foo ( foo_id numeric(10)) 
SQL:BatchStarting   delete from foo
    insert into foo ( foo_id ) values ( 1 )
    insert into foo ( foo_id ) values ( 2 )
    select foo_id, dbo.ufn_mjf(foo_id) from foo
SP:Starting select foo_id, dbo.ufn_mjf(foo_id) from foo
SP:StmtStarting set @datapoint = 'hello world'
SP:StmtStarting return @i
SP:Completed    select foo_id, dbo.ufn_mjf(foo_id) from foo
SP:Starting select foo_id, dbo.ufn_mjf(foo_id) from foo
SP:StmtStarting set @datapoint = 'hello world'
SP:StmtStarting return @i
SP:Completed    select foo_id, dbo.ufn_mjf(foo_id) from foo

is that enough for you?

那对你来说够了吗?

#3

This looks like what you need but it's only available in team/pro versions of Visual Studio.

这看起来像你需要的,但它只适用于Visual Studio的团队/专业版。

#4

Use SQL Profiler, I recommend you go overboard on adding events the first time around which will let you get a feel for what you need. Without testing I would add the events for SP:StmtStarted (or Completed or both), SQL:StmtStarted (again Completed or Both).

使用SQL事件探查器,我建议您在第一次添加事件时过度使用,以便您了解所需内容。如果不进行测试,我会为SP添加事件:StmtStarted(或已完成或两者),SQL:StmtStarted(再次完成或两者)。

#5

I second the SQL Profiler suggestion. Take some time to set it up so that only the events you are interested in are logged to cut output size. You can output the trace to a file - I have frequently then loaded that file back into a table to enable analysis. (extremely handy for performance analysis, though no doubt someone will tell me that 2008 has this all built in somwehere...)

我是SQL Profiler建议的第二个。花一些时间进行设置,以便只记录您感兴趣的事件以减少输出大小。您可以将跟踪输出到文件 - 我经常将该文件加载回表中以启用分析。 (对于性能分析来说非常方便,但毫无疑问有人会告诉我,2008年所有这些都是建立在somwehere ...)

Sometimes you won't have permissions to run SQL Profiler as it does slow the server down - ask your DBA to grant you permission on your Dev server. They shouldn't have any problems with that.

有时您无权运行SQL事件探查器,因为它会降低服务器速度 - 请求您的DBA授予您Dev开发服务器的权限。他们不应该有任何问题。

#6

Well in the past I have had to take typical values that would be in the UDF and then run just the udf part in a separate query window as straight SQL not a udf using the typical values as variables set with a declare and a set statement. If it is run from a table instead of having only one value, I would set up a temp table or table variable with the input values and then run them through the sql in the UDF (but again as straight SQL not a UDF) through a cursor. By running straight SQL you could have print statements in it to see what is happening. I know this is a pain, but it works. (I go through a simliar process when creating/debugging triggers, setup #inserted and #deleted with my test values and then test the code I intend to put into the trigger, then global replace the # with nothing and add the create trigger code.)

在过去,我不得不采用UDF中的典型值,然后在单独的查询窗口中仅使用udf部分作为直接SQL而不是udf,使用典型值作为使用declare和set语句设置的变量。如果它是从一个表运行而不是只有一个值,我会设置一个带有输入值的临时表或表变量,然后通过UDF中的sql运行它们(但是再次作为直接SQL而不是UDF)通过光标。通过运行直接SQL,您可以在其中包含打印语句以查看发生的情况。我知道这是一种痛苦,但它确实有效。 (我在创建/调试触发器时会经历一个simliar进程,使用我的测试值设置#inserted和#deleted,然后测试我打算放入触发器的代码,然后全局替换#with no,并添加create trigger代码。 )

#7

Maybe you can use SQL CLR to do the tracing as described here How to log in T-SQL

也许您可以使用SQL CLR来执行此处所述的跟踪如何登录T-SQL

#8

Can you take your function, and make a second copy of it, but returning a table type with an additional column for your debug information.

你可以使用你的函数,并制作它的第二个副本,但返回一个表类型,其中包含一个附加列,用于调试信息。

For example, the mySum function below

例如,下面的mySum函数

CREATE FUNCTION mySum
(   
    @param1 int,
    @param2 int
)
RETURNS INT AS
BEGIN
    DECLARE @mySum int

    SET @mySum = @param1

    SET @mySum = @mySum + @param2

    RETURN @mySum

END
GO
SELECT dbo.mySum(1, 2)

Would turn into

会变成

CREATE FUNCTION mySumDebug
(   
    @param1 int,
    @param2 int
)
RETURNS @myTable TABLE
(
    [mySum] int,
    [debug] nvarchar(max)
)
AS
BEGIN
    DECLARE @debug nvarchar(max)

    SET @debug = 'Declare @mySum variable. '
    DECLARE @mySum int

    SET @debug = @debug + 'Set @mySum = @param1(' + CONVERT(nvarchar(50), @param1) + ') '
    SET @mySum = @param1


    SET @debug = @debug + 'Add @param2(' + CONVERT(nvarchar(50), @param2) + ') to @mySum(' + CONVERT(nvarchar(50), @mySum) + ') '
    SET @mySum = @mySum + @param2

    SET @debug = @debug + 'Return @mySum variable. '

    INSERT @myTable (mySum, debug) VALUES (@mySum, @debug)

    RETURN
END
GO
SELECT mySum, debug FROM dbo.mySumDebug(1, 2)

Not an ideal solution, but useful just to return some text to help track down a bug.

不是一个理想的解决方案,但仅用于返回一些文本以帮助追踪错误。

#9

I use SQL SPY which does what you are looking for and more.

我使用SQL SPY来完成你正在寻找的东西以及更多。

SQL SPY

SQL SPY Feature Documentation

SQL SPY功能文档

SQL SPY's Incoming SQL Sniffer shows the incoming SQL code of each connection (Includes DDL and DML statement tracking)

SQL SPY的Incoming SQL Sniffer显示每个连接的传入SQL代码(包括DDL和DML语句跟踪)

This feature is designed for MS SQL Server 2005\2008, but will work with MS SQL Server 2000 in limited scope. It has the ability to record and report on Incoming SQL. How to use the features: See

此功能是为MS SQL Server 2005 \ 2008设计的,但可以在有限范围内与MS SQL Server 2000一起使用。它能够记录和报告传入SQL。如何使用功能:见

Disclosure: I am part of the SQL SPY team.

披露:我是SQL SPY团队的一员。

#1