Linked Servers

As you know by now, I write quite a bit of SQL for mining data from the Enterprise database. Occasionally, I'll need some data from another database on a different SQL Server to pair with some information from Elite. In SQL Server, you can create a linked server to be able to join across SQL Servers. For example, we use Minisoft's ARCS accounts receivable program, and I've created a linked server to join ARCS notes to real-time Elite data.

• Create a Linked Server to the other SQL Server

• USE master
  GO
  EXEC master.dbo.sp_addlinkedserver
  @server = N'ARCS'
  , @srvproduct=N''
  , @provider=N'SQLOLEDB'
  , @datasrc=N'ARCS-SERVERNAME'
  , @catalog=N'ARCS-DBNAME'
  GO
  

• Add login credentials for the other SQL Server (if you don't use trusted login)

• EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'ARCS'
  , @locallogin = NULL 
  , @useself = N'False'
  , @rmtuser = N'arcs-username'
  , @rmtpassword = N'arcs-password'
  GO
  

• Write Query to access ARCS from the Elite database

• SELECT TOP 100 *
  FROM ARCS.ARCS-DBNAME.dbo.ARCS_HOLDS
  

After setting up the first one of these, I've already contemplated a hundred such uses. It's definitely a handy tool.