Özet
Microsoft SQL Server, diğer OLE DB veri kaynaklarına olan bağlantıları kalıcı veya geçici olarak destekler. Kalıcı bağlantı bağlantılı sunucu olarak bilinir; tek bir sorgu nun iyiliği için yapılan geçici bir bağlantı dağıtılmış sorgu olarak bilinir. Microsoft Excel çalışma kitapları, SQL Server üzerinden bu şekilde sorgulayabileceğiniz bir OLE DB veri kaynağıdır. Bu makalede, bir Excel veri kaynağını bağlı sunucu olarak yapılandırmak için gereken sözdiziminin yanı sıra, Excel veri kaynağını sorgulayan dağıtılmış bir sorguyu kullanmak için gerekli sözdizimi açıklanmaktadır.
Ek Bilgi
Bağlantılı bir sunucuda Excel veri kaynağını sorgulama
Bir Excel veri kaynağını SQL Server bağlantılı sunucu olarak yapılandırmak için SQL Server Management Studio veya Enterprise Manager,sistem depolanan yordamı, SQL-DMO (Distributed Management Objects) veya SMO (SQL Server Management Objects) kullanabilirsiniz. (SMO yalnızca Microsoft SQL Server 2005 için kullanılabilir.) Tüm bu durumlarda, her zaman aşağıdaki dört özelliği ayarlamanız gerekir:
-
Bağlantılı sunucu için kullanmak istediğiniz ad.
-
Bağlantı için kullanılacak Olan OLE DB Sağlayıcısı.
-
Excel çalışma kitabının veri kaynağı veya tam yolu ve dosya adı.
-
Hedefi Excel çalışma kitabı olarak tanımlayan sağlayıcı dizesi. Varsayılan olarak, Jet Sağlayıcı bir Access veritabanı bekler.
Sistem depolanan yordam sp_addlinkedserver aynı zamanda herhangi bir dize değeri olabilir @srvproduct özelliği, bekliyor. Not SQL Server 2005 kullanıyorsanız, SQL Server Management Studio'daki Ürün adı özelliği veya excel veri kaynağı için depolanan yordamdaki @srvproduct özelliği için boş olmayan bir değer belirtmeniz gerekir.
Bir Excel veri kaynağını bağlantılı sunucu olarak yapılandırmak için SQL Server Management Studio veya Enterprise Manager'ı kullanma
SQL Server Management Studio (SQL Server 2005)
-
SQL Server Management Studio'da Object Explorer'daSunucu Nesneleri'ni genişletin.
-
Bağlantılı Sunucular'ısağ tıklatın ve ardından Yeni bağlantılı sunucuyutıklatın.
-
Sol bölmede Genel sayfayı seçin ve ardından aşağıdaki adımları izleyin:
-
İlk metin kutusuna, bağlı sunucu için herhangi bir ad yazın.
-
Diğer veri kaynağı seçeneğini seçin.
-
Sağlayıcı listesinde, Microsoft Jet 4.0 OLE DB Sağlayıcısı'nıtıklatın.
-
Ürün adı kutusuna, OLE DB veri kaynağının adı için Excel yazın.
-
Veri kaynak kutusuna, Excel dosyasının tam yolunu ve dosya adını yazın.
-
Sağlayıcı dize kutusunda, Excel 2002, Excel 2000 veya Excel 97 çalışma kitabı için Excel 8.0 yazın.
-
Yeni bağlantılı sunucuyu oluşturmak için Tamam'ı tıklatın.
-
Not SQL Server Management Studio'da, sunucunun içerdiği nesnelerin listesini görüntülemek için yeni bağlantılı sunucu adını genişletemezsiniz.
Kurumsal Yönetici (SQL Server 2000)
-
Kurumsal Yönetici'de Güvenlik klasörünü genişletmek için tıklatın.
-
Bağlantılı Sunucular'ısağ tıklatın ve ardından Yeni bağlantılı sunucuyutıklatın.
-
Genel sekmesinde aşağıdaki adımları izleyin:
-
İlk metin kutusuna, bağlı sunucu için herhangi bir ad yazın.
-
Sunucu türü kutusunda, Diğer veri kaynağınıtıklatın.
-
Sağlayıcı ad listesinde, Microsoft Jet 4.0 OLE DB Sağlayıcısı'nıtıklatın.
-
Veri kaynak kutusuna, Excel dosyasının tam yolunu ve dosya adını yazın.
-
Sağlayıcı dize kutusunda, Excel 2002, Excel 2000 veya Excel 97 çalışma kitabı için Excel 8.0 yazın.
-
Yeni bağlantılı sunucuyu oluşturmak için Tamam'ı tıklatın.
-
-
Yeni bağlantılı sunucu adını genişletmek için içerdiği nesnelerin listesini genişletmek için tıklatın.
-
Yeni bağlantılı sunucu adı altında Tablolar'ıtıklatın. Çalışma sayfalarınızın ve adlandırılmış aralıklarınızın doğru bölmede göründüğüne dikkat edin.
Excel veri kaynağını bağlantılı sunucu olarak yapılandırmak için depolanan yordamı kullanma
Ayrıca, bir Excel veri kaynağını bağlantılı sunucu olarak yapılandırmak için sistem depolanan yordamı sp_addlinkedserver kullanabilirsiniz:
DECLARE @RC intDECLARE @server nvarchar(128)DECLARE @srvproduct nvarchar(128)DECLARE @provider nvarchar(128)DECLARE @datasrc nvarchar(4000)DECLARE @location nvarchar(4000)DECLARE @provstr nvarchar(4000)DECLARE @catalog nvarchar(128)-- Set parameter valuesSET @server = 'XLTEST_SP'SET @srvproduct = 'Excel'SET @provider = 'Microsoft.Jet.OLEDB.4.0'SET @datasrc = 'c:\book1.xls'SET @provstr = 'Excel 8.0'EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog
Yukarıda belirtildiği gibi, bu depolanan yordam, Enterprise Manager ve SQL Server Management Studio yapılandırmasında "Ürün adı" olarak görünen @srvproduct bağımsız değişkeni için ek, rasgele bir dize değeri gerektirir. @location ve @catalog bağımsız değişkenleri kullanılmaz.
Bir Excel veri kaynağını bağlantılı sunucu olarak yapılandırmak için SQL-DMO'yu kullanma
Bir Excel veri kaynağını Microsoft Visual Basic veya başka bir programlama dilinden programlı olarak bağlı sunucu olarak yapılandırmak için SQL Distributed Management Objects'i kullanabilirsiniz. Enterprise Manager ve SQL Server Management Studio yapılandırmasında gereken aynı dört bağımsız değişkeni sağlamanız gerekir.
Private Sub Command1_Click() Dim s As SQLDMO.SQLServer Dim ls As SQLDMO.LinkedServer Set s = New SQLDMO.SQLServer s.Connect "(local)", "sa", "password" Set ls = New SQLDMO.LinkedServer With ls .Name = "XLTEST_DMO" .ProviderName = "Microsoft.Jet.OLEDB.4.0" .DataSource = "c:\book1.xls" .ProviderString = "Excel 8.0" End With s.LinkedServers.Add ls s.CloseEnd Sub
Bir Excel veri kaynağını bağlantılı sunucu olarak yapılandırmak için SMO'yu kullanma
SQL Server 2005'te, bir Excel veri kaynağını bağlı sunucu olarak programlamak için SQL Server Management Objects (SMO) kullanabilirsiniz. Bunu yapmak için Microsoft Visual Basic .NET veya başka bir programlama dilini kullanabilirsiniz. SQL Server Management Studio yapılandırmasında gereken bağımsız değişkenleri sağlamanız gerekir. SMO nesne modeli, Dağıtılmış Yönetim Nesneleri (SQL-DMO) nesne modelini genişletir ve bunun yerini alarır. SMO, SQL Server 7.0, SQL Server 2000 ve SQL Server 2005 ile uyumlu olduğundan, SMO'yu SQL Server 2000 yapılandırması için de kullanabilirsiniz.
Imports Microsoft.SqlServer.Management.SmoImports Microsoft.SqlServer.Management.CommonPublic Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim s As Server Dim conn As ServerConnection Dim ls As LinkedServer conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword") s = New Server(conn) Try ls = New LinkedServer(s, "XLTEST_DMO") With ls .ProviderName = "Microsoft.Jet.OLEDB.4.0" .ProductName = "Excel" .DataSource = "c:\book1.xls" .ProviderString = "Excel 8.0" End With ls.Create() MessageBox.Show("New linked Server has been created.") Catch ex As SmoException MessageBox.Show(ex.Message) Finally ls = Nothing If s.ConnectionContext.IsOpen = True Then s.ConnectionContext.Disconnect() End If End Try End SubEnd ClassBağlantılı bir sunucuda Excel veri kaynağını sorgulama
Bir Excel veri kaynağını bağlantılı sunucu olarak yapılandırdıktan sonra, verilerini Sorgu Çözümleyici'den veya başka bir istemci uygulamasından kolayca sorgulayabilirsiniz. Örneğin, Excel dosyanızın Sheet1'inde depolanan veri satırlarını almak için aşağıdaki kod, SQL-DMO kullanarak yapılandırdığınız bağlantılı sunucuyu kullanır:
SELECT * FROM XLTEST_DMO...Sheet1$
Excel'e bağlı sunucuyu aşağıdaki gibi "geçiş" şeklinde sorgulamak için OPENQUERY'yi de kullanabilirsiniz:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
OPENQUERY'nin beklediği ilk bağımsız değişken bağlantılı sunucu adıdır. Yukarıda gösterildiği gibi, çalışma sayfası adları için sınır layıcılar gereklidir. Ayrıca, aşağıdaki sorguyu kullanarak Excel bağlantılı sunucuda bulunan tüm tabloların listesini de alabilirsiniz:
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
Dağıtılmış sorguları kullanarak Excel veri kaynağını sorgulama
SQL Server dağıtılmış sorguları ve OPENDATASOURCE veya OPENROWSET işlevini, seyrek erişilen Excel veri kaynaklarını geçici olarak sorgulamak için kullanabilirsiniz. Not SQL Server 2005 kullanıyorsanız, aşağıdaki örnekte olduğu gibi SQL Server Surface Area Configuration'ı kullanarak Ad Hoc Distributed Sorgular seçeneğini etkinleştirdiğinizden emin olun:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$OPENROWSET'in ikinci ("Sağlayıcı Dizgesi") bağımsız değişkeni için nadir görülen bir sözdizimi kullandığını unutmayın:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls', Sheet1$)ActiveX Veri Nesneleri (ADO) geliştiricisinin OPENROWSET ile ikinci ("Sağlayıcı Dizgesi") bağımsız değişkeni için kullanmayı bekleyebileceği sözdizimi:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)Bu sözdizimi Jet Sağlayıcıdan aşağıdaki hatayı yükseltir:
Yüklenebilir ISAM bulamadım.
Not Bu hata, Veri Kaynağıyerine DataSource girerseniz de oluşur. Örneğin, aşağıdaki bağımsız değişken yanlıştır:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$) Başvurular
SQL Server bağlantılı sunucular ve dağıtılmış sorgular OLE DB Sağlayıcısı'nı kullandığından, Excel ile ADO kullanma konusunda genel kurallar ve uyarılar burada geçerlidir. Daha fazla bilgi için, Makaleyi Microsoft Bilgi Bankası'nda görüntülemek için aşağıdaki makale numarasını tıklatın:
257819 Visual Basic veya VBA'dan Excel verileriyle ADO nasıl kullanılır?SQL Server Management Objects hakkında daha fazla bilgi için aşağıdaki Microsoft Geliştirici Ağı (MSDN) Web sitesini ziyaret edin:
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspx Ad Hoc Dağıtılmış Sorgular seçeneğini etkinleştirme hakkında daha fazla bilgi için aşağıdaki MSDN Web sitesini ziyaret edin:
