Artigo: 306397 - Última revisão: sexta-feira, 2 de Novembro de 2007 - Revisão: 6.4

Como utilizar o Excel com o SQL Server servidores ligados e distribuídos consultas

Vista lado a ladoClique aqui para exibir o artigo traduzido e o artigo original em inglês, lado a lado.
Tradução automáticaVer isenção de responsabilidades para tradução automática
Ver produtos para os quais este artigo se aplica.
Dica do SistemaEste artigo aplica-se a um sistema operativo diferente do que está a utilizar. Foi desactivado o conteúdo do artigo, que pode não ser relevante para si.

Nesta página

Expandir tudo | Reduzir tudo

Sumário

Microsoft SQL Server suporta ligações a outras origens de dados OLE DB num persistentes ou numa base ad hoc. Ligação persistente é conhecida como um servidor ligado; uma ligação ad hoc efectuadas para benefício de uma consulta única é conhecida como uma consulta distribuída.

Livros do Microsoft Excel são um tipo de origem de dados OLE DB que pode consultar através do SQL Server desta forma. Este artigo descreve a sintaxe é necessária configurar uma origem de dados do Excel como um servidor ligado, bem como a sintaxe é necessária utilizar uma consulta distribuída que consulta uma origem de dados do Excel.
Voltar ao topo

Mais Informação

Consultar uma origem de dados do Excel num servidor ligado

Pode utilizar o SQL Server Management Studio ou Enterprise Manager, um sistema armazenadas procedimento SQL-DMO (Distributed Management Objects) ou SMO (SQL Server Management Objects) para configurar uma origem de dados Excel como um servidor ligado do SQL Server. (SMO só estão disponíveis para o Microsoft SQL Server 2005.) Em todos os casos, tem de definir sempre as seguintes quatro propriedades:
  • O nome que pretende utilizar para o servidor ligado.
  • O OLE DB Provider a ser utilizada para a ligação.
  • A origem de dados ou nome de caminho e o ficheiro completo para o livro do Excel.
  • A cadeia de fornecedor , que identifica o destino como um livro do Excel. Por predefinição, o fornecedor de Jet espera uma base de dados do Access.
O do procedimento armazenado do sistema sp_addlinkedserver espera também @ srvproduct propriedade, que pode ser qualquer valor de cadeia.

Nota Se estiver a utilizar o SQL Server 2005, tem de especificar um valor que não esteja vazio para a propriedade de nome de produto no SQL Server Management Studio ou @ srvproduct propriedade no procedimento armazenado para uma origem de dados do Excel.

Utilizar o SQL Server Management Studio ou Enterprise Manager para configurar uma origem de dados do Excel como um servidor ligado

SQL Server Management Studio (SQL Server 2005)
  1. No SQL Server Management Studio, expanda Servidor de objectos no Explorador de objectos .
  2. Clique com o botão direito do rato em Ligação servidores e, em seguida, clique em novo servidor ligado .
  3. No painel da esquerda, seleccione a página Geral e, em seguida, siga estes passos:
    1. Na primeira caixa de texto, escreva qualquer nome para o servidor ligado.
    2. Seleccione a opção de outra origem de dados .
    3. Na lista fornecedor , faça clique sobre O Microsoft Jet 4.0 OLE DB Provider .
    4. Na caixa nome do produto , escreva o Excel para o nome da origem de dados OLE DB.
    5. Na caixa origem de dados , escreva o nome de ficheiro e caminho completo do ficheiro do Excel.
    6. Na caixa cadeia de fornecedor , escreva Excel 8.0 para um livro do Excel 2002, Excel 2000 ou Excel 97.
    7. Clique em OK para criar o novo servidor ligado.
Nota No SQL Server Management Studio, não consegue expandir o novo nome de servidor ligado para ver a lista de objectos com o servidor.
Enterprise Manager (SQL Server 2000)
  1. No Enterprise Manager, clique para expandir a pasta de segurança .
  2. Clique com o botão direito do rato em Ligação servidores e, em seguida, clique em novo servidor ligado .
  3. No separador Geral , siga estes passos:
    1. Na primeira caixa de texto, escreva qualquer nome para o servidor ligado.
    2. Na caixa tipo de servidor , faça clique sobre outra origem de dados .
    3. Na lista nome de fornecedor , clique em Microsoft Jet 4.0 OLE DB Provider .
    4. Na caixa origem de dados , escreva o nome de ficheiro e caminho completo do Excel ficheiro.
    5. Na caixa cadeia de fornecedor , escreva Excel 8.0 para um livro do Excel 2002, Excel 2000 ou Excel 97.
    6. Clique em OK para criar o novo servidor ligado.
  4. Clique para expandir o novo nome de servidor ligado para expandir a lista de objectos que contém.
  5. Com o novo nome de servidor ligado, clique em tabelas . Repare que as folhas de cálculo e intervalos com nome aparecem no painel da direita.

