DetailPage-MSS-KB

Base de Dados de Conhecimento

ID do artigo: 306397 - Última revisão: domingo, 15 de março de 2015 - Revisão: 7.0

 

Nesta página

Sumário

Microsoft SQL Server oferece suporte a conexões com fontes de dados OLE DB em um persistente ou ocasionalmente. A conexão persistente é conhecida como um servidor vinculado; uma conexão ad hoc é feita para 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 dessa maneira por meio do SQL Server. Este artigo descreve a sintaxe que é necessária para 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.

Mais Informações

Consultar uma fonte de dados do Excel em um servidor vinculado

Você pode usar o 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 só 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 você deseja usar para o servidor vinculado.
  • O OLE DB Provider que deve ser usado para a conexão.
  • A fonte de dados ou o nome completo do caminho e arquivo 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 de procedimento armazenado do sistema sp_addlinkedserver também espera que a propriedade @srvproduct , que pode ser qualquer valor de cadeia de caracteres.

Observação: Se você estiver usando o SQL Server 2005, você deve especificar um valor que não esteja vazio para a propriedade de nome do produto no SQL Server Management Studio ou para a propriedade @srvproduct no procedimento armazenado para 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 o Servidor de objetos no Pesquisador de objetos.
  2. Clique com botão direito Servidores vinculadose, em seguida, 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 o <b00> </b00>outra fonte de dados opção.
    3. Na lista Provider , clique em Microsoft Jet 4.0 OLE DB Provider.
    4. Na caixa nome do produto , digite Excel o nome da fonte de dados OLE DB.
    5. Na caixa fonte de dados , digite o nome de arquivo e caminho completo do arquivo do Excel.
    6. Na caixa seqüência do provedor , digite Excel 8.0 para uma pasta de trabalho do Excel 97, Excel 2000 ou Excel 2002.
    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 de 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 botão direito Servidores vinculadose, em seguida, 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 arquivo do Excel.
    5. Na caixa seqüência do provedor , digite Excel 8.0 para uma pasta de trabalho do Excel 97, Excel 2000 ou Excel 2002.
    6. Clique em OK para criar o novo servidor vinculado.
  4. Clique para expandir o novo nome de servidor vinculado para expandir List de objetos que ela contém.
  5. Com o novo nome de servidor vinculado, clique em tabelas. Observe que as planilhas e intervalos nomeados são exibidos no rightpane.

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
				
Conforme observado acima, este procedimento armazenado requer um valor de seqüência de caracteres arbitrária adicionais para o argumento @srvproduct , que aparece como "Nome do produto" na configuração do Enterprise Manager e SQL Server Management Studio. Os argumentos @location e @catalog não são usados.

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

Você pode usar o 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 o 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 do SMO estende e substitui o modelo de objeto do Distributed Management Objects (SQL-DMO). Porque o SMO é compatível com o SQL Server versão 7.0, SQL Server 2000 e SQL Server 2005, você também pode usar o SMO para configuração do 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 do Excel em um servidor vinculado

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

Você também pode obter uma lista de todas as tabelas que 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 SQL Server distribuído e a função OPENDATASOURCE ou OPENROWSET para fontes de dados acessada com pouca freqüência de consulta do Excel de forma ad-hoc.

Observação: Se você estiver usando o SQL Server 2005, certifique-se de que você tenha ativado a opção de Consultas Ad Hoc distribuídas usando configuração de área de superfície do SQL Server, como no seguinte exemplo:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
				
Observe que OPENROWSET usa uma sintaxe comum 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 esperar 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$)
				
Essa 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 Fonte de dados em vez de Fonte de dados. Por exemplo, o argumento a seguir está incorreto:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$) 
				

Referências

Porque o SQL Server de servidores vinculados e distribuídos 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 de artigo que se segue para visualizar o artigo na Base de Conhecimento Microsoft:
257819  (http://support.microsoft.com/kb/257819/ ) Como usar o ADO com dados do Excel no Visual Basic ou VBA
Para obter mais informações sobre o SQL Server Management Objects, visite o seguinte site da Microsoft Developer Network (MSDN):
. aspx http://msdn2.microsoft.com/en-us/library/ms162169 (ide) (http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspx)
Para obter mais informações sobre como habilitar a opção de Consultas Ad Hoc distribuídas , visite o seguinte site da MSDN:
. aspx http://msdn2.microsoft.com/en-us/library/ms189978 (ide) (http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx)

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: 
kbsqlsetup kbdatabase kbhowto kbjet kbmt KB306397 KbMtpt
Tradução automáticaTradução automática
IMPORTANTE: Este artigo foi traduzido pelo software de tradução automática da Microsoft e eventualmente pode ter sido editado pela Microsoft Community através da tecnologia Community Translation Framework (CTF) ou por um tradutor profissional. A Microsoft oferece artigos traduzidos automaticamente por software, por tradutores profissionais e editados pela comunidade para que você tenha acesso a todos os artigos de nossa Base de Conhecimento em diversos idiomas. No entanto, um artigo traduzido pode conter erros de vocabulário, sintaxe e/ou gramática. A Microsoft não é responsável por qualquer inexatidão, erro ou dano causado por qualquer tradução imprecisa do conteúdo ou por seu uso pelos nossos clientes.
Clique aqui para ver a versão em Inglês deste artigo: 306397  (http://support.microsoft.com/kb/306397/en-us/ )
Compartilhar
Opções de suporte adicionais
Fóruns de Suporte do Microsoft Community
Contate-nos diretamente
Localize um parceiro certificado da Microsoft
Microsoft Store