question

SudipBhatt-9737 avatar image
0 Votes"
SudipBhatt-9737 asked AmeliaGu-msft answered

How to debug SP procedure from SSMS

My sql server database is Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

i want to debug SP line one by one which was possible before but now not getting this option. it is very hectic to debug a big SP code with PRINT.

can you guide me how to debug SP without PRINT?

any 3rd party tool exist which help me to debug the SP code line one by one just like code debugging Visual studio.
tell me few 3rd party tool which allow me to do this.

thanks

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I provided you a working solution for SSMS in my answer.
Did you have a chance to try it?

0 Votes 0 ·
AmeliaGu-msft avatar image
1 Vote"
AmeliaGu-msft answered

Hi @SudipBhatt-9737,

> I have VS 2019 community Ed.

If you really have VS 2019, you can go to menu Tools-> Get Tools and Features.
Then select SQL Server Data Tools under Data storage and processing.

47122-01.jpg

> I do not have VS 2017 in my pc.

You can use SSDT in Visual Studio 2019.
Or you can download Visual Studio 2017 from here.

> please guide me things are not getting clear how to do it.

After Installing SSDT with Visual Studio 2019, Please go to menu Tools->Connect to Server/Connect to database. Then click on SQL Server Object Explorer to see you Database.
47135-02.jpg

To debugging SP, go to database->Programmability->Stored Procedures-> right click the procedure you want to debug->select Debug Procedure.

47123-03.jpg

For more details, please refer to this article.
Or you can follow the steps from this doc.
Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



01.jpg (69.8 KiB)
02.jpg (15.5 KiB)
03.jpg (28.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered SudipBhatt-9737 edited

AFAIK the feature to debug a stored procedure in SSMS was removed, but you can use SSDT = "SQL Server Data Tools" to debug a SP, see https://docs.microsoft.com/en-us/sql/ssdt/how-to-debug-stored-procedures?view=sql-server-ver15

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

do i need to download anything because i saw there a download button ?

after download & install how could debug SP.....please guide me with details instruction. thanks

0 Votes 0 ·

Hi @SudipBhatt-9737,
In addition, you can download SSDT from here.
Best Regards,
Amelia


0 Votes 0 ·

SSDT will be a part of VS2019 ?

so after install do i need to open VS2019 to connect sql server to debug SP ?
please guide me things are not getting clear how to do it ?



Please give me download link regarding SSDT for VS 2019. i have VS 2019 community Ed but i do not have VS 2017 in my pc.
i go to your link but found no download link for SSDT for VS2019 rather i found SSDT for VS2017.

i am looking for detail instructions. thanks

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered DSPatrick commented

So far, last supporting inline debugging SSMS version is 17.9.1
You should be able to install that SSMS version from here: ssms 17.9.1

Additionally, there is a request/feedback to restore debugging in latest SSMS version. Please go ahead and up-vote it here: put-debugger-back-into-ssms-18

Same with your favorite topic. Please up-vote: sql-server-vnext-post-2019-and-nosql-functionality


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

With SSMS 17.9.1 version can i connect my database because i am using sql server 17 but version is higher.

0 Votes 0 ·

It shouldn't be a problem.
Microsoft disconnected SQL Server versions and SSMS long time ago.
That's why they both coming as a separate installs.

0 Votes 0 ·

voted them both up


0 Votes 0 ·