[MSSQL] 링크드 서버

----------------------------------------------------------------------------------------------------------------------------------

/****** 개체:  LinkedServer [WWTMES]    스크립트 날짜: 04/19/2016 12:06:22 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'TASIC', @srvproduct=N'mssql', @provider=N'SQLNCLI', @datasrc=N'192.168.12.10'

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

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TASIC',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='ictassvr'



GO

EXEC master.dbo.sp_serveroption @server=N'TASIC', @optname=N'collation compatible', @optvalue=N'true'

GO

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

GO

EXEC master.dbo.sp_serveroption @server=N'TASIC', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TASIC', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TASIC', @optname=N'rpc', @optvalue=N'true'

GO

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

GO

EXEC master.dbo.sp_serveroption @server=N'TASIC', @optname=N'sub', @optvalue=N'false'

GO

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

GO

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

GO

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

GO

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

GO

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

----------------------------------------------------------------------------------------------------------------------------------

/****** 개체:  LinkedServer [WWTMES]    스크립트 날짜: 04/19/2016 12:06:22 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'TASYC', @srvproduct=N'mssql', @provider=N'SQLNCLI', @datasrc=N'192.168.180.55'

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

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TASYC',@useself=N'False',@locallogin=NULL,@rmtuser=N'tasadmin',@rmtpassword='yctassvr'



GO

EXEC master.dbo.sp_serveroption @server=N'TASYC', @optname=N'collation compatible', @optvalue=N'true'

GO

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

GO

EXEC master.dbo.sp_serveroption @server=N'TASYC', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TASYC', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TASYC', @optname=N'rpc', @optvalue=N'true'

GO

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

GO

EXEC master.dbo.sp_serveroption @server=N'TASYC', @optname=N'sub', @optvalue=N'false'

GO

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

GO

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

GO

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

GO

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

GO

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

----------------------------------------------------------------------------------------------------------------------------------

/****** 개체:  LinkedServer [WWTMES]    스크립트 날짜: 04/19/2016 12:06:22 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'TASGS', @srvproduct=N'mssql', @provider=N'SQLNCLI', @datasrc=N'192.168.238.12'

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

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TASGS',@useself=N'False',@locallogin=NULL,@rmtuser=N'tasadmin',@rmtpassword='gstassvr'



GO

EXEC master.dbo.sp_serveroption @server=N'TASGS', @optname=N'collation compatible', @optvalue=N'true'

GO

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

GO

EXEC master.dbo.sp_serveroption @server=N'TASGS', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TASGS', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'TASGS', @optname=N'rpc', @optvalue=N'true'

GO

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

GO

EXEC master.dbo.sp_serveroption @server=N'TASGS', @optname=N'sub', @optvalue=N'false'

GO

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

GO

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

GO

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

GO

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

GO

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





-- 서버삭제

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'TASYC')EXEC master.dbo.sp_dropserver @server=N'TASYC', @droplogins='droplogins'



-- 서버정보확인

select * from sys.servers	



select COUNT(*) from TASIC.TASDBIC.dbo.tblTransportation	-- 44884

select COUNT(*) from TASYC.TASDBYC.dbo.tblTransportation	-- 44892

select COUNT(*) from TASGS.TASDBGS.dbo.tblTransportation	-- 44954

 

 SELECT top 1 * FROM WWTMES.CJSUGAR.dbo.TB_CJ1_WTCOUNT

select tpic.VehiNo as VehiNo_IC, tpic.CustomerCode as CustomerCode_IC,

tpyc.VehiNo as VehiNo_YC, tpyc.CustomerCode as CustomerCode_YC



 from TASIC.TASDBIC.dbo.tblTransportation tpic

full join TASYC.TASDBYC.dbo.tblTransportation tpyc

on tpic.VehiNo = tpyc.VehiNo

and tpic.CustomerCode = tpyc.CustomerCode

order by tpic.VehiNo

 



select top 10 * from TASYC.TASDBYC.dbo.tblTransportation

select top 10 * from TASGS.TASDBGS.dbo.tblTransportation