Unable to view tables in the dropdown

[Naval01]

Issue Type: Bug

After the new update, I cannot find the tables in the Table Dropdown.
I can see them when I use SSMS.
I can only see system tables, even though I made a connection to a particular database.

Azure Data Studio version: azuredatastudio 1.40.0 (3fbaa3d, 2022-11-11T22:47:05.361Z)
OS version: Windows_NT x64 10.0.19044
Restricted Mode: No
Preview Features: Enabled

[github-actions]

We need more info to debug your particular issue. If you could attach your logs to the issue (ensure no private data is in them), it would help us fix the issue much faster.
First open the Settings page, find the Mssql: Tracing Level setting and change that to All then restart ADS and repro your issue.
Next there are two types of logs to collect:

Console Logs

• Open Developer Tools (Help -> Toggle Developer Tools)

• Click the Console tab

• Click in the log area and select all text (CTRL+A)

• Save this text into a file named console.log and attach it to this issue.

• Developer Tools can be closed via Help -> Toggle Developer Tools
  Application Logs

• Open command palette (Click View -> Command Palette)

• Run the command: Developer: Open Logs Folder

• This will open the log folder locally. Please zip up this folder and attach it to the issue.

[aasimkhan30]

@Naval01 , can you please follow those steps and add logs?

[aparkhills]

I am on MacOS (Monterey 12.3.1) and encountering the same issue after the latest update.

Repro scenario:

1. Right-clicked on the server name and clicked Connect option

2. Was given this error message: https://bit.ly/3tHCbRW

3. Clicked the Enable button and ADS connected to the database

I can view the table names on the management screen (welcome screen) but there is no dropdown on the server name as the server name still has red dot instead of green dot.

Thanks!

[aparkhills]

@aasimkhan30 - Is there any update on this ticket? ADS has been an important part of my daily routine and I am still experiencing the issue mentioned above. Thank you for your time.

[aasimkhan30]

@aparkhills,I am sorry to hear that. Your error seems to be different than this issue. Make sure you are enabling trust server certificates. Please let me know if that fixes your issue.

[Charles-Gagnon]

@aasimkhan30 @aparkhills Your issue is likely this one some other people have also hit : #20192, please follow up with @cheenamalhotra on that issue to try and help figure out what's going on with that error

[Charles-Gagnon]

@cheenamalhotra We may want to look at the logic for displaying the trust server certificate here - it seems like it shouldn't have been shown in that case (since my understanding from looking at the other issue thread is that this problem is some other internal issue and not likely to be fixed by setting trust server certificate to true).

[aparkhills]

@aasimkhan30 @Charles-Gagnon @cheenamalhotra - @aasimkhan30's comment above fixed my issue permanently. No more connection or TCP error message. Thank y'all!

[aasimkhan30]

@Charles-Gagnon @cheenamalhotra it seems that enable trust certificate in the error dialog did not function properly. Is that a known issue?

[gregveres]

I am seeing the same problem as @Naval01.
I am connecting to a SQL Database hosted in Azure. This update completely blew away my ability to see the tables. Everything that @Naval01 said above is what I see.

I have did what the github bot suggested to do and have attached the two files here, after removing the name of my server, user and database.

console.log
log_directory.zip

[cheenamalhotra]

Can someone try to disable Encrypt (set it to false) and try again?
We would be able to rule out authentication factors if the issue persists.

Steps to follow:

• Edit Connection
• Set Encrypt=false
• Connect

[aasimkhan30]

@gregveres, from the logs that you have shared, we can see that the queries are executed on master.

Can you please let us know the result of the query "SELECT @@Version"

[gregveres]

Can someone try to disable Encrypt (set it to false) and try again? We would be able to rule out authentication factors if the issue persists.

Steps to follow:

• Edit Connection
• Set Encrypt=false
• Connect

I had tried all combinations of the encryption flags before posting. They all produce the same result.

[gregveres]

@gregveres, from the logs that you have shared, we can see that the queries are executed on master.

Can you please let us know the result of the query "SELECT @@Version"

Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 18 2022 13:24:45 Copyright (C) 2022 Microsoft Corporation

Now, since you are saying that the db that is being selected is master rather than the db that I specified, I think you found the problem.

Before I upgraded, I was not selecting any specific db. When I connected to the server, I had to select the db, then I could open the tables. After the upgrade, I saw what was shown in the original post of this issue.

I then deleted that server and recreated it by connecting to my azure account. When I redid the connect, I explicitly selected the DB that I want to work with. I still got the results of the original post.
Even right now, I right click on the server and said edit connection. I can plainly see that the database that is selected is the one I want to work with.

In fact, when I right click on the server and I say manage, I see the list of tables that are in the db I want to work with, not the list of tables in the master db.

So I would say that your bug is that in some situation, your connection code is ignoring the configurated database to connect to.
BTW, I cleaned the logs I posted. Everywhere that it says <db_name> in the log, the original log had the name of the db I am trying to connect to, so I don't know why you are saying it isn't trying to connect to that db. Doesn't the configuration in the files show that it is trying to connect to the database I specified?

[gregveres]

I think I have a work around.
When I edit my connection, rather than selecting the database I want to connect to, I select "master". When I do that, I get two options under the server in the tree: Databases and Security. When I open the Databases item, I get System Databases and the database I want to connect to. I can then open .... Oh wait. I can't open it. When I open it, the arrow in the tree turns down, but the is nothing under the database name.

This release is really messed up, making it unusable. I guess I should figure out how to download and install the previous version.

[Charles-Gagnon]

Sorry to hear that @gregveres - we're currently working on identifying the issue and will update with any further information.

In the meantime for those wanting to go back to the previous version for the time being you can get it from here : https://github.com/microsoft/azuredatastudio/releases/tag/1.39.1

(make sure you disable auto-update if you had that enabled so you aren't updated back to 1.40.0)

[gregveres]

When I went to uninstall ADS, there were two versions installed. 1.37.1 and then 1.40.0 (User). I don't know if that could cause this or not.
I didn't directly install either of these. I installed a version a few years ago and it has been upgrading itself ever since.

[kburtram]

@gregveres thanks for providing the additional context on this bug. We don't have a local repro yet and are working on that. There was a commit included in this release that changed the behavior in this scenario. I'd like to confirm if reverting this commit helps resolve this issue. Could you please verify this test build in this scenario, if possible? If the error persists, could you please post updated logs from this build?

Win x64 Zip - https://azuredatastudiobuilds.blob.core.windows.net/test/1.41.0/azuredatastudio-insider-1.41.0-oe-revert.zip
macOS x64 Zip - https://azuredatastudiobuilds.blob.core.windows.net/test/1.41.0/azuredatastudio-insider-1.41.0-oe-revert-darwin.zip

[leon3011]

@kburtram i'm not @gregveres but i had the same problem and with your version it is working again.

[gregveres]

@kburtram I am @gregveres :) and I have tried the insider build you provided and it worked for me too. It looks like you have found the bad commit.

[kburtram]

Thanks for confirming the test build! We'll work on getting this resolved in the official builds.

[Charles-Gagnon]

@gregveres @leon3011 While we work on getting the hotfix out, would you mind running this query against the DB you're having the problems with?

SELECT * FROM sys.database_service_objectives
SELECT * FROM sys.databases

[gregveres]

The first query results in:

1	System	System0	NULL
4	Standard	S0	NULL

The second query results in:

master	1	NULL	0x0106000000000064800000000077EACA3CDFDC9B193D1A4D9689EA6B6717E40D	2016-04-12 00:38:04.623	100	SQL_Latin1_General_CP1_CI_AS	0	MULTI_USER	0	0	0	0	ONLINE	0	0	0	1	ON	1	1	FULL	2	CHECKSUM	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	00000000-0000-0000-0000-000000000000	0	0	NOTHING	0	0	0	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	0	NONE	120	0	DISABLED	0	0	0	0	1	2	SQL_Latin1_General_CP1_CI_AS	<guid>	0	1	0	1	1	1	0	0
<db_name>	4	NULL	0x0106000000000164800000000077EACA384726C23C15AF4481845B3A2528FFDF	2016-04-18 04:01:25.390	100	SQL_Latin1_General_CP1_CI_AS	0	MULTI_USER	0	0	0	0	ONLINE	0	0	0	1	ON	1	1	FULL	2	CHECKSUM	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	1	0	0	0	0	0	00000000-0000-0000-0000-000000000000	0	0	NOTHING	0	0	0	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	0	NONE	120	0	DISABLED	0	0	0	0	1	2	SQL_Latin1_General_CP1_CI_AS	<guid>	0	1	0	1	1	1	0	0```

[Charles-Gagnon]

Hey @gregveres - the logs you originally attached seem to be for a connection to master. Could you make sure you're running this with a connection directly to the user DB?

In addition - if you could run this query using the same connecting to your user DB that'd be helpful. (replacing <DB_NAME> with the name of the db you're connecting to)

SELECT SERVERPROPERTY('EngineEdition')
create table #dso (database_id int primary key, azureEdition sysname null, engineEdition int)
if serverproperty('EngineEdition') = 11
BEGIN
    insert into #dso select database_id, NULL, 11 from sys.databases
END
ELSE
BEGIN TRY
    exec sp_executesql N'
    insert into #dso select database_id, edition,
    case when edition = ''DataWarehouse'' then 6 else 5 end
    from sys.database_service_objectives'
END TRY
BEGIN CATCH
    insert into #dso select database_id, N'', case when [name] = 'master' then 5 else 0 end from sys.databases
END CATCH

SELECT * FROM #dso
SELECT
case when dtb.name = 'master' then ISNULL(dso.engineEdition, 5) else ISNULL(dso.engineEdition, 0) end AS [RealEngineEdition]
FROM
sys.databases AS dtb
LEFT OUTER JOIN #dso dso ON dso.database_id = dtb.database_id
WHERE
(dso.engineEdition != 0)and(dtb.name='<DB_NAME>')

drop table #dso

[gregveres]

@Charles-Gagnon Ok, what version do you want me to be running and which database do you want my configuration to be pointing to?
For the second question, the choices are: master, or <db_name>.

I am a little concerned running your query against my database since this is a production database.

[Charles-Gagnon]

Running all of them would be great - here's the full list of all the separate queries in one batch :

-- Query 1
SELECT * FROM sys.database_service_objectives

-- Query 2
SELECT * FROM sys.databases

-- Query 3
SELECT SERVERPROPERTY('EngineEdition')

-- Query 4
create table #dso (database_id int primary key, azureEdition sysname null, engineEdition int)
if serverproperty('EngineEdition') = 11
BEGIN
    insert into #dso select database_id, NULL, 11 from sys.databases
END
ELSE
BEGIN TRY
    exec sp_executesql N'
    insert into #dso select database_id, edition,
    case when edition = ''DataWarehouse'' then 6 else 5 end
    from sys.database_service_objectives'
END TRY
BEGIN CATCH
    insert into #dso select database_id, N'', case when [name] = 'master' then 5 else 0 end from sys.databases
END CATCH

SELECT * FROM #dso

SELECT
case when dtb.name = 'master' then ISNULL(dso.engineEdition, 5) else ISNULL(dso.engineEdition, 0) end AS [RealEngineEdition]
FROM
sys.databases AS dtb
LEFT OUTER JOIN #dso dso ON dso.database_id = dtb.database_id
WHERE
(dso.engineEdition != 0)and(dtb.name='<DB_NAME>')

drop table #dso

All of these should be run directly against your user db (<db_name>).

These queries are very lightweight, and something that's executed anytime a user connects to a database with ADS/SSMS regardless. So this shouldn't be a problem to run even against a prod DB. But run whatever you can and I'll make do with what we're able to get.

[Charles-Gagnon]

Here's what my investigation has found out. I was never able to directly repro the issue, but by manually changing the query that seemed to be causing this I was seeing the behavior described above.

1. When we create an OE session we create the DatabaseTreeNode, which then creates the SMO Database object set to the database specified in the session and calls Refresh() in order to populate its child properties

2. Something then happened when running the queries while initializing the DB properties. Given that reverting the change above fixed it for the customers it seems like something is happening with sys.database_service_objectives that makes it not return a row for the user DB. This part I'm still unclear on since it doesn't appear an actual error occurs, but if I manually modify the query to only return a row for master then I get the same behavior so that is likely where the issue lies. Will require more investigation as part of the follow up

3. Because it didn't get a row from sys.database_service_objectives for the specified DB then the the reader being used for initialization is empty and it hits this check here : https://github.com/microsoft/sqlmanagementobjects/blob/main/src/Microsoft/SqlServer/Management/Smo/SqlSmoObject.cs#L1681 (we see this error in the customer logs)

4. Because it returned null there it considers the initialization to have failed and sees it as a "new" object - which means the state is left as Creating

5. Then in STS this check here sees that and recreates the Database object - except this time against master. https://github.com/microsoft/sqltoolsservice/blob/main/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/SmoModel/DatabaseTreeNode.cs#L29

6. This succeeds and thus we're left with the OE sessions having a connection against master instead of the user DB - which means all future expansion requests are done at the master level (so for tables for example it does fetch the tables, but for the wrong database)

Follow-up questions/tasks :

1. Why/how did the database_service_objectives query fail (or get wrong results)? Anyone else who've hit this issue, there is where having you run the above query and giving us the results may help narrow this down.
2. The fall back to master was added in fall back to master when connected to secondary sqltoolsservice#854 - we should investigate the fix here and see if this is something we still need and whether there's a better way to do it @shueybubbles
3. Any additional logging for STS/SMO to help with debugging these kinds of issues we can add?

[martinw3]

Hi, I am someone else who has noticed the same symptoms after the data studio update and who is following this issue.

I have one database connection that doesn't show the tables and another that does.

In both instances I am connecting to my own database (not master) in the connection details.

Running the SQL queries from above gives me these results for the one that doesn't show the tables in the treeview (sandbox):

Running the SQL queries on the connection that works gives me these results (preview):

I am getting no results on the last query when using my sandbox database connection.

Look like the join is failing because the database_id from sys.databases (7) is different from the database_id from sys.database_service_objectives (5).

Not sure why the database_ids are different (it is possible that database has been restored from a backup / snapshot in the past).

This may be a different problem that the others are having, but I thought I would post my results anyway in case it helps you diagnose the issue.

[martinw3]

Interestingly, it appears that my sandbox database has a database_id of 5 when queried in the context of the master database and a database_id of 7 when queried in the context of 'sandbox':

[shueybubbles]

thx @martinw3 ! that discrepancy doesn't seem correct to me. Can you open a support ticket on the Azure portal to get the right folks on the SQL DB engine side of the house to look at it? I will ask around to see if any of my peers can help too.

[martinw3]

Sure, I'll raise a ticket with Azure and ask the question.

Also I did find this on stack exchange that sounds similar: https://dba.stackexchange.com/a/138811

We have tested failover and also changed the pricing tier various times on those Azure SQL instances, so perhaps it is related to that.

[shueybubbles]

thx it never occurred to me that such metadata could be fluid.
Don't bother with the ticket. I'll redo the SMO code to match on name instead of id

[shueybubbles]

@martinw3 can you confirm this query gets the right edition for user databases when run in master as well as in the user database?

select dtb.name, dso.edition from sys.databases dtb left join sys.database_service_objectives dso on dso.database_id = isnull(db_id(dtb.name), dtb.database_id)

[martinw3]

I get this when running the query in my user database:

and get this when running in master:

So it has the right edition for the user database in both (but null for the edition for master when run in the user DB, but maybe that doesn't matter).

[shueybubbles]

thx, we special case master.

[Charles-Gagnon]

The insiders build was just updated with the fix for this issue - if anyone hitting this could install that and verify that'd be great.

https://github.com/Microsoft/azuredatastudio#try-out-the-latest-insiders-build-from-main

Otherwise we currently plan on releasing this to stable tomorrow morning (PDT).

[martinw3]

Hi, I can confirm the insider's build has fixed the issue for me, I am now seeing all the tables again in the tree view. :)

[gregveres]

I can confirm that the insiders build shows me the list of tables again.

[Charles-Gagnon]

Thank you for confirming - the stable update (1.40.1) just went out as well so I'll be closing this. Thanks for the assistance in tracking this down everyone!