Etiketler

, , , , , , , , ,


SQL Server İle Farklı Veri Kaynaklarından Sorgulama Yapmak | OPENROWSET | OPENDATASOURCE | OPENQUERY

SQL Server Management Studio üzerinden farklı  veri kaynaklarından da sorgulama yapabilirsiniz. Her ne kadar bu iş daha çok veri aktarımı ( daha çok “bulk insert”) için tercih edilse de, T-SQL’in nimetlerini kullanarak veri üzerinde hızlıca sorgular yazabilirsiniz.

Bu amaçla OPENROWSET fonsiyonu ile OLE DB veri kaynağı üzerinden uzaktaki bir veriye erişebilirsiniz.

Yukarıda açıklamasını da göreceğiniz üzere OPENROWSET  table  döndüren bir sistem fonsiyondur.

OpenRowSetFunction - System Functions

Bu metot Ad-Hoc bağlantı kurma ve veriye tek seferlik erişmeyi sağlar. Başka bir SQL DB deki tablodan, herhangibir .mdb (Access), .txt ya da bir .xls (Excel) … dosyasına bağlanabilirsiniz. Aşağıda herhangi bir .xls dosyasına nasıl bağlanacağınıza dair sorgu örneğini görebilirsiniz.

 SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:\ImportData.xls’,
[Sheet1$])

Yukarıda da belirttiğimiz üzere, OPENROWSET ad-hoc bir bağlantı kuracaktır. Bu sebeple veritabanınızın ayarlarının buna müsaade ediyor olması gerekir. Yukarıdaki sorguyu çalıştırdığınızda, aşağıdaki hatayı alıyorsanız,

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

hata açıklamasında da belirttiği “Surface Area Configuration” ayarlarından “Ad Hoc Distributed Queries” ayarını aktif hale getirmeniz gerekmektedir. Bunun için öncelikle

sp_configure ‘show advanced options’, 1
reconfigure

komutunu çalıştırarak “Advanced Options” konfigurasyonunu açmanız gerekmektedir.

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Eğer açmadan

sp_configure ‘Ad Hoc Distributed Queries’, 1

“Ad Hoc Distributed Queries” konfigurasyonunu aktif hale getirmek isterseniz,

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option ‘Ad Hoc Distributed Queries’ does not exist, or it may be an advanced option.

hatasını alırsınız. Açtıktan sonra çalıştırıp, reconfigure ettikten sonra OPENROWSET ile sorgularınızı çalıştırabilirsiniz.

Aşadağıda gördüğünüz .xls dosyasına,

Microsoft Excel - ImportData - OpenRowSet

 SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:\ImportData.xls’,
[Sheet1$])

Microsoft SQL Server Management Studio_ImportData - OPENROWSET

Hatta OPENROWSET size direk tablo döndüreceği için, T-SQL ile istediğiniz gibi veriyi  filitreleyebilirsiniz.

SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:\ImportData.xls’,
[Sheet1$])
WHERE Sutun2 = ‘A’

Ya da veriyi filitrelenmiş bir şekilde kaynağınızdan çekebilirsiniz.

SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:\ImportData.xls’,
‘SELECT * FROM [Sheet1$] WHERE Sutun2 = ”A”‘)

Dönen verilerde başka işlemler de yapmak istiyorsanız, seçtiğiniz sorguyu bir sub-query gibi kullanıp ya da herhangi bir tablo ile joinleyip…  dilediğiniz işlemleri yapabilirsiniz.

Kullanımına ilişkin detaylı bilgi için: http://msdn.microsoft.com/en-us/library/ms190312.aspx

OPENROWSET fonsiyonuna alternatif olarak OPENDATASOURCE fonsiyonunu da kullanabilirsiniz.

OPENDATASOURCE da OPENROWSET gibi ad-hoc bir bağlantı kurarark, SQL Server üzerinden sorgulama yapmanıza yarar.

SELECT *
FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’,
‘Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0’)…[Sheet1$]

OPENDATASOURCE

Kullanımına ilişkin detaylı bilgi için: http://msdn.microsoft.com/en-us/library/ms179856.aspx

Bu iki fonksiyonun en büyük dezavantajı her seferinde tüm bağlantı bilgilerinin açık bir şekilde geçiliyor olması gerekmektedir. LinkedServer fonksiyonalitesinden yararlanamazlar. Eğer LinkedServer fonksiyonalitesini kullanarak, biraz daha yönetilebilir bir şekilde kullanmak isterseniz OPENQUERY fonksiyonunu tercih etmelisiniz.

OPENQUERY için ilk şart Link-Server tanımın yapılmasıdır. Bu tanımı sp_addlinkedserver ile aşağıdaki gibi gerçekleştirebiliriz.

EXEC sp_addlinkedserver ‘LinkServerImportData’,
‘Jet 4.0’, ‘Microsoft.Jet.OLEDB.4.0’,
‘C:\ImportData.xls’,
NULL,
‘Excel 8.0’
GO

LinkServerImportData isminde bir Link-Server tanımladıktan sonra OPENQUERY fonksiyonunu aşağıdaki gibi kullanabiliriz.

SELECT *
FROM OPENQUERY(LinkServerImportData, ‘SELECT * FROM [Sheet1$]’)

filitrelerinizi de direk OPENQUERY içindeki Select cümlesi içinde yazabilirsiniz.

SELECT *
FROM OPENQUERY(LinkServerImportData,
‘SELECT * FROM [Sheet1$] WHERE Sutun1 > ”103”’)

Microsoft SQL Server Management Studio_OPENQUERY

Tabi burada önemli bir noktada veri tiplerinin uyumluluğudur. Excell üzerinde text olarak tanımlı bir sutun üzerinden, her ne kadar içersindeki tüm veriler sayı olsa da sayıymış gibi sorgulama yapamazsınız.

SELECT *
FROM OPENQUERY(LinkServerImportData3,
‘SELECT * FROM [Sheet1$] WHERE Sutun1 > 103‘)

Yukarıdaki sorguyu çalıştırabilmek için öncelikle Sutun1’in veri tipini sayıya çevirmeniz gerekir.

Ancak o zaman sorgunuzu başarılı bir şekilde çalıştırabilirsiniz.

Kullanımına ilişkin detaylı bilgi için: http://msdn.microsoft.com/en-us/library/ms188427.aspx

Test için kullandığım .xls dosyasını (ImportData.xls) buradan indirebilirsiniz.

Bu konu ile ilgili olarak aşağıdaki yazıları okumanızı öneririm.

Reklam