What are the challenges you face when working across database platforms? Take the survey
Options

Table Variable - Column Suggestions Disappearing

DiggaDigga Posts: 27 Bronze 3
Hi

When I have the query disconnected, I get the full suggestions list from my table variable, though when I connect to a SQL Server in Azure, column suggestions disappear for my table variable and the schema only shows the first column. When I connect to on-premise SQL Servers, both my local instance and remote within our network, I have all the columns and the table schema available.

Azure connection gives this:


Non Azure connection gives this:


I'm not sure if this is me being a dummy or there's a bug within SQL Prompt.

Any advice you can give would be greatly appreciated.

SQL Prompt: 10.6.0.17688
SSMS: 17.9.1
OS: Windows 10 Enterprise x64 - Version 10.0.19041 Build 19041

Tagged:

Answers

  • Options
    Hi @Digga

    Sorry to hear you're experiencing this issue, thanks for reaching out to us on this.

    The very latest version of SQL Prompt (v10.6.1) contains fixes for Azure related issues. As a first step, please can you update to this version, which can be downloaded here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.6.1.17779.exe

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

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    DiggaDigga Posts: 27 Bronze 3
    Hey Dan

    Thanks for getting back to me.

    Unfortunately the new version behaves in the same way in SSMS. Something I have noticed, both versions, 10.6.0 and 10.6.1, function correctly in Visual Studio 2019 Enterprise 16.8.4.

    Hope this helps and let me know if there's anything else you need from me or need me to test.

    Kind regards,

    Digga
  • Options
    Hi @Digga

    Thanks for your response on this! My apology for the delay in getting back to you!

    I'm sorry to hear that these issues continue in the latest version of SQL Prompt!

    I think the next thing to try would be update SSMS, I see you are currently using v 17.9, would you be happy to update to the latest version (v 18.8)? If so, this can be downloaded here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

    In the meantime, I will pose your query to my colleagues for discussion and will update you again shortly.

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    Hi again @Digga

    My apology, I should have asked you in my last post whether you are using Azure with MFA?

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    DiggaDigga Posts: 27 Bronze 3
    Hey @Dan_J

    I already had 18.5 installed and upgraded to 18.8, though the same behaviour exists in this version of SSMS as well.

    Kind regards,

    Digga
  • Options
    DiggaDigga Posts: 27 Bronze 3
    Hey @Dan_J

    When I raised this issue, yes I was connecting using Azure with MFA. I just got your message about the connection, I changed connection to my local instance to see the expected output, then switched to another Azure SQL Server using SQL Authentication, and unfortunately the behaviour returns. So the common is a connection to an Azure Database, regardless of the connection being Azure with MFA or SQL Authentication.

    Kind regards,

    Digga
  • Options
    DiggaDigga Posts: 27 Bronze 3
    FYI - Upgraded SQL Prompt to 10.6.3.18228 and the problem still persists.
  • Options
    Dan_JDan_J Posts: 454 Silver 2
    Hi @Digga

    My sincerest apology for not responding to you on this before now.

    I am sorry that this issue continues for you, I'd really like to try and find a solution for you on this!

    I think just a couple of things to get my troubleshooting moved forward on this:

    As mentioned before, this clearly appears to be Azure related, however please can you just confirm that the Connection settings within the SQL Prompt Options are set to load suggestions for all databases and schemas and linked servers (see below)?


    Also, if you haven't already, please can you update to the very latest version of SQL Prompt (v.10.6.12). This version can be downloaded directly from here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.6.12.20269.exe

    If you can please let me know if either this issue persists or not after trying the above, please don't hesitate in coming back to me so that I can further advise.

    Again, my sincere apology for the lack of update on this!

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    DiggaDigga Posts: 27 Bronze 3
    Hey @Dan_J

    Thanks for the follow-up.

    I can confirm the settings are the same as your screenshot and I have upgraded to version 10.6.12.20269.

    There's no change in behaviour.

    Kind regards,

    Digga
  • Options
    Dan_JDan_J Posts: 454 Silver 2
    Hi there @Digga

    My apology for the delay in replying to you.

    I have been speaking with my colleagues regarding this. We have tried again to reproduce the issue you are seeing but have so far been unsuccessful.

    Just going back to the first images you provided, there is a difference in the Nullability of the CRMEntity_Id column which is making us question whether it is looking at two different tables (although it doesn't suggest this when looking at the rest of the screenshots you provided):




    I order to help us with our attempts to reproduce this, would it be possible to get a copy of your script used for the above example?



    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    DiggaDigga Posts: 27 Bronze 3
    Hey @Dan_J

    Here's the script:

    DECLARE @entityTable TABLE
        (
            CRMEntity_Id INT IDENTITY(1, 1) ,
            EntityName NVARCHAR(250) NOT NULL ,
            IsEnabled BIT
                DEFAULT 1 ,
            IsEntityModelModified BIT
                DEFAULT 1 ,
            EntityModelModifiedDate DATETIME2
                DEFAULT '1900-01-01' ,
            IsError BIT
                DEFAULT 0 ,
            ErrorMessage NVARCHAR(250) NULL ,
            LastRunTime DATETIME2
                DEFAULT '1900-01-01'
        );

    SELECT *
    FROM   @entityTable AS ET;

    The nullability switch changes as well as the loss in columns with the above script. I just removed IDENTITY from the first column and it worked fine with no difference between Azure or on-premise, when I put it back in the issue occurs again. Remember, in Visual Studio 2019 this worked fine in both cases.

    Kind regards,

    Digga
  • Options
    Dan_JDan_J Posts: 454 Silver 2
    Hey @Digga

    Thank you for your update on this, and for providing the script you are using.

    Reading what you have mentioned about using an IDENTITY column over Azure suggests that these things combined are what is causing suggestions to not appear. Is that correct?

    I will be attempting to replicate this issue again later today and will let you know that outcome as soon as possible.

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    Dan_JDan_J Posts: 454 Silver 2
    Hey @Digga

    We have finally been able to reproduce the issue you are seeing here!

    Our development team will be working on a fix for this bug, I will update this thread as soon as I have an update for you on this.

    Thank you for highlighting this to us.
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    DiggaDigga Posts: 27 Bronze 3
    Hey @Dan_J

    Thanks for the update and nice to know I'm not going mad.

    Have a great weekend.

    Kind regards,

    Digga
  • Options
    Dan_JDan_J Posts: 454 Silver 2
    Thanks @Digga, you have a great weekend too!
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    Hi @Digga

    I'm pleased to advise that a fix for this will be included in the next release of SQL Prompt, which is likely to be at some point next week.

    I will update you again as soon as I am made aware the release is available for you to download.

    Thank you for your patience with us on this!

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    DiggaDigga Posts: 27 Bronze 3
    Hey @Dan_J

    Many thanks for the update and look forward to testing this out.

    Have a great weekend.

    Kind regards,

    Digga
  • Options
    Hey @Digga

    Thanks!

    Have you had a chance to test this following the update?

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • Options
    DiggaDigga Posts: 27 Bronze 3
    Hey @Dan_J

    Fixed in 10.10.  :)

    Many thanks.
  • Options
    Hey @Digga

    Great news! I'm very pleased to hear it!

    Thank you for your patience with us on this!
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.