Cross-server queries

Create linked server.sql

/*
- Replace OtherServerName with a real server name
*/
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'OtherServerName', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'OtherServerName', @locallogin = NULL , @useself = N'True'
GO

Cross server query - collation.sql

/**
- Note server and database references
- Note COLLATE keyword is used to normalize string collation and allow equality between the databases
- If there is a collation mismatch, then you may receive an error message similar to this: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS" in the equal to operation.
*/
SELECT sourceCustomer.[No_] as [Customer No_]
	, sourceCustomer.Name as [Name in Source Database]
	, targetCustomer.Name as [Name in Current Database]
	, *
FROM [OtherServerName].[OtherDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] sourceCustomer
INNER JOIN [MyDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] targetCustomer
    ON sourceCustomer.[No_] COLLATE Latin1_General_CI_AS = targetCustomer.[No_] COLLATE Latin1_General_CI_AS

Cross server query - join.sql

/**
- Note server and database references
*/
SELECT sourceCustomer.[No_] as [Customer No_]
	, sourceCustomer.Name as [Name in Source Database]
	, targetCustomer.Name as [Name in Current Database]
	, *
FROM [OtherServerName].[OtherDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] sourceCustomer
INNER JOIN [MyDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] targetCustomer
    ON sourceCustomer.[No_] = targetCustomer.[No_]

Cross server query - update from join.sql

/**
- Note server and database references
*/
UPDATE targetCustomer
SET Name = sourceCustomer.Name
FROM [OtherServerName].[OtherDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] sourceCustomer
INNER JOIN [MyDatabaseName].dbo.[CRONUS Australia Pty_ Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] targetCustomer
    ON sourceCustomer.[No_] = targetCustomer.[No_] 
WHERE sourceCustomer.Name <> targetCustomer.Name