SQL Server 2008 : Working with Multiple-Source Queries - Using Four-Part Database Names & The DTC Explained

Using Four-Part Database Names

If the data is in another SQL server, complete the four-part name that is required to access data from the remote SQL server. Server.Database.Schema.ObjectNameSELECT or DML query. For SELECT, here is the query. is the four-part name. The four-part name may be used in the

SELECT * FROM [<server_name,sysname,(local)><instance_name,sysname,\
SQLEXPRESS>].<database_name,sysname,pubs>.<schema_name,sysname,dbo>.
<table_name,sysname,authors>
GO
Example: SELECT Name, Address FROM RMTSERVER.AdventureWorks.Person.Address

In case of other database types, often you won’t find the schema name. For example, in the MS Access example, you won’t see a database and schema with a match to the SQL server. In those cases you can avoid them by notation as given in the following example.

SELECT * FROM CUSTOMERACCESS...Customer

The DTC Explained

SQL Server uses the Distributed Transaction Coordinator (DTC) to handle multiple-server transactions, commits, and rollbacks. The DTC server uses a two-phase commit schema for multiple-server transactions. Here are the steps.

1. Server is sent a prepare to commit message.

2. Server performs the first phase of the commit, verifying that it is capable of committing the transaction.

3. Server replies when it has finished preparing for the commit.

4. After server has responded positively to the prepare to commit message, the actual commit message is sent to each server.

5. DTC is required only when remote updates are occurring in the remote server.

Tip

There are three catalog views related to Linked Servers: sys.linked_logins, sys.servers, and sys.remote_logins. The most frequently used catalog view is sys.servers.

  SELECT * from sys.servers
  WHERE is_linked = 1

The preceding query will return the list of linked servers attached to a SQL server.