joining a view and table of different 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

General SQL Server Forums

New to SQL Server Programming

joining a view and table of different servers

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-01-16 : 11:28:49

I am trying to join a view of one server database with a table of another server database where both has common column as callerid.

could anyone please help with it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-16 : 11:43:07

There are lost of ways to do this. What you are probably looking for is a lnked server: [url]http://msdn.microsoft.com/en-us/library/ms188279.aspx[/url]

If the tables are of any signifigant size, this could cause serious performance issues. So, just be aware of that.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-16 : 11:48:35

what is your problem?

SELECT * FROM
server.db.owner.table1 = server.db.owner.table2
ON key=key

HOWEVER

It will probably drag all the data across..maybe not if it's a simple join

Use Profiler to see what's going on

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 11:52:57

if its for a one off activity you can also use distributed query using OPENROWSET

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gagani
Posting Yak Master

112 Posts

Posted - 2012-01-16 : 11:53:57

I am trying to write a select statement which picks up some columns of a view of one server and some columns of a table of another server

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:13:09

quote:
Originally posted by gagani

I am trying to write a select statement which picks up some columns of a view of one server and some columns of a table of another server

it just needs a simple join among them on related columns. for accessing other server view/table you need to add linked server connection as Lamprey suggested and use server.dbname.dbo.object or use OPENROWSET


...
as OPENROWSET('SQLOLEDB',
 'servername';'user';'password',
 'your query')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources