Unable to ALTER SPROC - The connection is closed

[mstephens-adig]

• Azure Data Studio Version: 1.0.0

Steps to Reproduce:

1. Open connection to Azure SQL Server using SQL Authentication that has db_datareader, db_datawriter and the account has GRANT EXECUTE set.
2. Locate existing SPROC in table view, click Script as Alter, new window with existing sproc will open.
3. Press go to execute the ALTER with no changes.
4. The following message is displayed in the query results tab. Started executing query at Line 1 Query failed: Invalid operation. The connection is closed.Total execution time: 00:00:00.003
5. The same procedure works in SSMS using same user account and same sproc.

[kburtram]

@mstephens-adig could you please provide a couple additional details?

1. This is happening on Windows?
2. Scripting other objects like tables is working fine?

[mstephens-adig]

@kburtram very sorry,
1) I'm on a Mac running High Sierra with latest security updates. 10.13.16 I had been using SQL Operations Studio on Sierra and had no issues, I upgraded to Azure Data Studio last week during a OS upgrade. I've updated to Azure Data Studio 1.1.3 this morning as well and that has not solved the issue. Extensions installed: Server Reports 0.1.3 SQL Server Agent 0.34.0 SQL Server Import 0.3.0 SQL Server Profiler 0.2.0

2) In an attempt to provide a full reproduction I created a simple set of procedures for both CREATE and ALTER and that seems to function as expected. Example: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[AzureDataStudioProcExample] @someId INT , @myId INT OUTPUT AS BEGIN SET @myId = (SELECT TOP 1 Id from Item where Id = @someId) END GO

I'm experimenting with the two PROCs I was editing this morning and trying to determine the cause, they have previously succeeded using SQL Operations Studio in the past and the exact same ALTER statement this morning succeeds in SSMS on my Windows VM.

I will continue trimming back those procedures to narrow in on the exact cause, at this point maybe its the length of the sproc? It is currently over 1300 lines long.

[mstephens-adig]

Update - I've uninstalled all extensions, that doesn't resolve the issue.

[kburtram]

@mstephens-adig thanks for the update. I'll try to repro and get back to you.

[kburtram]

@mstephens-adig I wasn't able to repro this issue using the latest build and the simple sproc in your example. Are you still hitting this? Is there any additional context you can think of that may narrow do the repro.

I've basically did the following.

1. Connect to SQL Server 2017
2. Create a new database
3. Create an sproc using the example provided above
4. Script as Alter the sproc
5. Execute the Alter statement

And this works as expected.

[mikejohanson]

I have the same error, but different use case. Same environment (MacOS High Sierra, Azure Data Studio), replicated on two different Macs. Does not show itself on Windows 10 in Azure Data Studio or SSMS. If you just do a couple insert statements, it won't throw the error either. Furthermore, seems its only with an Azure DB, doesn't happen with a localdb in Docker. @kburtram

This error shows itself with the following repro: 1) Create a #tempTable 2) Execute 1000 insert statements 3) Select * FROM #tempTable

i.e.:

create table #mockSales (
    id VARCHAR(50),
    email VARCHAR(50),
    address1 VARCHAR(50),
    address2 VARCHAR(50),
    city VARCHAR(50),
    postalCode VARCHAR(50),
    country VARCHAR(50),
    soldPrice VARCHAR(50),
    soldDate DATE,
    mobilePhone VARCHAR(50),
    workPhone VARCHAR(50),
    firstName VARCHAR(50),
    lastName VARCHAR(50),
    state VARCHAR(50),
    agent VARCHAR(50),
    transferTo VARCHAR(50)
);

insert into #mockSales (id, email, address1, address2, city, postalCode, country, soldPrice, soldDate, mobilePhone, workPhone, firstName, lastName, state, agent, transferTo) values (NEWID(), 'tbrent0@japanpost.jp', '5115 Banding Junction', null, 'Chicago', '60669', 'USA', '$440.92', '11/8/2018', '312-618-3594', '402-201-7624', 'Tedman', 'Brent', 'Illinois', 'Tedman Brent', null);
-- copy and paste 1000 times :) 
...
SELECT * FROM #mockSales

[kolchy]

I am getting the same issue as mentioned above - same Mac setup, any update?

[mstephens-adig]

@kolchy I'm still getting this, I have a couple legacy sprocs that are in excess of 1300 lines long. Azure Data Studio refuses to show it when scripting an alter from the server and also when attempting to execute them.
Same as above, it works 100% fine in SSMS.

[w-corey-masters]

I'm running into this issue as well. I'm using Azure Data Studio 1.13.0 on Mac OS Catalina.

Trying to create Brent Ozar's sp_Blitz* procs on a SQL Server 2016 database. "The connection is closed".

[gothick]

I ran into this when trying to set up the example database for Itzik Ben-Gan's 70-761 exam book. It seemed that Azure Data Studio would simply choke on scripts longer than a certain length. In the end I just gave up and use SSMS on Windows.