scripting a join between db's on different servers/same domain

Question

scripting a join between db's on different servers/same domain

wjhamel

Old Hand

Points: 347
More actions
November 8, 2011 at 1:26 pm

#262517

how would i script a join (or anything else for that matter) between two tables/databases on different servers on the same domain?
For instance, i want to compare the Name column on Civil.dbo.subjects on the server JHAMEL to the same db and table on the server AGENCY-DATA.
Thanks
James

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Breakwaterpc (11/8/2011)
how would i script a join (or anything else for that matter) between two tables/databases on different servers on the same domain?
For instance, i want to compare the Name column on Civil.dbo.subjects on the server JHAMEL to the same db and table on the server AGENCY-DATA.
Thanks
James

after you've created linked servers for the remote machines, you can join the tables together;

SELECT

S1.*,

S2.*

FROM JHAMEL.Civil.dbo.subjects S1

LEFT OUTER JOIN [AGENCY-DATA].Civil.dbo.subjects S2

ON S1.ID = S2.ID

WHERE S1.SomeColumn = 42

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

applebyte Ten Centuries Points: 1028 More actions · Answer 2

Wouldn't you have to use OPENQUERY if the servers use NTLM authorization?

eric.lyons SSC Eights! Points: 860 More actions · Answer 3

In order to query two databases, you set up a linked server under 'server objects'

The code is below:

/****** Object: LinkedServer [MyDB\MyInstance] Script Date: 11/11/2011 09:07:21 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'MyDB\MyInstance', @srvproduct=N'SQL Server'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyDB\MyInstance',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'rpc', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'rpc out', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MyDB\MyInstance', @optname=N'use remote collation', @optvalue=N'true'

*****

Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

@Breakwaterpc

Wouldn't you have to use OPENQUERY if the servers use NTLM authorization?

No, you wouldn't have to. However if you really wanted to you could use OPENQUERY but becomes kinda tricky...much easier to do it the way Lowell mentioned. Cleaner.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience