Table Variable - Column Suggestions Disappearing

Digga

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

Dan_J

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.

Digga

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

Dan_J

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.

Dan_J

Hi again @Digga

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

Digga

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

Digga

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

Digga

FYI - Upgraded SQL Prompt to 10.6.3.18228 and the problem still persists.

Dan_J

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!

Digga

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

Dan_J

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?

Digga

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

Dan_J

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.

Dan_J

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.

Digga

Hey @Dan_J

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

Have a great weekend.

Kind regards,

Digga

Dan_J

Thanks @Digga, you have a great weekend too!

Dan_J

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!

Digga

Hey @Dan_J

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

Have a great weekend.

Kind regards,

Digga

Dan_J

Hey @Digga

Thanks!

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

Digga

Hey @Dan_J

Fixed in 10.10. 

Many thanks.

Dan_J

Hey @Digga

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

Thank you for your patience with us on this!