可能得到SQL Server当前正在执行的sproc的行号？ [英] Possible to get the line number of the currently executing sproc in SQL Server?

问题描述

几年前，我在Sybase / Delphi环境中工作，使用BDE连接到DB服务器。我们有一个Delphi应用程序，给定当前正在执行的存储过程的名称，可以告诉您当前正在执行该sproc的哪一行。这对于调试似乎挂起的sprocs非常有用。

我想在SQL Server中使用此功能，但我不记得是否是Sybase或BDE功能。 SQL Server中是否提供此功能，如果是这样，我需要使用什么命令来查看这些信息？

解决方案

您可以使用

  SELECT 
 CASE 
 WHEN statement_end_offset = -1 
 THEN文本
 ELSE SUBSTRING（text，statement_start_offset / 2，（statement_end_offset- statement_start_offset）/ 2）
 END，
 statement_end_offset，statement_start_offset 
 FROM sys.dm_exec_requests 
 CROSS APPLY sys .dm_exec_sql_text（sql_handle）
 WHERE session_id = 53  - 无论如何！ 
  

statement_start_offset 和 statement_end_offset 是映射到当前正在执行的语句而不是行号的字符偏移。

单行可以包含多个可执行语句，单个语句可以跨多行。

A few years back I worked in a Sybase/Delphi environment, using the BDE to connect to the DB server. We had a little Delphi app that, given the name of a currently executing stored procedure, could tell you what line of that sproc was currently being executed. This was exceptionally useful for debugging sprocs that seemed to be hanging.

I'd like to use this functionality in SQL Server, but I can't remember whether it was a Sybase or a BDE feature. Is this functionality available in SQL Server and if so, what command(s) do I need to use to view this information?

解决方案

You can use something like

SELECT
    CASE
        WHEN statement_end_offset = -1
        THEN text
        ELSE SUBSTRING(text,statement_start_offset/2,(statement_end_offset- statement_start_offset)/2)
    END,
    statement_end_offset, statement_start_offset
FROM    sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE session_id = 53 --Or whatever!

The statement_start_offset and statement_end_offset are character offsets that map to the statement currently being executed rather than a line number.

A single line can contain multiple executable statements and a single statement can span multiple lines.

这篇关于可能得到SQL Server当前正在执行的sproc的行号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！