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.
*fyi, "Could not find server in sysservers. Execute sp_addlinkedserver to add the server to sysservers. The statement has been terminated."