Database on External Machine - 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

General SQL Server Forums

New to SQL Server Programming

Database on External Machine

Author

Topic

rockster8
Starting Member

7 Posts

Posted - 2007-05-11 : 13:39:45

Hi,
I have two servers, namely ServerA and ServerB. These two servers are on the same subnet. ServerB has a database called DatabaseB.
I'm logged in in ServerA and trying to pull data from DatabaseB.

This is the query I do when I'm logged in in ServerB (note im using Microsoft SQL Server Management Studio):

USE DatabaseB

SELECT distinct b.Subject
FROM dbo.F_Class_Exam a
LEFT OUTER JOIN dbo.D_Course_Catalog b
ON a.Course_ID = b.Course_ID
WHERE a.Term = '2071' AND a.Class_Exam_Type = 'FIN' AND b.Term = '2071'
ORDER BY b.Subject

How would I do this same query on ServerA? Ideas? Thoughts?

Thanks,
Mikhial

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-11 : 13:44:45

If you can access serverB from A, you would need to use the four part naming standard.


SELECT distinct b.Subject
FROM ServerB.dbo.F_Class_Exam a
LEFT OUTER JOIN ServerB.dbo.D_Course_Catalog b
ON a.Course_ID = b.Course_ID
WHERE a.Term = '2071' AND a.Class_Exam_Type = 'FIN' AND b.Term = '2071'
ORDER BY b.Subject

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rockster8
Starting Member

7 Posts

Posted - 2007-05-11 : 14:31:39

Hi Dinakar,
Thank you for the reply. One question: How will it know it is using DatabaseB?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 14:34:13

he forgot the 4th part

ServerB.DatabaseB.dbo.F_Class_Exam

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-11 : 14:56:20

oops..thanks Mladen..

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-11 : 15:09:45

You'll need to setup a linked server on ServerA that points to ServerB to do this.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rockster8
Starting Member

7 Posts

Posted - 2007-05-11 : 15:56:57

thanks guys,
tkizer, what do you mean? how do i link two servers?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-11 : 16:10:50

In SSMS, connect to ServerA, then go to Server Objects. Right click on Linked Servers, select new linked server. Type the name you wish to use to reference ServerB. Typically you use the same exact name of the SQL Server. For server type, select SQL Server. I always go to the security tab and select the option to use the login's current security context.

Now you've got a linked server setup, so you can now use the four part naming convention to get to remote objects.

For more information on linked servers, check out SQL Server Books Online as it is discussed in good detail in there.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources