Remote Server MSSQL

I have just had occasion to want to access data from two MSSQL databases. One is on my workstation. The other is out there in cyberspace someplace. I used MS SQL Server Management Studio Express. Here is how I did it:

First, I created two new scripts, CreateServer.sql and ProcessData.sql. Each of these was created with a connection to my local instance.

Then, I created a named reference to the remote database. In their parlance, it is a server and the reference is in sys.server (as I found out from one of the many error messages* I got along the way). This required EXEC'ing two commands, both placed in the CreateServer file:

EXEC    sp_addlinkedserver    @server='LOCALSERVERNAME', @srvproduct='', @provider='SQLOLEDB', @datasrc='999.999.999.999'

EXEC sp_addlinkedsrvlogin @rmtsrvname=LOCALSERVERNAME, @useself=FALSE, @rmtuser='REMOTEUSERNAME', @rmtpassword='remotepassword'

Then I could access them using straightforward syntax (in the ProcessData file):


Obviously, DATABASENAME and TABLENAME must represent things that exist in the remote database. Less obviously, "dbo" must be exactly the way it is shown.

Microsoft explains some of the particulars: sp_addlinkedserver and sp_addlinkedsrvlogin.

*fyi, "Could not find server in sysservers. Execute sp_addlinkedserver to add the server to sysservers. The statement has been terminated."