Sql-server – Database diagram for linked server tables

database-diagramslinked-serversql server

I run Microsoft SQL Server Express 64-bit version 14.0.2027.2 (X64) on Windows 10 Pro 10.

I created a linked server connecting tables from a Microsoft Access (mdb) database to Server Objects/Linked Servers on my SQL Server Express instance. All the linked tables seem to be working well.

I would like to create a Database Diagram for the linked tables. Since I am new to SQL Server, as far as I know ER diagram is possible to make only for the local database.

Is there any option how to make an ER diagram for linked server tables?

If not (when diagram is allowed only for native database/tables), is there any possibility to link tables directly into a native SQL Server database and then create diagram for tables?

Best Answer

There's no way to do this with the native tools within SQL Server Management Studio. You could get a third party tool like ER/Studio, but even that won't automatically model the linked tables. You'll have to enter them manually. External tables just aren't considered to be a part of the fundamental model of a given database. They're external, additional, other.

Related Solutions

Sql-server – Can’t create linked server SQL Server 2005

As one colleague pointed out, there's no SQL Server 2003 version. I suppose you're speaking about MSSQL 2000. Which was somehow related in time with VS 2003 :-).

As you're working with instances on the same server, do you have the service "SQL Server Browser" started and running?

Why you need it:

"The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

Sql-server – “Prelogin failure” when attempting to run stored procedure across linked server

I've seen it happen twice after SQL Server service packs were applied - service packs that took a really long time (hours) to finish the upgrade, or broke as they were trying to apply changes to the system databases.

The fact that you mentioned there was a scheduled restart clues me in, too - that means someone had probably staged updates to happen on restart, and they did - but then they caused an outage.

As a result of this, I'm never a big fan of automatically applying updates (Windows or SQL) on SQL Servers.

Best Answer

Related Solutions

Sql-server – Can’t create linked server SQL Server 2005

Sql-server – “Prelogin failure” when attempting to run stored procedure across linked server

Related Question