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):
SELECT field1, field2 FROM LOCALSERVERNAME.DATABASENAME.dbo.TABLENAME
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."