SQL prompt query window stuck executing

gm0593

When I execute SQL inside a SQL Prompt query window in SSMS, the query executes and gives back my results and in the messages tab it gives me a completion time.  However, the query window still says "executing query".

This seems to happen when I have multiple statement blocks with GO after them.  Any ideas?

SQL Server Management Studio 15.0.18369.0

SQL Server Management Objects (SMO) 16.100.46041.41

Microsoft Analysis Services Client Tools 15.0.19342.0

Microsoft Data Access Components (MDAC) 10.0.19041.1

Microsoft MSXML 3.0 6.0 

Microsoft .NET Framework 4.0.30319.42000

Operating System 10.0.19042

SQL Prompt 10.4.6.16406

Dan_J

Hi @gm0593

I'm sorry to hear your experiencing issues with SQL Prompt. Thanks for reaching out to us regarding this.

Before I look into this any further, it would be good to see if you continue to experience this issue after updating to the latest version of SQL Prompt, V10.6.9. This version can be downloaded here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.6.9.19437.exe

Please let me know if updating does or does not resolve this issue for you.

gm0593

I have updated, both SSMS and SQL Prompt.  The issue still happens.

New version info:

SQL Server Management Studio 15.0.18384.0

SQL Server Management Objects (SMO) 16.100.46367.54

Microsoft Analysis Services Client Tools 15.0.19535.0

Microsoft Data Access Components (MDAC) 10.0.19041.1

Microsoft MSXML 3.0 6.0 

Microsoft .NET Framework 4.0.30319.42000

Operating System 10.0.19042

SQL Prompt 10.6.10.19533

Dan_J

Hi @gm0593

My sincere apology for the delay in responding to you on this.

Even though SSMS is stating that the query is still running, do you still appear to be seeing a complete set of results?

If I were to contact you via the ticket we have created for this issue, would you be happy to provide some example syntax that is causing this issue?

gm0593

Yes, I do get full results back.  The script appears to be done (no longer returning any results in the background).  However, I'm unable to continue to use the query window.  I have to copy my script to clipboard, close the query window, open a new one, and paste my script back into it.  Not the end of the world, but annoying.

I would be happy to work with you on the ticket.  I will pay attention to the next time it happens and save off the script for the ticket.

Dan_J

Hi @gm0593

Thanks for clarifying what is happening here, that does seem very strange behavior.

I understand that this may be an inconvenient thing to try, however I do wonder if this issue were to continue to see this issue with SQL Prompt removed/uninstalled?

Thanks also for your willingness to work with us on this, it would be good to find a solution to this for you. It would certainly be very helpful to see a copy of the log file produced after this issue occurs. So that we can see as much logging as possible, it is best to enable verbose logging (SQL Prompt menu > Help > Enable Verbose logging).

gm0593

Sorry it has been a while.  The following script causes the issue where the query window gets stuck executing as outlined above.

USE [TRIDocumentStorage09];

GO

IF EXISTS

(

    SELECT *

    FROM sys.server_principals

    WHERE name = 'AD-DOMAIN\ADUSERNAME'

)

   AND NOT EXISTS

(

    SELECT 1

    FROM sys.database_principals

    WHERE name = 'AD-DOMAIN\ADUSERNAME'

)

BEGIN

    PRINT 'CREATE USER [''AD-DOMAIN\ADUSERNAME''] FOR LOGIN [''AD-DOMAIN\ADUSERNAME'']';

    CREATE USER [AD-DOMAIN\ADUSERNAME] FOR LOGIN [AD-DOMAIN\ADUSERNAME];

END;

GO

IF EXISTS

(

    SELECT 1

    FROM sys.database_principals

    WHERE name = 'AD-DOMAIN\ADUSERNAME'

)

BEGIN

    PRINT 'EXEC sp_AddRoleMember ''db_datareader'', ''AD-DOMAIN\ADUSERNAME''';

    EXEC sp_addrolemember 'db_datareader', 'AD-DOMAIN\ADUSERNAME';

END;

GO

IF EXISTS

(

    SELECT 1

    FROM sys.database_principals

    WHERE name = 'AD-DOMAIN\ADUSERNAME'

)

BEGIN

    PRINT 'EXEC sp_AddRoleMember ''db_datawriter'', ''AD-DOMAIN\ADUSERNAME''';

    EXEC sp_addrolemember 'db_datawriter', 'AD-DOMAIN\ADUSERNAME';

END;

GO

IF EXISTS

(

    SELECT 1

    FROM sys.database_principals

    WHERE name = 'AD-DOMAIN\ADUSERNAME'

)

BEGIN

    PRINT 'EXEC sp_AddRoleMember ''db_executor'', ''AD-DOMAIN\ADUSERNAME''';

    EXEC sp_addrolemember 'db_executor', 'AD-DOMAIN\ADUSERNAME';

END;

GO

gm0593

Also, when I click to cancel running the query that seems to be "stuck", I get an error message from SSMS:  Object reference not set to an instance of an object.