SqlDependency.Stop() in Microsoft.Data.SqlClient 1.1.2 doesn't remove stored procedure

[GitHubPang]

Describe the bug

Microsoft.Data.SqlClient.SqlDependency.Stop(string) in Microsoft.Data.SqlClient 1.1.2 does not remove the stored procedure created.

Steps to reproduce:

1. Use Command Prompt (cmd) to start a SQL Server in Docker:

   docker run -it -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=GitHub4567Issue$%^&Password" -p 4567:1433 mcr.microsoft.com/mssql/server:2017-latest

2. Start Microsoft SSMS and connect to the SQL Server.

   • Server type: Database Engine
   • Server name: localhost,4567
   • Authentication: SQL Server Authentication
     • Login: sa
     • Password: GitHub4567Issue$%^&Password

3. Open a New Query tab, and run the following SQL to create a database ready for SqlDependency:

   CREATE DATABASE testGitHubIssue;
   ALTER DATABASE testGitHubIssue SET ENABLE_BROKER;

4. In Object Explorer, go to localhost,4567 > Databases > testGitHubIssue > Programmability > Stored Procedures. Verify that there're no user stored procedures yet.

5. Start Visual Studio, and choose Create a new project > C# Console App (.NET Core) to create a new project.

6. In the project, choose Project > Manage NuGet Packages, find the Microsoft.Data.SqlClient package, and install the version 1.1.2.

7. In Program.cs, type this:

   using Microsoft.Data.SqlClient;
   
   namespace ConsoleApp1
   {
   	class Program
   	{
   		static void Main(string[] args)
   		{
   			var connectionString = "Data Source=localhost,4567;Initial Catalog=testGitHubIssue;Persist Security Info=True;User ID=sa;Password=GitHub4567Issue$%^&Password";
   			SqlDependency.Start(connectionString);
   			SqlDependency.Stop(connectionString);
   		}
   	}
   }

   then set a breakpoint on the line with SqlDependency.Stop.

8. Run the console program in Visual Studio, and wait until it stops at the breakpoint. Now in SSMS, do a refresh and verify that a new stored procedure is created with a name similar to SqlQueryNotificationStoredProcedure-5e2958e2-f0e5-46cb-9f6e-9db02488c545.

9. In Visual Studio, click Continue to resume the program. Now in SSMS, do a refresh and check the stored procedures.

Expected behavior

The stored procedure should be removed by SqlDependency.Stop().

Actual behavior

The stored procedure remains. Note that this does not happen to Microsoft.Data.SqlClient 1.1.1.

Version info

Microsoft.Data.SqlClient version: 1.1.2
.NET target: .NET Core 3.1
SQL Server version: Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64)
Operating system: Microsoft Windows 10 Pro Version 1909 (OS Build 18363.778)
Visual Studio: Microsoft Visual Studio Community 2019 Version 16.5.4

Additional info

Rider says an exception like this is caught and swallowed in SqlDependencyProcessDispatcher.cs line 834.

Microsoft.Data.SqlClient.SqlException (0x80131904): The transaction operation cannot be performed because there are pending requests working on this transaction.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SqlDependencyProcessDispatcher.SqlConnectionContainer.TearDownAndDispose()
ClientConnectionId:47b2dbcb-b256-416e-8b78-a403d8a5d81c
Error Number:3981,State:1,Class:16

[cheenamalhotra]

Hi @GitHubPang

We're investigating this issue and will share update soon!

[cheenamalhotra]

Hi @GitHubPang

We will include fix for this issue soon in upcoming releases.
PR #544 and #545 fix the issue.