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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 8, 2011 at 1:30 pm

  #1404826

  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

  November 10, 2011 at 9:43 am

  #1405887

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

• eric.lyons

  SSC Eights!

  Points: 860

  More actions

  November 11, 2011 at 8:11 am

  #1406296

  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

  November 11, 2011 at 1:50 pm

  #1406586

  @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