How can I connect and debug a SQL Server stored procedure?

Question

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

Therefore, I would like to be able to:

• set breakpoint in stored procedure
• wait for the procedure to be called from outside, and the breakpoint
• see parameter values ​​passed
• execute stored procedure

Is this possible, and if so, how?

Answer 1

" " , sqlserver . : http://www.4guysfromrolla.com/articles/051607-1.aspx. , Profiler, Eppz.:)

Answer 2

. , . , (SP: StmtStarting). Apex SQL Debug, , , Management Studio.

Visual Studio, :

Visual Studio 2005

: MS SQL?

Answer 3

, :

# 1 .

:

DECLARE @Loginfo varchar(7500)

:

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

( ) :

(..., @LogInfo)

, , , , , .

# 2 sq-

, 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 "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

, , , ,

Answer 4

SQL Profiler, , , .

Answer 5

I would use a combination of SQL Profiler and print statements inside your SQL statement. Not sure about the possibility step by step, but using a profiler in combination with print and selection operators (if using temporary tables) to view their contents, as the proc run quickly sheds light on what is happening.