What is the difference between "SQL Server Object Explorer" and "Server Explorer")

Malcolm Fellmann 5

I followed the information:

"Create a dataset for .NET Framework application using the Dataset Designer"

and the Server Explorer shows the "NorthwindDataset.mdf" but with no tables.

and the SQL Server Object Explorer shows the "Northwind" with all the tables created by "Northwind transact-SQL script"

Why is there a difference?

2 answers

Azar 19,400 Reputation points

2024-04-27T15:51:53.1433333+00:00

Hi there Malcolm Fellmann

Thanks for using QandA platform

The SQL Server Object Explorer is specifically designed for managing SQL Server databases.

It provides a hierarchical view of SQL Server instances, databases, tables, views, stored procedures, and other database objects.

You can use it to browse, query, and modify database objects directly within Visual Studio.

The Server Explorer is a more general-purpose tool for exploring various data sources, including databases, services, and other resources.

In your case, it seems there is a difference in the visibility of database objects between the two explorers. The "NorthwindDataset.mdf" file may be associated with a local database file (.mdf), which is accessible through the Server Explorer but may not reflect the tables created by the "Northwind transact-SQL script." On the other hand, the SQL Server Object Explorer directly connects to the SQL Server instance and displays the database objects created by the script.

If this helps kindly accept the answer thanks much.
Please sign in to rate this answer.
Erland Sommarskog 101.7K Reputation points MVP

2024-04-27T17:44:35.6033333+00:00

In addition to Azar's answer: I see that when I use the Server Explorer, I can add a data connection and I get these choices:

It seems that you have chosen Microsoft SQL Server Database File. I have to say that it is completely beyond me how a database file could be a data source. To be able to read and write to a database, you need an SQL Server instance. You have zero use for the database file on its own.

Just to give you some context: I consider myself to be an SQL Server expert, but most parts of Visual Studio makes my head spin. But, no, that choice makes no sense. Stay away from it to avoid further confusion.

And Azar is right: Use SQL Server Object Explorer to explore the database.

Malcolm Fellmann 5 Reputation points

2024-04-28T09:53:56.9066667+00:00

Thank you for your reply Azar.

I understand your explanation concerning the two types of Server views, but it does not explain why, by using the learn guide offered by Microsoft, and following step by step, I end up with two different views.

After looking in detail at the offered "Northwind transact-SQL script":

EXECUTE (N'CREATE DATABASE Northwind

ON PRIMARY (NAME = N''Northwind'', FILENAME = N''' + @device_directory + N'northwnd.mdf'')

LOG ON (NAME = N''Northwind_log'', FILENAME = N''' + @device_directory + N'northwnd.ldf'')')

I have these 2 questions:

To me it looks like the database is being created locally, so where do I find the settings for the @device_directory?

Where is the SQL Server Object Explorer looking for the database?

If there are two different databases on my pc with the same name, that would explain the differences.

Erland Sommarskog 101.7K Reputation points MVP

2024-04-28T10:01:30.91+00:00

I understand your explanation concerning the two types of Server views, but it does not explain why, by using the learn guide offered by Microsoft, and following step by step, I end up with two different views.

Do you have a link to that guide?

To me it looks like the database is being created locally, so where do I find the settings for the @device_directory?

That variable is set earlier in the script. If memory serves, it looks at the location for the master database and puts Northwind in the same folder. (Which is not really the best solution. It should read the default locations for databases and log files from the registry. But the script is very old.)

Where is the SQL Server Object Explorer looking for the database?

In SQL Server. More precisely, it connects to SQL Server and runs queries to see which databases there are. Note here that the SQL Server instance does not have to be on your local computer. You could connect to an SQL Server instance on a different machine in your network. As a matter of fact, this is often the case if you are in a corporate environment.

Malcolm Fellmann 5 Reputation points

2024-04-28T10:02:52.4033333+00:00

Hello Erland Sommarskog,

As I followed the learn guide offered by Microsoft and reached the step to add a data connection, I was offered "Microsoft SQL Server Database File (SqlClient)" as the default Data Source. Not wanting to mess up the instructions I accepted that Data source and continued on

Malcolm Fellmann 5 Reputation points

2024-04-28T10:07:19.0933333+00:00

Hello again Erland, here is the link to the leanr guide:

https://learn.microsoft.com/en-us/visualstudio/data-tools/walkthrough-creating-a-dataset-with-the-dataset-designer?view=vs-2022

I have been using SQL for over 20 years but to be honest, the way Visual Studio guides me to set it up, is confusing me more with every answer I am getting here

Erland Sommarskog 101.7K Reputation points MVP

2024-04-28T12:12:07.4033333+00:00

I have been using SQL for over 20 years but to be honest, the way Visual Studio guides me to set it up, is confusing me more with every answer I am getting here

I guess we are two then. :-)

I looked at that link, but I could not find that it gave me any instructions on how to add the database. It just says:

On the View menu, click Server Explorer.

In Server Explorer, click the Connect to Database button.

Create a connection to the Northwind sample database.

But it gives no instruction on how to work with that Connect to Database dialog, and which defaults to Microsoft Access Database File.

If you have created the database in your localDB instance, the option you would use is Microsoft SQL Server. When would use Microsoft SQL Server Database File? Maybe if you have a copy of a database from somewhere else and want to attach it to your localDB instance. But the normal way to copy databases around is by BACKUP/RESTORE, not by copying .mdf files.

Is it correct that if I delete the one in my "C:\Users\Mac\Documents" and work only with my LocalDB via the SQL Server Object Explorer this will solve my problems?

That file may be more helpful than confusing, so, yes, you may as well delete it.
Sign in to comment
MikeyQiao-MSFT 560 Reputation points Microsoft Vendor

2024-04-28T02:31:52.3466667+00:00

Hi，Malcolm Fellmann

Server Explorer and SQL Server Object Explorer refer to this.

The .mdf file in Server Explorer is specifically meant to serve your local project. In your case, this .mdf file should contain the schema you defined in the Dataset Designer, such as tables, columns, relationships, etc.

You need to access its content through Microsoft SQL Server Database File (SqlClient). It will not contain any actual data unless you manually add data or import data. This file is created to allow you to build and test data access logic within your local environment.

The .mdf file here differs significantly from the traditional .mdf file found in SQL Server, and I believe that the naming convention used by Visual Studio can be quite ambiguous. I suggest it might be better to consider it as a .vmdf file.

Best regards,

Mikey Qiao

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.
Please sign in to rate this answer.
Erland Sommarskog 101.7K Reputation points MVP

2024-04-28T10:03:27.1233333+00:00

You need to access its content through Microsoft SQL Server Database File (SqlClient). ... The .mdf file here differs significantly from the traditional .mdf file found in SQL Server,

Really? If access it through SqlClient, this means that you are accessing it through SQL Server. Which it its turn suggests that it is perfectly regular .mdf file. But, as I said, Visual Studio, mainly leaves me in a maze, so I may be missing something.

MikeyQiao-MSFT 560 Reputation points Microsoft Vendor

2024-04-28T10:10:55.02+00:00

To my known, linked databases generally do not directly operate on MDF files. The MDF file mentioned in this case is likely a local database provided by Visual Studio, intended for use with datasets. This database does indeed exist, but it is definitely not very useful.

Malcolm Fellmann 5 Reputation points

2024-04-28T10:24:26.8766667+00:00

Although I mentioned I have been using SQL for over 20 years, the actual SQL Server and connection details were set up and maintained from another Department.

I did a search and found the database created by the script with @device_directory is in my "C:\Users\Mac\Documents" (Mac is my nickname not my pc type)

The other location for that Database is:

"C:\Users\Mac\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB"

Is it correct that if I delete the one in my "C:\Users\Mac\Documents" and work only with my LocalDB via the SQL Server Object Explorer this will solve my problems?
Sign in to comment