Uporaba Excela s povezanimi strežniki in distribuiranimi poizvedbami strežnika SQL Server

P O V Z E T E K

Microsoft SQL Server podpira povezave z drugimi podatkovnimi viri OLE DB trajno ali začasno. Trajna povezava je znana kot povezan strežnik; začasna povezava, ustvarjena za eno samo poizvedbo, pa kot distribuirana poizvedba.

Microsoft Excelovi delovni zvezki so ena izmed vrst podatkovnega vira OLE DB, za katerimi lahko poizvedujete v strežniku SQL Server na ta način. V tem članku je opisana skladnja, s katero lahko konfigurirate Excelov podatkovni vir kot povezan strežnik, in skladnja za uporabo distribuirane poizvedbe, ki poizveduje za Excelovim podatkovnim virom.

D O D A T N E I N F O R M A C I J E

Poizvedovanje za Excelovim podatkovnim virom v povezanem strežniku

Če želite Excelov podatkovni vir konfigurirati kot strežnik, povezan z SQL Serverjem, lahko uporabite SQL Server Management Studio ali Enterprise Manager, sistemski shranjen postopek, SQL-DMO (Distributed Management Objects) ali SMO (SQL Server Management Objects). (Predmeti SMO so na voljo le za Microsoft SQL Server 2005.) V vseh primerih nastavite te štiri lastnosti:

ime, ki ga želite uporabljati za povezani strežnik;
ponudnik OLE DB, ki bo uporabljen za povezavo;
podatkovni vir ali celotna pot in ime datoteke za Excelov delovni zvezek;
niz ponudnika, ki določa cilj kot Excelov delovni zvezek. Privzeto Jet Provider pričakuje Accessovo zbirko podatkov.

Postopek sp_addlinkedserver, , shranjen v sistemu, prav tako pričakuje lastnost @srvproduct, ki je lahko poljubna vrednost niza.

Opomba Če uporabljate SQL Server 2005, za lastnost Ime izdelka v programu SQL Server Management Studio ali za lastnost @srvproduct v shranjenem postopku za Excelov podatkovni vir navedite vrednost, ki ni prazna.

Uporaba programa SQL Server Management Studio ali Enterprise Manager za konfiguriranje Excelovega podatkovnega vira kot povezanega strežnika

SQL Server Management Studio (SQL Server 2005)

V programu SQL Server Management Studio pod možnostjo Object Explorer (Raziskovalec predmetov) razširite Server Objects (Predmeti strežnika).
Z desno tipko miške kliknite Linked Servers (Povezani strežniki) in nato kliknite New linked server (Nov povezan strežnik).
V levem podoknu izberite stran General (Splošno) in sledite temu postopku:
1. V prvo polje za besedilo vnesite poljubno ime povezanega strežnika.
2. Izberite možnost Other data source (Drug podatkovni vir).
3. Na seznamu Provider (Ponudnik) kliknite Microsoft Jet 4.0 OLE DB Provider (Ponudnik Microsoft Jet 4.0 OLE DB).
4. V polje Product name (Ime izdelka) za ime podatkovnega vira OLE DB vnesite Excel.
5. V polje Data source (Podatkovni vir) vnesite celotno pot in ime Excelove datoteke.
6. V polje Provider string (Niz ponudnika) vnesite Excel 8.0 za delovni zvezek programa Excel 2002, Excel 2000 ali Excel 97.
7. Kliknite OK (V redu), da ustvarite nov povezani strežnik.

Opomba V programu SQL Server Management Studio ne morete razširiti imena novega povezanega strežnika in si ogledati seznama predmetov, ki jih vsebuje strežnik.

Enterprise Manager (SQL Server 2000)

V programu Enterprise Manager s klikom razširite mapo Security (Varnost).
Z desno tipko miške kliknite Linked Servers (Povezani strežniki) in nato kliknite New linked server (Nov povezan strežnik).
Na zavihku General (Splošno) sledite temu postopku:
1. V prvo polje za besedilo vnesite poljubno ime povezanega strežnika.
2. V polju Server type (Vrsta strežnika) kliknite Other data source (Drug podatkovni vir).
3. Na seznamu Provider name (Ime ponudnika) kliknite Microsoft Jet 4.0 OLE DB Provider (Ponudnik Microsoft Jet 4.0 OLE DB).
4. V polje Data source (Podatkovni vir) vnesite celotno pot in ime Excelove datoteke.
5. V polje Provider string (Niz ponudnika) vnesite Excel 8.0 za delovni zvezek programa Excel 2002, Excel 2000 ali Excel 97.
6. Kliknite OK (V redu), da ustvarite nov povezani strežnik.
S klikom razširite ime novega povezanega strežnika, da razširite seznam predmetov, ki jih vsebuje.
Pod imenom novega povezanega strežnika kliknite Tabele. Delovni listi in poimenovani obsegi so prikazani v desnem podoknu.

Uporaba shranjenega postopka za konfiguracijo Excelovega podatkovnega vira kot povezanega strežnika

Če želite Excelov podatkovni vir konfigurirati kot povezan strežnik, lahko uporabite tudi postopek sp_addlinkedserver, shranjen v sistemu:

DECLARE @RC int
DECLARE @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 values
SET @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

Kot je navedeno zgoraj, ta shranjeni postopek zahteva dodatno, poljubno vrednost niza za argument @srvproduct, ki je prikazan kot »Ime izdelka«v konfiguraciji programa Enterprise Manager and SQL Server Management Studio. Argumenta @location in @catalog nista uporabljena.

Uporaba predmetov SQL-DMO za konfiguracijo Excelovega podatkovnega vira kot povezanega strežnika

S predmeti SQL Distributed Management Objects lahko Excelov podatkovni vir konfigurirate kot povezan strežnik s programiranjem v Microsoft Visual Basicu ali drugem programskem jeziku. Navesti morate iste štiri argumente, ki so zahtevani v konfiguraciji programov Enterprise Manager in SQL Server Management Studio.

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.Close
End Sub

Uporaba predmetov SMO za konfiguriranje Excelovega podatkovnega vira kot povezanega strežnika

V programu SQL Server 2005 lahko s predmeti SQL Server Management Objects (SMO) konfigurirate Excelov podatkovni vir kot povezan strežnik s programiranjem. Za to lahko uporabite Microsoft Visual Basic .NET ali drug programski jezik. Navesti morate argumente, ki so zahtevani v konfiguraciji programa SQL Server Management Studio. Model predmeta SMO razširi in nadomesti model predmeta Distributed Management Objects (SQL-DMO). Ker je SMO združljiv s programi SQL Server 7.0, SQL Server 2000 in SQL Server 2005, lahko z njim konfigurirate tudi SQL Server 2000.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public 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 Sub
End Class

Poizvedovanje po Excelovem podatkovnem viru v povezanem strežniku

Ko Excelov podatkovni vir konfigurirate kot povezan strežnik, zlahka poizvedujete po njegovih podatkih iz programa Query Analyzer ali drugega odjemalskega programa. Na primer za pridobivanje vrstice s podatki, ki so shranjeni na Listu 1 Excelove datoteke, spodnja koda uporabi povezani strežnik, ki ste ga konfigurirali s predmetom SQL-DMO:

SELECT * FROM XLTEST_DMO...Sheet1$

Če želite v Excelovem povezanem strežniku poizvedovati v »prepustnem« načinu, uporabite OPENQUERY:

SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')

Prvi argument, ki ga pričakuje OPENQUERY, je ime povezanega strežnika. Za imena delovnih listov so obvezna ločila, kot je prikazano zgoraj.

Seznam vseh tabel, ki so na voljo v Excelovem povezanem strežniku, lahko pridobite s to poizvedbo:

EXECUTE SP_TABLES_EX 'XLTEST_DMO'

Poizvedovanje po Excelovem podatkovnem viru z distribuiranimi poizvedbami

Z distribuiranimi poizvedbami SQL Serverja in funkcijo OPENDATASOURCE ali OPENROWSET lahko začasno poizvedujete po redko dostopanih Excelovih podatkovnih virih.

Opomba Če uporabljate SQL Server 2005, zagotovite, da ste z orodjem SQL Server Surface Area Configuration omogočili možnost Ad Hoc Distributed Queries (Začasne distribuirane poizvedbe), kot je prikazano v tem primeru:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$

OPENROWSET za drugi argument (»Provider string« (Niz ponudnika)) uporablja drugačno skladnjo:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)

Skladnja, ki bi jo razvijalec predmetov ActiveX Data Objects (ADO) pričakovano uporabil za drugi argument (»Provider String« (Niz ponudnika)) s funkcijo OPENROWSET:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)

Ta skladnja v ponudniku Jet Provider povzroči to napako:

Could not find installable ISAM. (Ni mogoče najti namestljivega ISAM-a.)

Opomba Do napake pride tudi, če namesto Data Source vnesete DataSource. Na primer ta argument ni pravilen:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)

S K L I C I

Povezani strežniki in distribuirane poizvedbe strežnika SQL Server uporabljajo ponudnika OLE DB, zato tukaj veljajo splošna navodila in opozorila o uporabi predmeta ADO z Excelom.
Če želite več informacij, kliknite to številko članka iz Microsoftove zbirke znanja:

257819 Uporaba predmeta ADO z Excelovimi podatki iz programa Visual Basic ali VBA (Ta povezava lahko vodi k besedilu, ki je delno ali v celoti v angleščini)Če želite več informacij o predmetih SQL Server Management Objects, obiščite to spletno mesto MSDN (Microsoft Developer Network):

http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspxČe želite več informacij o tem, kako omogočite možnost Ad Hoc Distributed Queries (Začasne distribuirane poizvedbe), obiščite to spletno mesto MSDN:

http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx