Como usar o Excel com o SQL Server vinculada servidores e distribuídos consultas

ID do artigo: 306397 - Exibir os produtos aos quais esse artigo se aplica.
Tradução automáticaExibir Aviso de Isenção de Tradução Automática
Expandir tudo | Recolher tudo

Nesta página

Sumário

O Microsoft SQL Server oferece suporte a conexões a fontes de dados OLE em um persistente ou uma base ad hoc. A conexão persistente é conhecida como um servidor vinculado; uma conexão ad hoc que é feita para fins de uma única consulta é conhecida como uma consulta distribuída.

Pastas de trabalho do Microsoft Excel são um tipo de fonte de dados OLE DB que você pode consultar através do SQL Server dessa maneira. Este artigo descreve a sintaxe que é necessária configurar uma fonte de dados do Excel como um servidor vinculado, bem como a sintaxe que é necessária usar uma consulta distribuída que consultar uma fonte de dados do Excel.
Voltar para o início | Submeter comentários

Mais Informações

Consultar uma fonte de dados Excel em um servidor vinculado

Você pode usar SQL Server Management Studio ou Enterprise Manager, um sistema armazenados procedimento, SQL-DMO (Distributed Management Objects), ou o SMO (SQL Server Management Objects) para configurar uma fonte de dados do Excel como um servidor vinculado do SQL Server. (SMO somente estão disponíveis para o Microsoft SQL Server 2005.) Em todos esses casos, você deve sempre definir as seguintes quatro propriedades:
  • O nome que deseja usar para o servidor vinculado.
  • O OLE DB Provider que deve ser usado para a conexão.
  • fonte de dados ou caminho completo e arquivo o nome para a pasta de trabalho do Excel.
  • A seqüência do provedor , que identifica o destino como uma pasta de trabalho do Excel. Por padrão, o provedor Jet espera um banco de dados do Access.
O procedimento de armazenado do sistema sp_addlinkedserver também espera @ srvproduct propriedade, que pode ser qualquer valor de seqüência de caracteres.

Observação Se você estiver usando o SQL Server 2005, você deve especificar um valor que não está vazio para a propriedade de nome de produto no SQL Server Management Studio ou para @ srvproduct propriedade no procedimento armazenado de uma fonte de dados do Excel.

Usando o SQL Server Management Studio ou Enterprise Manager para configurar uma fonte de dados do Excel como um servidor vinculado

SQL Server Management Studio (SQL Server 2005)
  1. No SQL Server Management Studio, expanda Server Objects no Object Explorer .
  2. Clique com o botão direito Servidores vinculados e clique em novo servidor vinculado .
  3. No painel esquerdo, selecione a página Geral e, em seguida, execute as seguintes etapas:
    1. Na primeira caixa de texto, digite um nome para o servidor vinculado.
    2. Selecione a opção de outra fonte de dados .
    3. Na lista provedor , clique em Microsoft Jet 4.0 OLE DB Provider .
    4. Na caixa nome do produto , digite o Excel para o nome da fonte de dados OLE.
    5. Na caixa fonte de dados , digite o caminho e o nome completo do arquivo do Excel.
    6. Na caixa seqüência do provedor , digite Excel 8.0 para uma pasta de trabalho Excel 2002, Excel 2000 ou Excel 97.
    7. Clique em OK para criar o novo servidor vinculado.
Observação No SQL Server Management Studio, você não pode expandir o novo nome do servidor vinculado para exibir a lista de objetos que contém 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 Servidores vinculados e clique em novo servidor vinculado .
  3. Na guia Geral , siga estas etapas:
    1. Na primeira caixa de texto, digite um nome para o servidor vinculado.
    2. Na caixa tipo de servidor , clique em outra fonte de dados .
    3. Na lista nome do provedor , clique em Microsoft Jet 4.0 OLE DB Provider .
    4. Na caixa fonte de dados , digite o nome de arquivo e caminho completo do Excel arquivo.
    5. Na caixa seqüência do provedor , digite Excel 8.0 para uma pasta de trabalho Excel 2002, Excel 2000 ou Excel 97.
    6. Clique em OK para criar o novo servidor vinculado.
  4. Clique para expandir o novo nome de servidor vinculado para expandir a lista de objetos que ela contém.
  5. Sob o novo nome do servidor vinculado, clique em tabelas . Observe que as planilhas e intervalos nomeados são exibidas no painel à direita.

Usando um procedimento armazenado para configurar uma fonte de dados do Excel como um servidor vinculado

Você também pode usar o de procedimento armazenado do sistema sp_addlinkedserver para configurar uma fonte de dados do Excel como um servidor vinculado: 
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 mencionado acima, este procedimento armazenado requer um valor de seqüência de caracteres adicionais arbitrários para @ srvproduct argumento, que aparece como "Nome do produto" na configuração do Enterprise Manager e SQL Server Management Studio. O local @ e @ catálogo argumentos não são usados.

Usando o SQL-DMO para configurar uma fonte de dados do Excel como um servidor vinculado

Você pode usar SQL Distributed Management Objects para configurar uma fonte de dados do Excel como um servidor vinculado por meio de programação do Microsoft Visual Basic ou outra linguagem de programação. Você deve fornecer os mesmos quatro argumentos que são necessárias na configuração do Enterprise Manager e 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

Usando o SMO para configurar uma fonte de dados do Excel como um servidor vinculado

No SQL Server 2005, você pode usar SQL Server Management Objects (SMO) para configurar uma fonte de dados do Excel como um servidor vinculado por meio de programação. Para fazer isso, você pode usar o Microsoft Visual Basic .NET ou outra linguagem de programação. Você deve fornecer os argumentos que são necessárias na configuração do SQL Server Management Studio. O modelo de objeto SMO estende e substitui o modelo de objeto 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 fonte de dados Excel em um servidor vinculado

Depois de configurar uma fonte de dados do Excel como um servidor vinculado, você pode consultar facilmente seus dados de Query Analyzer ou outro aplicativo de cliente. Por exemplo, para recuperar as linhas de dados que são armazenados na Plan1 do seu arquivo do Excel, o código a seguir usa o servidor vinculado que você configurou usando SQL-DMO: 
SELECT * FROM XLTEST_DMO...Sheet1$
também OPENQUERY pode ser usado para consultar o servidor vinculado de Excel de maneira "passagem", da seguinte maneira: 
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
o primeiro argumento que OPENQUERY espera é o nome do servidor vinculado. Delimitadores são necessários para nomes de planilha, como mostrado acima.

Você também pode obter uma lista de todas as tabelas estão disponíveis no servidor vinculado do Excel usando a seguinte consulta: 
EXECUTE SP_TABLES_EX 'XLTEST_DMO'

Consultar uma fonte de dados do Excel usando consultas distribuídas

Você pode usar consultas distribuídas do SQL Server e a função OPENROWSET ou OPENDATASOURCE para fontes de dados do Excel consulta acessada com pouca freqüência em uma base ad hoc.

Observação Se você estiver usando o SQL Server 2005, certifique-se que você tenha ativado a opção de Consultas Ad Hoc distribuído usando a configuração da área do SQL Server superfície, como no exemplo a seguir: 
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
Observação que OPENROWSET usa uma sintaxe incomum para o segundo argumento ("seqüência do provedor"): 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)
a sintaxe que um desenvolvedor de ActiveX Data Objects (ADO) pode usar para o segundo argumento ("seqüência do provedor") com OPENROWSET: 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
esta sintaxe gera o seguinte erro do provedor Jet:
Não foi possível localizar ISAM instalável.
Observação Este erro também ocorre se você inserir DataSource em vez de Fonte de dados . Por exemplo, o argumento a seguir é incorreto: 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
Voltar para o início | Submeter comentários

Referências

Como SQL Server vinculada servidores e distribuído consultas usam o provedor OLE DB, as diretrizes gerais e cuidados sobre como usar o ADO com o Excel se aplicam aqui. Para obter mais informações, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
257819
(http://support.microsoft.com/kb/257819/ )
Como usar o ADO com dados do Excel do Visual Basic ou VBA
Para obter mais informações sobre o SQL Server Management Objects, visite o seguinte site da Web 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 ativar a opção Consultas Ad Hoc distribuídas , visite o seguinte site da MSDN:
http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx
(http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx)
Voltar para o início | Submeter comentários

Propriedades

ID do artigo: 306397 - Última revisão: sexta-feira, 2 de novembro de 2007 - Revisão: 6.4
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • 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
Palavras-chave: 
kbmt kbdatabase kbhowto kbjet KB306397 KbMtpt
Traduçã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 traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes 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 para o início | Submeter comentários

Submeter comentários

 
Voltar para o inícioVoltar para o início