1

I have written a CLR stored procedure (.NET Framework 4.5 and IDE VS 2019) and trying to debug the CLR stored procedure before deploying it to SQL Server 2016. However, I am unable to locate the debug option for CLR DB object.

I did try steps provided on Microsoft document but unable to find [Test Scripts] folder in Solution Explorer. I am using Visual Studio 2019.

https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/debugging-clr-database-objects?view=sql-server-2016

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Mayur Patel
  • 21
  • 4
  • Mayur, this question _should_ be a duplicate of "[How to debug a CLR Stored procedure in VS 2013](https://stackoverflow.com/a/45305204/577765)". Please try the steps I outlined in my answer there and let me know if it works. That was tested on VS 2015, and I expect the behavior to be the same in VS 2019, but if not then I can investigate further. Either way, there are definitely some required steps missing from the MS documentation. – Solomon Rutzky Aug 16 '19 at 13:57
  • Thank you for details. Appreciate it. I tried the mentioned solution but getting an error. It seems I do not have permission. It is giving error [Failed to start debugger. An exception was generated:'The EXECUTE permission was denied on the object 'sp_enable_sql_debug']. I was trying to debug the CLR stored procedure without deploying it on SQL server. Microsoft document has described the steps. I do not see those options in VS 2019. Ref: Microsoft document (Debugging CLR Database Objects): https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms131096(v=sql.100) – Mayur Patel Aug 16 '19 at 15:12
  • Mayur, what do you mean by, "_debug the CLR stored procedure without deploying it on SQL server_"? The code only runs in SQL Server. It needs to exist within SQL Server in order to be callable as the environment that calls it is SQL Server. Visual Studio comes with SQL Server Express LocalDB so that you can easily test stuff like this on your local dev box without having to deploy it anywhere outside of your personal dev environment. I just added a requirement to that other answer: the login used for deploying in debug mode needs to be a `sysadmin`. That might resolve that error. – Solomon Rutzky Aug 16 '19 at 15:28
  • Thank you Solomon, Understood now. I was misreading the Microsoft document. Appreciate your all help and time spent on this question. – Mayur Patel Aug 16 '19 at 18:33
  • No problem. Since this question was about debugging _without_ publishing, that is _not_ a clear duplicate of the other question. I will add my comments as an answer. – Solomon Rutzky Aug 16 '19 at 18:36

1 Answers1

0

What exactly do you mean by:

debug the CLR stored procedure without deploying it on SQL server

? The code only runs in SQL Server. It needs to exist within SQL Server in order to be callable as the environment that calls it is SQL Server. Visual Studio comes with SQL Server Express LocalDB so that you can easily test stuff like this on your local dev box without having to deploy it anywhere outside of your personal dev environment. When you create a database project, the debug connection string (i.e. where it deploys to when publishing to test, whether you start debugging or not) should be preconfigured for your local instance of LocalDB.

Please try the steps I outlined in my answer to "How to debug a CLR Stored procedure in VS 2013". That was tested on VS 2015, and I expect the behavior to be the same in VS 2019. Either way, that answer contains some required steps that are missing from the MS documentation.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171