Hüseyin Çelik Blog Sayfasi

.Net, Java, Android, Progress 4gl Paylasimlarim

SQL Server da Linked Server

SQL Server üzerinden diger verit abani sunucularina baglanmanizi gerektiren durumlarla karsilasabilirsiniz. Benim siklikla karsilastigim bir durumdur.

Genellikle Uyumsoft üzerine çalistigimdan benim Progress veri tabani baglantisi yapmam gerekiyordu. Bunun disinda Oracle, Informix postgresql vb. veri tabanlarina baglanip entegrasyon yapmam gerekiyordu.

Iste bu gibi durumlarda link server çok isimize yarayacak bir özelliktir. Ben örnek olarak progress ve Sql veri tabani ile ilgili bilgileri verecegim.

Iste bu gibi durumlarda çok isimize yarayacak bir özellik link server. Ben örnek olarak progress ve Sql server bilgileri verecegim.

EXEC master.dbo.sp_addlinkedserver @server = N'PROGRESSDB', @srvproduct=N'Progress OpenEdge', @provider=N'MSDASQL', @provstr=N'Driver={Progress OpenEdge 10.1B driver};HOST=192.168.0.10;PORT=1500;DB=uyum2016;UID=sysprogress;PWD=1;DefaultIsolationLevel=READ UNCOMMITTED;'
 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PROGRESSDB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PROGRESSDB',@useself=N'False',@locallogin=N'sa',@rmtuser=N'sysprogress',@rmtpassword='1'

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

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

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

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

EXEC master.dbo.sp_serveroption @server=N'PROGRESSDB', @optname=N'rpc', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'PROGRESSDB', @optname=N'rpc out', @optvalue=N'false'

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

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

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

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

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

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

EXEC master.dbo.sp_serveroption @server=N'PROGRESSDB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
EXEC master.dbo.sp_addlinkedserver @server = N'UYUMDB', @srvproduct=N'Progress OpenEdge', @provider=N'MSDASQL', @provstr=N'Driver={Progress OpenEdge 10.2A driver};HOST=192.168.0.10;PORT=4002;DB=uyum2005;UID=sysprogress;PWD=1;DefaultIsolationLevel=READ UNCOMMITTED;POOLING=1'

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

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'UYUMDB',@useself=N'False',@locallogin=N'sa',@rmtuser=N'sysprogress',@rmtpassword='1'

 Link server üzerinden sorgu çalistirmak için;

SELECT * FROM OPENQUERY(UYUMDB, 'select cast(s.rowid as int) as "MalzemeId", s.stok_kod as "MalzemeKod", 
s.stok_ad as "MalzemeAd", s.stok_ad2 as "MalzemeAd2", cast(b.rowid as int) as "BirimId", 
s.birim as "Birim", s.kdv_oran as "KdvOran", s.min_stok as "MinStok", s.max_stok as "MaxStok", 
s.fazla_sipyuzde as "FazlaSipMiktar", s.int_sira as "HammaddeTakip", s.kkontrol AS "KaliteKontrol", 
s.urun_tip AS "TipKod", case s.urun_tip when  ''Mamul'' then 3 when ''Y.Mamul'' then 2 when ''Hammadde'' then 1 else 0 end as Tip
from pub.stok_kart s, pub.birim b where s.firma_kod = ''TEST'' and b.birim = s.birim and b.firma_kod = s.firma_kod')

Sql Server dan bir baska Sql Server baglantisi için;

EXEC sp_addlinkedserver
   @server = 'TEST',
   @srvproduct = '',
   @provider = 'SQLNCLI10',
   @datasrc = '192.168.0.10',
   @provstr = 'SERVER=192.168.0.10;UID=sa;PWD=1;'
   
   
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST',
@useself='False',
@locallogin=NULL,
@rmtuser='sa',
@rmtpassword='1'

 

select top 1 * from LINKED_SQL.ELP.elp.ZMM_BARI01 (nolock)

SELECT * FROM OPENQUERY(XELP, 'select top 1 * from elp.ZMM_BARI01')



Loading