如何附加和调试正在运行的SQL Server存储过程？

I am investigating an odd error from a SQL Server 2005 stored procedure which I cannot reproduce by calling it directly from Management Studio.

我正在调查SQL Server 2005存储过程中的一个奇怪的错误,我无法通过直接从Management Studio调用它来重现它。

Therefore I'd like to be able to:

因此,我希望能够:

set a breakpoint in the stored procedure

在存储过程中设置断点

wait for the procedure to be called externally and the breakpoint hit

等待从外部调用该过程并且断点命中

see the values of the passed-in parameters

查看传入参数的值

step through the stored procedure

逐步执行存储过程

Is this possible, and if so how?

这是可能的,如果是这样的话怎么样?

5 个解决方案

#1

You could try the "server explorer" from visual studio but the sqlserver needs to be configured to allow debugging. Here is some info: http://www.4guysfromrolla.com/articles/051607-1.aspx. But I think that you first should try Profiler like Eppz say. :)

您可以从visual studio尝试“服务器资源管理器”,但需要配置sqlserver以允许调试。以下是一些信息:http://www.4guysfromrolla.com/articles/051607-1.aspx。但我认为你首先应该尝试像Eppz说的Profiler。 :)

#2

If you can't step through the code, here are two ways:

如果您无法单步执行代码,请执行以下两种方法:

#1 concatenate a string, and insert into a log file.

#1连接一个字符串,并插入一个日志文件。

Declare a variable like:

声明一个变量,如:

DECLARE @Loginfo varchar(7500)

append debug info into it as you progress through the code:

在您完成代码的过程中将调试信息附加到其中:

SET @LogInfo=ISNULL(@LogInfo)+'#01> @x='+COALESCE(CONVERT(varchar(10),@x),'NULL')
..
SET @LogInfo=ISNULL(@LogInfo)+'#02>'
..
SET @LogInfo=ISNULL(@LogInfo)+'#03> top loop'

at all exit points (after any rollbacks) add:

在所有出口点(在任何回滚之后)添加:

INSERT INTO YourLogTable VALUES (... ,@LogInfo )

插入YourLogTable值(...,@ LogInfo)

depending on the transaction usage and you error in particular, you may be able to just insert many times with no fear of rollback, so you will need change this to your situation.

根据事务使用情况而言,特别是您的错误,您可能只需插入多次而不用担心回滚,因此您需要根据自己的情况进行更改。

#2 write to a text file on the sq server

#2写入sq服务器上的文本文件

this may not be an option because it uses the very insecure xp_cmdshell stored procedure. However, if you can use that and if transactions from the calling app are causing a problem try creating this stored procedure:

这可能不是一个选项,因为它使用非常不安全的xp_cmdshell存储过程。但是,如果您可以使用它,并且如果来自调用应用程序的事务导致问题,请尝试创建此存储过程:

CREATE PROC log_message
     @Message         varchar(255)
    ,@FileName        varchar(100)
    ,@OverWrite       char(1) = 'N'
AS

/*
Log messages to server side text files from stored procedures/triggers/sql scripts

  Input parameters:
      Message   - message to put in the log file 
      FileName  - path and name of the file to log the message into
      OverWrite - 'Y'=overwrite entire file with current message
                  'N'=append current message onto end of file

  Return code:
      0 - everything was fine
      1 - there was an error


        NOTE: the command to log the message can not be longer than 255 characters,
              as a result the message and file name should be less than 245 chars combined


  Example: EXEC log_message 'Duplicates found','C:\logfile.txt', 'N'
      append the "Duplicates found" message onto the server's "C:\logfile.txt" file

*/

BEGIN
    SET NOCOUNT ON

    DECLARE @ExecuteString    VARCHAR(255)   --command string can only be 255 chars long
    DECLARE @ReturnValue           int

    --build command string
    SET @ExecuteString = RTRIM('echo ' + COALESCE(LTRIM(@Message),'-')
        + CASE WHEN (@OverWrite = 'Y') THEN ' > ' ELSE ' >> ' END + RTRIM(@FileName))

    --run command string
    EXEC @ReturnValue=master..xp_cmdshell @ExecuteString
    --IF @ReturnValue!=0
    --    PRINT 'command failed, return value='+CONVERT(varchar(40),@ReturnValue)

    RETURN @ReturnValue

    SET NOCOUNT OFF
END

sprinkle calls to this procedure these through your code write what you need into a file on the server

通过您的代码将调用此函数的内容写入服务器上的文件中

#3

Update: I am not sure if there is a way to attach to a running stored proc. You can use profiler to get a real time trace of the statements getting executed (SP:StmtStarting). Also check out Apex SQL Debug which seems to have more capabilities and is available as an Add-in to Management Studio.

更新:我不确定是否有办法附加到正在运行的存储过程。您可以使用分析器来获取执行语句的实时跟踪(SP:StmtStarting)。另请参阅Apex SQL Debug,它似乎具有更多功能,可作为Management Studio的插件使用。

If you have Visual Studio, it is easy to debug:

如果您有Visual Studio,则很容易调试:

Debugging Stored Procedures in Visual Studio 2005

在Visual Studio 2005中调试存储过程

More answers here: What’s your favored method for debugging MS SQL stored procedures?

这里有更多的答案:您最喜欢的调试MS SQL存储过程的方法是什么?

#4

Use SQL Profiler to view what is happening when the procedure gets called and what params are passed in.

使用SQL事件探查器查看调用过程时传递的内容以及传入的参数。

#5

I would use a combination of SQL Profiler and print statements inside of your SQL statement. Not sure of a way to step thru line by line but using profiler in combination with print and select statements (if using temp tables) to view their contents as the proc runs will quickly shed light on what's happening.

我会在SQL语句中使用SQL事件探查器和打印语句的组合。不确定是否逐行逐行,但使用分析器与print和select语句(如果使用临时表)一起查看其内容,因为proc运行将很快揭示正在发生的事情。

#1

#2