Author |
Message |
Mindflux
Joined: 25 May 2013 Posts: 817 Country: United States |
|
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:
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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.
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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.
|
|
Tue Aug 15, 2023 3:33 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
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.
|
|
Tue Aug 15, 2023 7:01 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 817 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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.
|
|
Tue Aug 15, 2023 7:07 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 817 Country: United States |
|
|
|
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.
|
|
Tue Aug 15, 2023 8:45 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
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
|
|
Tue Aug 15, 2023 9:12 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 817 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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.
|
|
Tue Aug 15, 2023 9:23 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 817 Country: United States |
|
|
|
Are you thinking this is an SSMS issue or a problem with S.A?
|
|
Wed Aug 16, 2023 11:54 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
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.
|
|
Wed Aug 16, 2023 8:54 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
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.
|
|
Thu Aug 17, 2023 10:26 pm |
|
![](templates/GreenTech/images/spacer.gif) |
|