View on linked server tables - SQL Server Forums

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)

View on linked server tables

Author

Topic

crownclit
Starting Member

20 Posts

Posted - 2007-12-19 : 19:44:57

Hi

Could someone please tell me if it is possible to create view with tables from server1 and tables from linked server2? I have created linked server but have now idea how SQL syntax should look alike.
Any sql sample would be greatly appreciated.

Cheers:

CC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-19 : 20:35:03

Yes that is possible. Just use the four part naming convention for the remote object:

CREATE VIEW View1
AS
SELECT * FROM Table1
UNION ALL
SELECT * FROM Server2.Database1.Owner1.Table1

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

crownclit
Starting Member

20 Posts

Posted - 2007-12-19 : 20:39:40

Thanks, but can you join 2 tables by a common field from server1 and server2

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 20:44:53

Possible.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-19 : 20:59:12

quote:
Originally posted by crownclit

Thanks, but can you join 2 tables by a common field from server1 and server2

CREATE VIEW View1
AS
SELECT *
FROM Table1 t1
INNER JOIN Server2.Database1.Owner1.Table2 t2
ON t1.id = t2.id

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-20 : 09:02:29

it's possible to do this, but the performance will not be great.

better to have both databases on the same server for performance.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources