none
Importando Arquivo Excel RRS feed

  • Pergunta

  • Bom dia pessoal. Vê se vocês pode me ajudar. 

    Montei uma query para importar uma planilha no excel, só que está dando erro. Veja: esse é o erro

    O SQL Server bloqueou o acesso a INSTRUÇÃO 'OpenRowset/OpenDatasource' do componente 'Ad Hoc Distributed Queries' porque esse componente está desativado como parte da configuração de segurança deste servidor. Um administrador de sistema pode habilitar o uso de 'Ad Hoc Distributed Queries' usando sp_configure. Para obter mais informações sobre como habilitar 'Ad Hoc Distributed Queries', procure 'Ad Hoc Distributed Queries' nos Manuais Online do SQL Server.
    

    esse é o meu código

    USE [GLIMOBILIARIA]
    GO
    
    EXECUTE sp_configure
    'show advanced options', 1
    reconfigure
    EXECUTE sp_configure
    'Ad Hoc Distributed Queries', 1 
    reconfigure
    
    /* Criando a tabela com os meus atributos da planilha */
     
    create table tbClientes(
    	DDD_Cliente					Varchar(3) NOT NULL,
    	Telefone_Cliente			VARCHAR(15) NOT NULL,
    	Nome_Cliente				VARCHAR(60) NOT NULL,
    	Endereco_Cliente			VARCHAR(60) NOT NULL,
    	NumeroEndereco_Cliente		VARCHAR(5) NOT NULL,
    	ComplEndereco_Cliente		VARCHAR(30) NOT NULL, 
    	Bairro_Cliente				VARCHAR(35) NOT NULL,
    	Cep_Proprietario			NVARCHAR(8) NOT NULL,
    	Municipio_Proprietario		VARCHAR(35) NOT NULL,
    	Sexo_Cliente				CHAR(1) NOT NULL,
    	Data_Nascimento				DATETIME NOT NULL,
    	DDD1Celular_Cliente			Varchar(3) NOT NULL,
    	Telefone_CelularCliente1	VARCHAR(15) NOT NULL,
    	DDD2Celular_Cliente			Varchar(3) NOT NULL,
    	Telefone_CelularCliente2	VARCHAR(15) NOT NULL,
    	UF_Cliente					NCHAR(2) NOT NULL,
    	Profissao_Cliente			VARCHAR(35) NOT NULL,
    	Email_Cliente				NVARCHAR(100) NOT NULL)
     
    /* Comando para importar a planilha na tabela */
     
    INSERT INTO tbClientes SELECT
    * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
    'EXCEL 10.0;Database=C:\Trabalhos\Clientes.xls'
    ,Dados$)
    
    IF (OBJECT_ID('dbo.stpSecurity_Checklist') IS NULL) EXEC('CREATE PROCEDURE dbo.stpSecurity_Checklist AS SELECT 1')
    GO
    

    sábado, 21 de setembro de 2019 14:55

