Monday, February 22, 2010

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
    EXEC master.dbo.sp_addlinkedserver
    @server = N'ARCS'
    , @srvproduct=N''
    , @provider=N'SQLOLEDB'
    , @datasrc=N'ARCS-SERVERNAME'
    , @catalog=N'ARCS-DBNAME'
  • 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'
  • Write Query to access ARCS from the Elite database
  • SELECT TOP 100 *
After setting up the first one of these, I've already contemplated a hundred such uses. It's definitely a handy tool.

No comments:

Post a Comment