SA: 12.3.431 Modifying stored procedure with S.A "Edit&

SA: 12.3.431 Modifying stored procedure with S.A "Edit&

Using the "Edit" hyperlink that appears in a bubble above hovered objects like stored procedures... when I go to commit my changes with F5 using that edit link I get an error like:

Quote:

An error occurred while executing batch. Error message is: There is already an open DataReader associated with this Connection which must be closed first.
An error occurred while executing batch. Error message is: There is already an open DataReader associated with this Connection which must be closed first.
An error occurred while executing batch. Error message is: There is already an open DataReader associated with this Connection which must be closed first.
An error occurred while executing batch. Error message is: There is already an open DataReader associated with this Connection which must be closed first.
An error occurred while executing batch. Error message is: There is already an open DataReader associated with this Connection which must be closed first.
An error occurred while executing batch. Error message is: There is already an open DataReader associated with this Connection which must be closed first.

If I use the SSMS object explorer to edit the stored procedure I do not have that issue committing the changes with F5.

Quote:

SQL Server Management Studio 19.1.56.0
SQL Server Management Objects (SMO) 16.200.48044.0+eeb184ee48a91ebc6a27a5d192c0d67bdfaae8b6
Microsoft T-SQL Parser 17.0.8.0+3c5555b8bd579d12add8f155f1dbc871e3e734c4
Microsoft Analysis Services Client Tools 16.0.20010.0
Microsoft Data Access Components (MDAC) 10.0.22621.521
Microsoft MSXML 3.0 4.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.22621

Moreover:

If I use the code hyperlink in the same pop-up bubble (next to the edit hyperlink), then I press the "COPY TO EDITOR" button/icon in the upper right to move the code into the editor window, and change "CREATE PROCEDURE" to "ALTER PROCEDURE" and hit F5... it commits just fine.

I'm unable to reproduce it so far. Does that happen with a freshly started SSMS instance?

I'm not sure it's related to how you get the code inserted into the editor. What I think happened, you pressed F5 key while SQL Assistant in the background was reading data from the database using active editor's connection, and that process got interrupted leaving the data reader open and blocking all further database operations. This is a rare scenario. If you face it again, please copy code to a new tab and execute it from there.

SysOp wrote:

I'm unable to reproduce it so far. Does that happen with a freshly started SSMS instance?

I'm not sure it's related to how you get the code inserted into the editor. What I think happened, you pressed F5 key while SQL Assistant in the background was reading data from the database using active editor's connection, and that process got interrupted leaving the data reader open and blocking all further database operations. This is a rare scenario. If you face it again, please copy code to a new tab and execute it from there.

The age of the SSMS instance doesn't seem to matter. I just repro'd it on my laptop from home over VPN.

If I get the data reader error and then press Ctrl+F9 (But Ctrl+F5, the native parse hotkey does not fix the datareader message) for a syntax check, and then hit F5 the execution completes.

Edit: Also using Ctrl+F9 to execute the editor window via SQL Assistant, I do not experience the datareader error.

Edit2: If I pull up Database Explorer (Ctrl-W) and drill down to the procedure, right click and do edit procedure the datareader error does not occur then.

I spun up a LocalDB instance and made a database and a stored procedure that just selects * from sysobjects.

using the popup on a new editor window that only contains:

"exec proc_name" and hitting edit and then pressing F5 (immediately, or waiting for up to a minute) I get the datareader error.

I reproduced this error with SSMS 19.1.56. When I close the tab after getting that error, I also get the following error

Unable to query transaction count. The SQL text editor window will close without committing any open transactions.
------------------------------
ADDITIONAL INFORMATION:
There is already an open DataReader associated with this Connection which must be closed first. (Microsoft.Data.SqlClient)

It looks like there is some clash, SSMS is trying to execute something in the background too

SysOp wrote:

I reproduced this error with SSMS 19.1.56. When I close the tab after getting that error, I also get the following error

Unable to query transaction count. The SQL text editor window will close without committing any open transactions.
------------------------------
ADDITIONAL INFORMATION:
There is already an open DataReader associated with this Connection which must be closed first. (Microsoft.Data.SqlClient)

It looks like there is some clash, SSMS is trying to execute something in the background too

Yeah I get the popup too.

Are you thinking this is an SSMS issue or a problem with S.A?

We are still investigating. Based on what's known today it seems to be caused by a recent change in SSMS 19.1, leading to some internal collision or racing condition.
We are unable to reproduce it with previous SSMS versions.

It looks like we have figured it out. The sequencing of some internal operations has changed and that is leading to an open DataReader error. We're testing a fix.