locked
How to debug tsql code in Visual Studio since SSMS v18 does not have a debugger RRS feed

  • Question

  • So SSMS v18 is not going to have a tsql debugger. I am a heavy user of the SSMS tsql debugger so I need to learn how to use VS to debug tsql stored procedures, functions, lines of code, etc. Are there any articles or how tos? I have looked and found an MSN link that is broken and it only seems to address stored procedures.

    https://support.microsoft.com/af-za/help/316549/how-to-debug-stored-procedures-in-visual-studio-net

    Also, are their issues with having multiple SSMS installed? Guess that I could use a version lower than v18 for debugging only? 
    • Edited by TheBrenda Friday, June 28, 2019 3:54 PM
    Friday, June 28, 2019 3:49 PM

All replies

  • I have found that PRINT and SELECT are very good debug tools. I gave up using SQL debuggers long ago. Occasionally I use trace when I want to see in which order statements are executed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, June 28, 2019 10:01 PM
  • Hi TheBrenda,

     

    I recommend you use 'print' as it is indeed a very good tool. It shows the input batch of code you want to run, and then just execute the message of print results. Here's one example I can show:

    ---here i lose 'm' in 'from '
    DECLARE @s AS NVARCHAR(200);
    SET @s = N'Davis'; -- originates in user input
    
    DECLARE @sql AS NVARCHAR(1000);
    SET @sql = N'SELECT empid, firstname, lastname, hiredate
    FRO HR.Employees WHERE lastname = N''' + @s + N''';';
    
    PRINT @sql; -- for debug purposes
    EXEC (@sql);
    GO
    
    --the message is as below 
    /*
    SELECT empid, firstname, lastname, hiredate
    FRO HR.Employees WHERE lastname = N'Davis';
    */
    
    --then i just copy and paste the message and run it. 
    
    SELECT empid, firstname, lastname, hiredate
    FRO HR.Employees WHERE lastname = N'Davis';
    
    /*
    Msg 102, Level 15, State 1, Line 23
    Incorrect syntax near 'HR'.
    */

    Another useful tool is 'TRY-CATCH' which is a classic error-handling construct. You specify your usual code in the TRY block and the error-handling code in the CATCH block. If code in the TRY block completes successfully, the CATCH block is skipped. If there’s an error, SQL Server passes control to the first line of code in the CATCH block. Here's the syntax:

    BEGIN TRY ...
    END TRY
    BEGIN CATCH ...
    END CATCH;

    For more information about try-catch, you can check below link:

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

     

    Here's another thread about debug stored procedures, you can take a look.:

    https://www.sqlshack.com/debugging-stored-procedures-sql-server-management-studio-ssms/

     

    By the way, if you want to know how to use VM to debug, please try to send question in :

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?category=visualstudio

     

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 1, 2019 9:53 AM
  • I have been a developer for a really, really long time in multiple languages. Not having a debugger for your language and worse yet having it taken away is just mind bending. I have been using a debugger for over an embarrassing number of years. Has the world gone mad?

    Vote to add debugger back

    https://feedback.azure.com/forums/908035-sql-server/suggestions/35881492-put-debugger-back-into-ssms-18#comments

    I am still asking

    1) how to use the VS debugger for tsql code. And yes this is the correct forum and audience for this question. Some tsql person must have already written something addressing this issue especially since the debugger is being removed. 

    2) can you have multiple SSMS versions installed on the sql server. does it cause any problems?

    Monday, July 1, 2019 1:26 PM
  • Hi TheBrenda,

    I concur with all your points.

    It is possible to install multiple SSMS versions on the same machine.

    Monday, July 1, 2019 2:01 PM
  • Erland, 

    What function do you use to PRINT/SELECT really long strings. I have one that I was using, but it inserted line break at 4k (if the line was greater than 4k) and would lose the first character of the next string. Really a pain to put the string back together.

    https://www.codeproject.com/Articles/18881/SQL-String-Printing

    Did a quick Google prior to Submitting this and found a Jeff Model function that was buried in some comments at 

    https://www.sqlservercentral.com/forums/topic/longprint#post-3658884

    Really nice function. And it respects the CRLF which most SQL 2008 and SQL 2008 R2 PRINT/SELECT statements do not. Yes, I know SQL 2008 and 2008 R2 are out of support.

    CREATE FUNCTION [dbo].[ShowLongString]
    /**********************************************************************************************************************
     Purpose:
     Display a string of more than 8000 characters.  The string can be Dynamic SQL, XML, or just about anything else.
    
     Note that this function does use XML and, while normally quite faithful, there are characters (I've not taken the time
     to identify them because they're edge cases that I don't deal with) that it just won't handle. It will, however, 
     handle most control-characters below ASCII 32.
    -----------------------------------------------------------------------------------------------------------------------
     Usage:
    --===== Example with Dynamic SQL
    DECLARE @SQL VARCHAR(MAX);
     SELECT @SQL = '
     SELECT somecolumnlist
       FROM some table with joins
    ;'
    ;
     SELECT LongString 
       FROM dbo.ShowLongString(@SQL)
    ;
    --===== Example with a call to a table or view
     SELECT sm.Object_ID, Definition = ls.LongString 
       FROM sys.SQL_Modules sm
      CROSS APPLY dbo.ShowLongString(sm.Definition) ls
    ;
    -----------------------------------------------------------------------------------------------------------------------
     Credits:
     1. I learned this trick from a post by Orlando Colamatteo at the following link.  It has served me very well since 
        then. Thanks, Orlando.
        https://www.sqlservercentral.com/Forums/FindPost1468782.aspx
    -----------------------------------------------------------------------------------------------------------------------
    Revision History:
    Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
    **********************************************************************************************************************/
    --===== Declare the I/O for this function
            (@pLongString VARCHAR(MAX))
    RETURNS TABLE WITH SCHEMABINDING AS
     RETURN
     SELECT LongString =
            (
             SELECT REPLACE(
                        CAST(
                            '--' + CHAR(10) + @pLongString + CHAR(10)
                        AS VARCHAR(MAX))
                    ,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
                 AS [processing-instruction(LongString)] 
                FOR XML PATH(''), TYPE
            )
    ;
    GO
    GRANT SELECT ON [dbo].[ShowLongString] TO [public] AS [dbo]
    ;

    Now call it with this example

    declare @t varchar(max) = cast(replicate('1', 8000) as varchar(max)) + 'x' + char(13) + char(10) + replicate('2', 8000) + 'x' + char(13) + char(10) + replicate('3', 8000) + 'x' + char(13) + char(10) +'z'
    print len(@t)
    
    select @t
    print @t
    
    SELECT LongString 
       FROM dbo.ShowLongString(@t)

    Monday, July 1, 2019 2:19 PM
  • Hi TheBrenda,

    As I already mentioned, it is possible to install multiple SSMS versions on the same machine.

    So keep  your SSMS v.17.9.1 installed to allow inline debugging of SQL code.

    Monday, July 1, 2019 7:05 PM
  • What function do you use to PRINT/SELECT really long strings. I have one that I was using, but it inserted line break at 4k (if the line was greater than 4k) and would lose the first character of the next string. Really a pain to put the string back together.

    This is a procedure written by a co-worker of mine:

    CREATE OR ALTER PROCEDURE SystemPrint @str nvarchar(max) AS
    SET XACT_ABORT, NOCOUNT ON;
    DECLARE @noofchunks int,         @chunkno    int = 0,          @pos        int = 0, -- substring start posotion
            @len        int;     -- substring length

    SET @str = replace(@str, nchar(13), '');
    SET @noofchunks = ceiling(len(@str) / 4000.0);

    WHILE @chunkno < @noofchunks
    BEGIN
       SET @len = 4000 - charindex(nchar(10) COLLATE Latin1_General_BIN2,
                                   reverse(substring(@str, @pos, 4000)));
       PRINT substring(@str, @pos, @len);
       SET @chunkno += 1;
       SET @pos += @len + 1; -- accumulation + LF
    END

    Debuggers may have its place, but T-SQL is not really liek C++. They are good if you want to do things like single-step and set breakpoints. But with SQL, you often want to see intermediate result sets etc, which the debugger does not do a good work with.

    Add to this that using a debugger on any other instance than one on your local machine can be very tricky to get going.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, July 1, 2019 10:35 PM
  • Erland, your co-workers SystemPrint sproc also cuts off a character from the end of the line when it has to break up a line larger than 4k. You can see it in these two examples

    declare @t varchar(max) = cast(replicate('1', 8000) as varchar(max)) + char(13) + char(10) + replicate('2', 8000) + char(13) + char(10) + replicate('3', 8000) + char(13) + char(10) + 'z'
    declare @t2 varchar(max) = cast(replicate('1', 8000) as varchar(max)) + replicate('2', 8000) + replicate('3', 8000) + 'z'
    
    exec systemprint @t
    select * from dbo.ShowLongString(@t)
    
    exec systemprint @t2
    select * from dbo.ShowLongString(@t2)


    • Edited by TheBrenda Tuesday, July 9, 2019 3:18 PM
    Tuesday, July 9, 2019 3:18 PM
  • I think they wrote the function to look at generate dynamic SQL. Well-generated dynamic SQL should have line breaks more often than every 4000th character. (Although one don't with these guys. They write insanely long lines in their static SQL - I have to turn on word wrap to be able to work with their code!)

    (And to their defence, Janne's original version may handle your test case correctly, because he had varchar(MAX). What I posted is an adapted version for a new version on my article on dynamic SQL, and since dynamic SQL should be nvarchar(MAX), I used nvarchar.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 9, 2019 9:47 PM
  • Has the world gone mad?

    No... just the folks at Microsoft. ;-)  They also originally did away with SQL Diagrams, which they brought back.

    To be honest, I've never used the debugger just because of the way I write code.  But, I agree... removing the debugger altogether, especially one that apparently worked, was a giant leap backwards proving, once again that...

    "Change is inevitable... change for the better is not".


    --Jeff Moden

    Friday, January 3, 2020 2:50 AM
  • Erland,

    Am I missing something?

    create function dbo.foo ()
    Returns integer
    as
    begin
      print 'foo';
      return 1;
    end;
    go
    
    Msg 443, Level 16, State 14, Procedure foo, Line 5 [Batch Start Line 84]
    Invalid use of a side-effecting operator 'PRINT' within a function.
    
    drop function dbo.foo;
    go
    
    create function dbo.foo ()
    Returns integer
    as
    begin
      select 'foo';
      return 1;
    end;
    go
    
    Msg 444, Level 16, State 3, Procedure foo, Line 5 [Batch Start Line 99]
    Select statements included within a function cannot return data to a client.
    

    Simon

    (A heavy user of SSMS TSQL Debugger)




    Wednesday, January 15, 2020 10:59 PM