Ok, lets go step by step. We're going to use Transact SQL language to do this, so go to the Query Analyzer and login to the Local Server (ServerA).
- First Link the ServerB to ServerA:
EXEC sp_addlinkedserver 'ServerB', 'SQL Server'
- Then establish the user that we'll use to connect from ServerA to ServerB. For that task we'll use a valid account in ServerA (with the appropriate permissions) and map it to a valid account in ServerB. That account must have permission to access the source table for the view in ServerA:
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ServerB', @useself = 'false', @locallogin = 'YourLocalUserFromA', @rmtuser = 'YourRemoteUserFromB', @rmtpassword = 'PasswordOf_rmtuser'
- Log in to ServerA with the account that we mapped to ServerB and create the view:
CREATE VIEW dbo.MyView AS SELECT Field1, Field2, FieldN FROM ServerB.RemoteDB.dbo.RemoteTable GO
- Start using it, and good luck:
SELECT * FROM MyView
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Microsoft SQL Server Installation
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.