Utilizar um procedimento armazenado para configurar uma origem de dados do Excel como um servidor ligado

Também pode utilizar o do procedimento armazenado do sistema sp_addlinkedserver para configurar uma origem de dados do Excel como um servidor ligado: 
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
como indicado acima, este procedimento requer um valor de cadeia arbitrário adicionais para @ srvproduct argumento, que aparece como "Nome do produto" na configuração do Enterprise Manager e o SQL Server Management Studio. A localização de @ e @ catálogo não forem utilizados argumentos.

Utilizar SQL-DMO para configurar uma origem de dados do Excel como um servidor ligado

Pode utilizar o SQL Distributed Management Objects para configurar uma origem de dados do Excel como um servidor ligado através de programação do Microsoft Visual Basic ou outra linguagem de programação. Tem de fornecer quatro argumentos mesmos que são necessárias na configuração do Enterprise Manager e o 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

Utilizar SMO para configurar uma origem de dados do Excel como um servidor ligado

No SQL Server 2005, pode utilizar o SQL Server Management Objects (SMO) para configurar uma origem de dados do Excel como um servidor ligado através de programação. Para efectuar este procedimento, pode utilizar o Microsoft Visual Basic .NET ou outra linguagem de programação. Tem de fornecer argumentos que são necessárias na configuração do SQL Server Management Studio. O modelo de objecto SMO expande e substitui o modelo de objecto Distributed Management Objects (SQL-DMO). Because SMO is compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, you can also use SMO for configuration of 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

Consultar uma origem de dados do Excel num servidor ligado

Depois de configurar uma origem de dados do Excel como um servidor ligado, pode consultar facilmente os dados da analisador de consultas ou outra aplicação de cliente. Por exemplo, para obter as linhas de dados que são armazenados na Folha1 do ficheiro do Excel, o código seguinte utiliza o servidor ligado configurado utilizando SQL-DMO: 
SELECT * FROM XLTEST_DMO...Sheet1$
pode também utilizar OPENQUERY para consultar o servidor ligado Excel de uma forma "passthrough", da seguinte forma: 
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
o primeiro argumento OPENQUERY espera é o nome do servidor ligado. Delimitadores são necessários para nomes de folha de cálculo, conforme mostrado acima.

Também pode obter uma lista de todas as tabelas que estão disponíveis no servidor ligado Excel utilizando a seguinte consulta: 
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
Voltar ao topo

Consultar uma origem de dados do Excel utilizando consultas distribuídas

Pode utilizar consultas de SQL Server distribuídos e a função OPENDATASOURCE ou OPENROWSET para consulta acedida com pouca frequência origens de dados do Excel numa base ad hoc.

Nota Se estiver a utilizar o SQL Server 2005, certifique-se de que activou a opção de Consultas Ad Hoc distribuídas utilizando a configuração do SQL Server superfície área, como no exemplo seguinte: 
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
Nota OPENROWSET utiliza uma sintaxe comuns para o segundo argumento ("fornecedor de cadeia"): 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)
a sintaxe que poderá esperar um programador de ActiveX Data Objects (ADO) a utilizar para o segundo argumento ("fornecedor de cadeia") com OPENROWSET: 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
esta sintaxe aumenta o seguinte erro do fornecedor de Jet:
Não foi possível localizar um ISAM instalável.
Nota Este erro também ocorre se introduzir a origem de dados em vez de Origem de dados . Por exemplo, o argumento seguinte está incorrecto: 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
Voltar ao topo

Referências

Uma vez que SQL Server servidores ligados e distribuídos consultas utilizam o fornecedor OLE DB, as orientações gerais e medidas sobre como utilizar ADO com o Excel aplicam aqui. Para obter mais informações, clique no número de artigo que se segue para visualizar o artigo na Microsoft Knowledge Base:
257819  (http://support.microsoft.com/kb/257819/ ) Como utilizar ADO com dados do Excel a partir do Visual Basic ou VBA
Para obter mais informações sobre objectos de gestão do SQL Server, visite o seguinte Web site da Microsoft Developer Network (MSDN):
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspx (http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspx)
Para obter mais informações sobre como activar a opção de Consultas Ad Hoc distribuídas , visite o seguinte Web site da MSDN:
http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx (http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx)
Voltar ao topo
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL 2005 Server Enterprise
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL 2005 Server Workgroup
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 7.0 Standard Edition
Voltar ao topo
Palavras-chave: 
kbmt kbdatabase kbhowto kbjet KB306397 KbMtpt
Voltar ao topo
Tradução automáticaTradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine translation ou MT), não tendo sido portanto revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática? erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 306397  (http://support.microsoft.com/kb/306397/en-us/ )
Voltar ao topo