New training course: Customizing SQL Prompt. Watch now.

SQL prompt query window stuck executing

gm0593gm0593 Posts: 5 New member
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
Tagged:

Answers


  • 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.


    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • gm0593gm0593 Posts: 5 New member
    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

  • 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?

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • gm0593gm0593 Posts: 5 New member
    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.

  • 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).

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • gm0593gm0593 Posts: 5 New member
    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

  • gm0593gm0593 Posts: 5 New member
    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.
Sign In or Register to comment.