SQL view from another Database

Question

I want to create a view in a Database residing in SQL Server Express 2008.

The view will be take its data from table residing on another server SQL Server 2008 on another machine in the same LAN.

My Question is if a must create a Linked Server for this goal and if it is possible to create a linked Server on SQL Express.

Are there any solutions without using a linked Server?

score 1 · Accepted Answer · edited May 23 '17 at 12:33

Yes and Yes and No. See my answer here:
Login failed for user sql

To access table T_Users on database catalog Cor_Basic on server CORDB2005 with user ApertureWebServicesDE (on CORDB2005) and password MY_TOP_SECRET_PASSWORD you would use:

EXEC master.dbo.sp_addlinkedserver 
    @server = N'RemoteDB'
   ,@srvproduct = 'OLE DB Provider for SQL'
   ,@provider = N'SQLNCLI'
   ,@datasrc = 'CORDB2005'
   ,@catalog = 'COR_Basic'

GO

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'RemoteDB'
   ,@useself = false
   --,@locallogin = 'LocalIntegrationUser'
   ,@locallogin = NULL 
   ,@rmtuser = N'ApertureWebServicesDE'
   ,@rmtpassword = N'MY_TOP_SECRET_PASSWORD'
GO




SELECT * 
FROM RemoteDB.COR_Basic.dbo.T_Users

SQL view from another Database

1 Answers1