Linked Server – Why and how use them!

A linked server is used to connect to another (remote) database or file (Xls, CVX) using SQL Server Management Studio (SSMS) and discover the data or objects. You can write SQL queries from your SSMS directly on a database on another machine. In Oracle they call it DBLinks (Database Links).

For example, it is possible to create a view in a database which receives data from a database on another machine, cool right? The other database can be SQL Server, Oracle or another supported database. I use Linked Server to create connections to all my source systems in a datawarehouse/BI environment. If somebody asks me to check a number, I can always check the source data in a couple of seconds by writing a query using linked servers. I will explain you how to add a linked server in SQL server.

Create a Linked Server

Setting up a linked server in SQL Server is not a hard job. You can setup a linked server using a SQL Statement or via the graphical user interface in SSMS. I have setup this linked server in sql server 2008 R2 but it should also work in higher versions of SQL Server.

Linked server sql:

-- Add Linked Server
EXEC master.dbo.sp_addlinkedserver 
@server = N'ServerName'
, @srvproduct=N'ServerName\InstanceName'
, @provider=N'SQLNCLI10'
, @datasrc=N'ServerName\InstanceName'
, @catalog=N'DatabaseName'
 
-- Add Credentials to the Linked Server 
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'ServerName'
,@useself=N'False'
,@locallogin=NULL
,@rmtuser=N'Domain\UserName'
,@rmtpassword='Password'

Linked Server via SSMS Guid:

Expand the “Server Objects” folder in SSMS, Right Click “Linked Servers”-> New Linked Server. Now fill in all the fields. The video below will show you how to create a linked server to a SQL Server Database with an instance. You can click on the image to show a popup with a large video of how to create one!

View Linked Server configurations

In the SSMS, you can open the folder “Linked Servers” to view all linked servers on the SQL Instance. Another option to check for all linked server is the following SQL command:

EXEC sp_linkedservers

View Linked Server Tables

Sometimes it’s very useful to check which tables are in your linked server. This can be handy if you browse an AS400 database (DB2). The system stored procedure sp_tables is used to list out the tables available in the current database of the current server. What if you want to know the same that exist in the linked Server? You can use sp_tables_ex:

EXEC sp_tables_ex 'LinkedServerName'

Query tables via a Linked Server

Now comes the important part. If you finished setting up the linked server, you can query the tables or views from the linked server with the following syntax:

SELECT *
FROM DWH_NL_UAA_PRD_NLD_SQL.uaa.dbo.rightsMatrix

DWH_NL_UAA_PRD_NLD is the name of the Linked Server. uaa is the name of the remote database, dbo the schemaname and rightsMatrix the name of the table.

Conclusion

I hope you have learned how to add a Linked Server in SQL Server. You can use advantaged options when you click on the properties of the linked server and select “Server Options”. You will need this if you want to run a procedure or function from a linked server. If you have any questions, leave a message.

Extra: video to show how to create a linked server