Todas as Respostas

  • Garibaldo,

    o aviso já explica o motivo:
    O SQL Server bloqueou o acesso a INSTRUÇÃO 'OpenRowset/OpenDatasource' do componente 'Ad Hoc Distributed Queries' porque esse componente está desativado como parte da configuração de segurança deste servidor. Um administrador de sistema pode habilitar o uso de 'Ad Hoc Distributed Queries' usando sp_configure. Para obter mais informações sobre como habilitar 'Ad Hoc Distributed Queries', procure 'Ad Hoc Distributed Queries' nos Manuais Online do SQL Server.

    O procedimento para permitir o uso da função OPENROWSET() é o que você realizou:

    -- código #1
    EXECUTE sp_configure 'show advanced options', 1 reconfigure
    go EXECUTE sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure
    go

     
    Ele precisa ser executado uma única vez e por usuário que seja administrador: "Para executar sp_configure com ambos os parâmetros para alterar uma opção de configuração ou para executar a instrução RECONFIGURE, você deve receber a permissão de nível de servidor ALTER Settings. A permissão ALTER SETTINGS é implicitamente mantida pelas funções de servidor fixas sysadmin e serveradmin, conforme consta em sp_configure.

    Sugiro que retire o código de ativação da opção de dentro do código de importação, de modo que ele seja executado à parte, uma única vez.

    ---

    Como consta no aviso, "esse componente está desativado como parte da configuração de segurança deste servidor". Avalie a utilização de outro método para importar planilhas, sem colocar em risco a segurança do servidor. Uma forma é através de vinculação de servidores (linked server); detalhes no artigo “Importação de dados em massa no SQL Server”.

    ---

    O provedor ACE OLE DB precisa ser configurado:

    -- código #2
    EXECUTE sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.nn.0', N'AllowInProcess', 1 go EXECUTE sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.nn.0', N'DynamicParameters', 1 go

    Altere nn para a versão: 12, 15 ou 16.

    ---

    Sugiro que também retire a última linha do código:
       IF (OBJECT_ID('dbo.stpSecurity_Checklist') IS NULL) EXEC('CREATE PROCEDURE dbo.stpSecurity_Checklist AS SELECT 1')

    Em resumo, no código de importação da planilha deixe somente as instruções de importação.



    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz domingo, 22 de setembro de 2019 11:16
    sábado, 21 de setembro de 2019 15:33
  • Boa tarde José Diaz. Baseando-se na sua referência eu montei uma query porém está dando um erro.

    a query

    EXECUTE sp_addlinkedserver
    	@server = 'PlanilhaExcel',
    	@srvproduct = 'Excel',
    	@provider = 'Microsoft.Jet.OLEDB.4.0',
    	@datasrc = 'C:\Trabalhos\clientes.xls',
    	@provstr = 'Excel 8.0;IMEX=1;HDR=YES;';
    go
    
    USE [GLIMOBILIARIA]
    TRUNCATE TABLE dbo.tbClientes;
    go
    
    set IDENTITY_INSERT tbClientes on;
    INSERT into dbo.tbClientes
    with (tablock)
    	(DDD_Cliente, Telefone_Cliente, Nome_Cliente, Endereco_Cliente, NumeroEndereco_Cliente, 
    	 ComplEndereco_Cliente, Bairro_Cliente, Cep_Proprietario, Municipio_Proprietario, 
    	 Sexo_Cliente, Data_Nascimento, DDD1Celular_Cliente, Telefone_CelularCliente1, 
    	 DDD2Celular_Cliente, Telefone_CelularCliente2, UF_Cliente, Profissao_Cliente, Email_Cliente)
    
    SELECT	T.DDD_Cliente, T.Telefone_Cliente, T.Nome_Cliente, T.Endereco_Cliente, T.NumeroEndereco_Cliente, 
    	 T.ComplEndereco_Cliente, T.Bairro_Cliente, T.Cep_Proprietario, T.Municipio_Proprietario, 
    	 T.Sexo_Cliente,  convert(date, T.[Data_Nascimento], 102), T.DDD1Celular_Cliente, T.Telefone_CelularCliente1, 
    	 T.DDD2Celular_Cliente, T.Telefone_CelularCliente2, T.UF_Cliente, T.Profissao_Cliente, T.Email_Cliente
    from PlanilhaExcel...[clientes$] as T;
    set IDENTITY_INSERT tbClientes off;
    

    o erro

    Mensagem 15028, Nível 16, Estado 1, Procedimento sp_addlinkedserver, Linha 82
    O servidor 'PlanilhaExcel' já existe.
    Mensagem 7302, Nível 16, Estado 1, Linha 3
    Não é possível criar uma instância do provedor do OLE DB "Microsoft.Jet.OLEDB.4.0" para o servidor vinculado "PlanilhaExcel".
    

    sábado, 21 de setembro de 2019 16:41
  • Garibaldo, o trecho de código

    -- código #3
    EXECUTE sp_addlinkedserver @server = 'PlanilhaExcel', @srvproduct = 'Excel', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'C:\Trabalhos\clientes.xls', @provstr = 'Excel 8.0;IMEX=1;HDR=YES;'; go

     
    somente deve ser executado uma única vez. Isto é, uma vez estabelecida a vinculação com a planilha, não é mais necessário executar esse código. Se executar o código acima uma segunda vez, a mensagem de erro exibida é justamente
          O servidor 'PlanilhaExcel' já existe

    Para testar se a vinculação foi estabelecida, basta executar

    -- código #4
    EXECUTE sp_testlinkedserver
    	@server = 'PlanilhaExcel';

    Quando quiser remover a vinculação, basta executar

    -- código #5
    EXECUTE sp_dropserver @server = 'PlanilhaExcel';

    ---

    As linhas

         set IDENTITY_INSERT tbClientes on;
         set IDENTITY_INSERT tbClientes off;

    não são necessárias neste caso, pois não existe coluna com a propriedade IDENTITY na tabela tbClientes.

    ---

    Como está formatada a coluna que contém a data de nascimento na planilha clientes$?

    No exemplo do artigo “Importação de dados em massa no SQL Server” foi utilizado
        convert(date, T.[data], 102),
    pois na coluna da planilha de teste as datas estavam como aaaa/mm/dd. No seu caso terá que alterar o valor 102 para o valor de estilo que corresponda ao formato em que está na planilha a ser importada, conforme valores de estilo que constam para a função CONVERT(). Por exemplo, se a data estiver formatada como dd/mm/aaaa então utilize 103 no lugar de 102.

    Se a coluna Data_Nascimento da tabela tbClientes conterá somente a data de nascimento, sem o horário, sugiro que altere para o tipo de dados date.



    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sábado, 21 de setembro de 2019 19:06
    sábado, 21 de setembro de 2019 18:24
  • Boa tarde José, desculpe a minha insistência. É que eu estou dependendo desse trabalho para conseguir um cliente e agora está dando esse erro.

    Mensagem 7302, Nível 16, Estado 1, Linha 3
    Não é possível criar uma instância do provedor do OLE DB "Microsoft.Jet.OLEDB.4.0" para o servidor vinculado "PlanilhaExcel".

    sábado, 21 de setembro de 2019 18:57
  • Mensagem 7302, Nível 16, Estado 1, Linha 3
    Não é possível criar uma instância do provedor do OLE DB "Microsoft.Jet.OLEDB.4.0" para o servidor vinculado "PlanilhaExcel".

    Qual é o nome do arquivo que contém a planilha? Em qual diretório ele está?

    A planilha foi gerada com qual versão do Excel? 

    A extensão do arquivo com a planilha é .xls ou .xlsx?

    No código #3 (na resposta anterior que postei) você deve informar o caminho e o nome do arquivo que contém a planilha no parâmetro @datasrc. Por exemplo, se o arquivo que contém a planilha é o "C:\temp\Extrato.xls", então fica assim:
           @datasrc = 'C:\temp\Extrato.xls',

    ---

    Execute primeiro o código #5. Depois, execute o código #3 e, se não for emitida nenhuma mensagem de erro, execute o código #4. Se tudo estiver certo, então rode o seu código de importação.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sábado, 21 de setembro de 2019 19:45
    sábado, 21 de setembro de 2019 19:16
  • A extensão do arquivo é: .xls

    @datasrc = 'C:\Trabalhos\clientes.xls',

    tanto o meu sql server e o wintos é 64 bits.

    sábado, 21 de setembro de 2019 19:25
  • Execute primeiro o código #5. Depois, execute o código #3 e, se não for emitida nenhuma mensagem de erro, execute o código #4.

    Se tudo estiver certo, então rode o seu código de importação:

    USE [GLIMOBILIARIA]
    TRUNCATE TABLE dbo.tbClientes;
    go
    
    INSERT into dbo.tbClientes
    with (tablock)
    	(DDD_Cliente, Telefone_Cliente, Nome_Cliente, Endereco_Cliente, NumeroEndereco_Cliente, 
    	 ComplEndereco_Cliente, Bairro_Cliente, Cep_Proprietario, Municipio_Proprietario, 
    	 Sexo_Cliente, Data_Nascimento, DDD1Celular_Cliente, Telefone_CelularCliente1, 
    	 DDD2Celular_Cliente, Telefone_CelularCliente2, UF_Cliente, Profissao_Cliente, Email_Cliente)
    
    SELECT	T.DDD_Cliente, T.Telefone_Cliente, T.Nome_Cliente, T.Endereco_Cliente, T.NumeroEndereco_Cliente, 
    	 T.ComplEndereco_Cliente, T.Bairro_Cliente, T.Cep_Proprietario, T.Municipio_Proprietario, 
    	 T.Sexo_Cliente,  convert(date, T.[Data_Nascimento], 102), T.DDD1Celular_Cliente, T.Telefone_CelularCliente1, 
    	 T.DDD2Celular_Cliente, T.Telefone_CelularCliente2, T.UF_Cliente, T.Profissao_Cliente, T.Email_Cliente
    from PlanilhaExcel...[clientes$] as T;
    


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    sábado, 21 de setembro de 2019 19:59
  • Eu Executei primeiro o código #5, não deu erro.  Depois, executei o código #3, também não deu erro.

    Quando executei o código #4 deu a mensagem de erro..

    Mensagem 7302, Nível 16, Estado 1, Linha 3
    Não é possível criar uma instância do provedor do OLE DB "Microsoft.Jet.OLEDB.4.0" para o servidor vinculado "PlanilhaExcel".
    

    sábado, 21 de setembro de 2019 20:14
  • Eu Executei primeiro o código #5, não deu erro.  Depois, executei o código #3, também não deu erro.
    Quando executei o código #4 deu a mensagem de erro..

    Segundo o artigo You encounter error messages when you execute linked server queries without the required providers installed, há 3 possibilidades para ocorrer esse erro:

    • The OLEDB provider is not yet installed;
    • The installed OLEDB provider's bitness does not match the bitness of SQL Server. For example, you may have installed 32 bit version of the provider but SQL Server is running in 64 bit mode;
    • The OLEDB provider is not registered.
     
    Quais provedores OLE DB estão instalados?

        EXECUTE master.dbo.sp_MSset_oledb_prop

    ---

    O provedor OLE DB Jet 4.0 é de 32 bits. Como você citou que está a utilizar Windows de 64 bits, então pode substitui-lo pelo ACE OLE DB no código #3

        @provider='Microsoft.ACE.OLEDB.12.0',

    Lembre-se de executar o código #2, caso ainda não o tenha feito. Uma única vez.

    Após alterar o código #3, repita o roteiro: código #5, #código #3, código #4. Se tudo ok, importação.

    --

    Eis artigo completo sobre importação de excel para SQL Server que encontrei no capítulo de referências do artigo “Importação de dados em massa no SQL Server”:




    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz domingo, 22 de setembro de 2019 11:17
    sábado, 21 de setembro de 2019 20:49
  • esses estão instalados

    SQLOLEDB

    MSOLAP
    SQLNCLI11
    Microsoft.ACE.OLEDB.12.0
    Microsoft.ACE.OLEDB.15.0
    ADsDSOObject
    Search.CollatorDSO
    MSDASQL
    MSDAOSP

    sábado, 21 de setembro de 2019 21:11
  • Garibaldo,

    Por padrão o Microsoft.Jet.OLEDB.4.0 é compatível com as versões 97/2003/2007 do Microsoft Office, a partir da versão 2010 a Microsoft começou a introduzir novas versões.

    Uma validação que você poderia fazer além das indicadas anteriormente pelo José Diz, é se o seu arquivo, mesmo criado em uma versão mais atual do Excel, não esteja apresentando uma estrutura compatível com as versões citadas acima.

    É muito comum mesmo nas versões mais atuais, criar arquivos com nível de compatibilidade para versões anteriores, inclusive neste semana em um cliente, tive um cenário justamente similar ao seu.

    Abra novamente este arquivo no Excel, e salve com a referida extensão .xlsx para utilizer o Microsoft.ACE.OLEDB.12.0 ou .XLS para Microsoft.Jet.OLEDB.4.0.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 21 de setembro de 2019 21:12
  • Quando executo dá essa mensagem

    Mensagem 7399, Nível 16, Estado 1, Linha 3
    O provedor do OLE DB "Microsoft.ACE.OLEDB.12.0" para o servidor vinculado "PlanilhaExcel" reportou um erro. O provedor causou uma falha do servidor em um processo externo.
    Mensagem 7303, Nível 16, Estado 1, Linha 3
    Não é possível inicializar um objeto de fonte de dados do provedor do OLE DB "Microsoft.ACE.OLEDB.12.0" para o servidor vinculado "PlanilhaExcel".

    sábado, 21 de setembro de 2019 21:13
  • esses estão instalados
    ...

    Ok. O Jet 4.0 não está instalado.

    O SQL Server também é de 64 bits? Aliás, qual é a versão do SQL Server e qual a versão do Windows? A dúvida é se o ACE OLE DB 12.0 que está instalado é de 32 bits ou de 64 bits.

    O Access Database Engine (ACE) está disponível nas versões 12.0, 15.0 e 16.0. No computador que testou estão instaladas as versões 12.0 e 15.0. Se quiser instalar a mais recente, acesse Access Database Engine 2016 mas selecione a compatível com o SQL Server, com relação a arquitetura do processador: 32 ou 64 bits. Logo após execute o código #2 (uma única vez).

    O ACE lê qualquer formato padrão de planilha do Excel, tanto as no formato BIFF8 (geradas como padrão no Office 97 a 2003 e geralmente com extensão .xls) quanto as no formato BIFF12 (geradas como padrão a partir do Office 2007 e geralmente com extensão .xlsx). Não testei ainda se abre planilhas no formato ODS ou as planilhas do excel anteriores ao Office 97.

    Sugestão de leitura: Formatos de arquivos com suporte no Excel.

    ---

    A planilha contém primeira linha com cabeçalhos? Se não tiver cabeçalhos, substitua
       HDR=YES
    por
      HDR=NO

    no código #3.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    sábado, 21 de setembro de 2019 21:40