Trying to create a view across two SQL Servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trying to create a view across two SQL Servers

Author

Topic

salthead
Starting Member

8 Posts

Posted - 2008-05-20 : 16:32:56

I have two machines running SQL server. I need to create a view that contains data from both servers on Server 2.

ex:
Server 1 contains:
database (Employees)
Table (EmpMaster)
Column (EmpNumber)
column (EmpName)

Server 2 contains:
database (Training)
Table (TrainingEntry)
Column (EmpNumber)
Column (TrainingDate)
Column (TrainingDescription)

If the two tables were on the same server and in the same database, I'd just write the following:

select
TrainingEntry.EmpNumber
,Employee.EmployeeName
,TrainingEntry.TrainingDate
,TrainingEntry.TrainingDescription
from TrainingEntry
Left Outer Join Employee on TrainingEntry.EmpNumber =
Employee.EmpNumber

The question is how do I handle going between two databases, and more importantly, how do I handle linking between two servers?

All examples are appreciated.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-20 : 16:36:19

Create a linked server and then use the four-part naming convention for the remote object. Check Books Online for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-20 : 16:37:25

Create a linked server in server 2 for server 1. Check booksonline

salthead
Starting Member

8 Posts

Posted - 2008-05-20 : 17:17:04

I cannot find examples of this on Books Online. Either a link or an actual example would be appreciated.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-20 : 17:26:24

You can do it from the GUI (which is easiest) or from T-SQL via sp_addlinkedserver. BOL article for sp_addlinkedserver: http://msdn.microsoft.com/en-us/library/ms190479.aspx

Four-part naming convention example:

SELECT * FROM LinkedServerName.DatabaseName.ObjectOwner.TableName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

salthead
Starting Member

8 Posts

Posted - 2008-05-20 : 18:05:00

Thanks for the link Tara. I'll give it a read. I'm not sure how to do it in Management Studio, but I'll look into that as well.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-20 : 18:31:47

If you have permissions, you can expand server objects-linked server and configure it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources