none
Falha ao usar o OpenRowSet para ler arquivos XLSX em diretório compartilhado. RRS feed

  • Pergunta

  • Bom dia Prezados(as),

    Estou tento um problema na empresa para ler arquivos XLSX utilizando o OpenRowSet, sendo que esse arquivo XLSX está em um diretório compartilhado. O meu maior problema nesse caso foi o seguinte, quando estou na máquina funciona, mas quando faço da minha máquina (SSMS) não funciona.

    Fiz alguns testes e fiz algumas verificações:

    1-Quando tento ler esse arquivo com XP_CMDSHELL 'TYPE "\\IP\DIRETORIOS\ARQUIVO.XLSX"' funciona, e consigo ler o arquivo;
    2-Se coloco o arquivo no C: do servidor e tento ler "remotamente" funciona o OpenRowSet;
    3-Já esta com Dynamic parameter e Allow inprocess habilitado, também entrei no regedit e desabilitei o Disallow adhoc access;
    4-Habilitei o Ad Hoc Distributed Queries no sp_configure;
    5-Também entrei no regedit e dei a permissão necessárias para a chave {2206CDB0-19C1-11D1-89E0-00C04FD7A829};
    6-Fui nos componentes de serviços e dei as permissões para o acesso remoto no MSDAINITIALIZE;
    7-O usuário que estou usando é sysadmin;
    8-Abaixo tem a query que usei:

    SELECT *
    FROM OPENROWSET
     ('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0;HDR=YES;DATABASE=\\IP\DIRETORIOS\ARQUIVO.XLSX',
      'SELECT * FROM [Dados$]')
    WHERE [CODIGO] IS NOT NULL;

    9-A mensagem de erro que apresenta no retorno remoto é a seguinte:

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Observação: Já verifiquei que não é permissão em diretório, estão todos corretos, estou com usuário SYSADMIN, então dei permissão para a conta de serviço do SQL Server e o usuário que estou utilizando. A versão do SQL Server é a 2008 - Build 10.0.6000, Edição Standard Edition 64-bit.

    Se alguém tiver alguma dica para me ajudar a solucionar esse problema eu agradeço.

    Desde já agradeço.

    []'s


    • Editado Caio Morais terça-feira, 23 de abril de 2019 16:42 Tecnologia errada
    terça-feira, 23 de abril de 2019 13:34

Todas as Respostas

  • Caio,

    Isso esta me parecendo regras de firewall!

    Quando você se referi a remote client, não esta falando em abrir uma área de trabalho remota? Eu imagino que não, imagino, que você esta tentando via rede fazer o acesso.

    De que forma na sua máquina você esta tentando acessar?


    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]

    terça-feira, 23 de abril de 2019 13:58
  • Deleted
    terça-feira, 23 de abril de 2019 14:18
  • Via SSMS, não Remote Client, editei na explicação.

    O Firewall eu não tenho expertise para analisar, sabe me informar como posso avaliar se esse é o problema?

    terça-feira, 23 de abril de 2019 16:51
  • Caio,

    Certo, via SSMS não esta tendo o acesso!

    Verifique se as portas 1433 no protocolo TCP/IP estão liberada, bem como, a porta 1434 no protoloco UDP.

    Além disso, é necessário que o serviço SQL Browser esteja habilitado, bem como, os protolocos Named Pipes e Shared Memory.


    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]

    terça-feira, 23 de abril de 2019 16:54
  • Galvão,

    Os serviços e o protocolos estão habilitados, a porta que utilizamos não é a padrão 1433 e 1434, devido a característica de negocio tivemos que usar outra porta, mas consegui fechar comunicação através do telnet.

    Entre minha máquina a instancia não possui nenhum filtro no firewall, verifiquei com o responsável pela infra.

    terça-feira, 23 de abril de 2019 17:12
  • Diz,

    É IP Privado (Rede Local).

    terça-feira, 23 de abril de 2019 17:13
  • Caio,

    Ok, perfeito, que bom que eles não utilizam portas padrões.

    Bom, se via telnet a comunicação esta ocorrendo, temos que analisar agora como o sua máquina via Management Studio é reconhecida pelo servidor de destino.

    As configurações e regras de roteamento do seu IP, existe alguma configuração?


    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]

    terça-feira, 23 de abril de 2019 17:15
  • Galvão,

    Elas estão dentro do mesmo escopo de endereçamento IP, devido a isso não existe roteamento entre elas.

    terça-feira, 23 de abril de 2019 17:22
  • Deleted
    terça-feira, 23 de abril de 2019 17:35
  • Diz,

    Acessei minha estação de trabalho (Meu computador que tem apenas um SSMS instalado), me conectei a instancia (instancia nomeada, a que irá executar os script).

    Somente descobri que estava tendo esse problema quando comecei a desenvolver a Procedure e vi que não estava funcionando o OpenRowSet da minha máquina, quando entrei na máquina para testar funcionou.

    terça-feira, 23 de abril de 2019 17:43
  • Galvão,

    Elas estão dentro do mesmo escopo de endereçamento IP, devido a isso não existe roteamento entre elas.

    Caio,

    Perfeito, vamos deixar de lado, então a parte de rede, roteamento e firewall.


    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]

    terça-feira, 23 de abril de 2019 17:59
  • Caio,

    Com base, no que você conversou com o José Diz, a pergunta do José Diz é importante, a forma de autenticação e conta de login ou usuário utilizada poderá nos definr a solução.

    A sua mensagem de erro também informa o uso do Linked Server para estabelecer a conexão com o Excel, neste linked server o usuário utilizado para ele é o mesmo que esta sendo utilizado no SSMS?

    A versão do provider OLEDB instalado na sua máquina é o mesmo do servidor?

    Você saberia dizer se a conta que esta rodando o serviço do SQL Server é uma conta local?


    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]




    terça-feira, 23 de abril de 2019 18:01
  • Caio,

    Nas configurações deste Linked Server, na guia Segurança, o que esta configurado:

    Da uma olhada neste link: https://support.microsoft.com/en-us/help/2450479/you-get-7399-and-7300-error-messages-when-accessing-a-linked-server


    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]


    terça-feira, 23 de abril de 2019 18:13
  • Caio,

    Com base, no que você conversou com o José Diz, a pergunta do José Diz é importante, a forma de autenticação e conta de login ou usuário utilizada poderá nos definr a solução.

    A sua mensagem de erro também informa o uso do Linked Server para estabelecer a conexão com o Excel, neste linked server o usuário utilizado para ele é o mesmo que esta sendo utilizado no SSMS?

    A versão do provider OLEDB instalado na sua máquina é o mesmo do servidor?

    Você saberia dizer se a conta que esta rodando o serviço do SQL Server é uma conta local?


    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]




    Não é utilizado LinkedServer, essa é uma mensagem padrão do SQL Server.

    Esse Print é quando executo da minha máquina:


    Esse Print é quando executo diretamente do servidor:

    Lembrando, se eu movimento esse mesmo arquivo para o C:\ do servidor ele enxerga, e consegue ler o XLSX. O mapeamento de rede esta com as permissões corretas, por que ao executar (exec XP_CMDSHELL 'type "\\IP\Diretorio\arquivo.xlsx"') esta sendo executado com sucesso.

    terça-feira, 23 de abril de 2019 18:25
  • Caio,

    Nas configurações deste Linked Server, na guia Segurança, o que esta configurado:

    Da uma olhada neste link: https://support.microsoft.com/en-us/help/2450479/you-get-7399-and-7300-error-messages-when-accessing-a-linked-server


    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]


    Não existe LinkedServer.
    terça-feira, 23 de abril de 2019 18:26
  • Caio,

    Então, era isso mesmo que eu queria saber da sua parte!

    Podemos pensar na possibilidade de criar um linked server justamente para ver o comportamento na sua máquina.


    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]

    quarta-feira, 24 de abril de 2019 00:10
  • Bom dia Galvão,

    Já havia realizado esse teste, e tem o mesmo comportamento, na minha máquina não funciona e usando o linkedserver no servidor funciona.

    quarta-feira, 24 de abril de 2019 13:29
  • Hoje pela manhã achei que poderia ser alguma permissão especial que não tinha no diretório, ai liberei o "Everyone" e testei para verificar se iria funcionar, e não deu certo... mesmo comportamento.

    quarta-feira, 24 de abril de 2019 13:42
  • Caio,

    Verifique se este arquivo msdaps.dll, esta armazenado na sua máquina e em qual local o mesmo se encontra no servidor.



    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]

    quarta-feira, 24 de abril de 2019 14:23
  • Caio,

    Verifique se este arquivo msdaps.dll, esta armazenado na sua máquina e em qual local o mesmo se encontra no servidor.



    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]

    Galvão, estes arquivos se encontram nos seguintes diretórios:

    No servidor:

    c:\>dir msdaps.dll /s /p
     Volume in drive C has no label.
     Volume Serial Number is 24FE-1D1C
    
     Directory of c:\Program Files\Common Files\System\Ole DB
    
    21/11/2014  22:43           421.888 msdaps.dll
                   1 File(s)        421.888 bytes
    
     Directory of c:\Program Files (x86)\Common Files\System\Ole DB
    
    21/11/2014  22:42           261.632 msdaps.dll
                   1 File(s)        261.632 bytes
    
     Directory of c:\Windows\WinSxS\amd64_microsoft-windows-m..terface-remoting-ps_3
    1bf3856ad364e35_6.3.9600.16384_none_dd345b0a97db8f7b
    
    22/11/2014  03:02            82.923 msdaps.dll
                   1 File(s)         82.923 bytes
    
     Directory of c:\Windows\WinSxS\amd64_microsoft-windows-m..terface-remoting-ps_3
    1bf3856ad364e35_6.3.9600.17415_none_dd80f5e697a1fc03
    
    21/11/2014  22:43           421.888 msdaps.dll
                   1 File(s)        421.888 bytes
    Press any key to continue . . .
    
     Directory of c:\Windows\WinSxS\x86_microsoft-windows-m..terface-remoting-ps_31b
    f3856ad364e35_6.3.9600.16384_none_8115bf86df7e1e45
    
    22/11/2014  04:34            52.233 msdaps.dll
                   1 File(s)         52.233 bytes
    
     Directory of c:\Windows\WinSxS\x86_microsoft-windows-m..terface-remoting-ps_31b
    f3856ad364e35_6.3.9600.17415_none_81625a62df448acd
    
    21/11/2014  22:42           261.632 msdaps.dll
                   1 File(s)        261.632 bytes
    
         Total Files Listed:
                   6 File(s)      1.502.196 bytes
                   0 Dir(s)  72.018.964.480 bytes free

    Na minha maquina:

    c:\>dir msdaps.dll /s /p
     O volume na unidade C não tem nome.
     O Número de Série do Volume é 0CB1-6091
    
     Pasta de c:\Program Files\Common Files\system\ole db
    
    11/04/2018  20:34           374.784 msdaps.dll
                   1 arquivo(s)        374.784 bytes
    
     Pasta de c:\Program Files (x86)\Common Files\system\ole db
    
    11/04/2018  20:34           251.904 msdaps.dll
                   1 arquivo(s)        251.904 bytes
    
     Pasta de c:\Windows\WinSxS\amd64_microsoft-windows-m..terface-remoting-ps_31bf3856ad364e35_10.0.17134.1_none_d1f687572d0eeef1
    
    11/04/2018  20:34           374.784 msdaps.dll
                   1 arquivo(s)        374.784 bytes
    
     Pasta de c:\Windows\WinSxS\x86_microsoft-windows-m..terface-remoting-ps_31bf3856ad364e35_10.0.17134.1_none_75d7ebd374b17dbb
    
    11/04/2018  20:34           251.904 msdaps.dll
                   1 arquivo(s)        251.904 bytes
    
         Total de Arquivos na Lista:
                   4 arquivo(s)      1.253.376 bytes
                   0 pasta(s)   365.695.758.336 bytes disponíveis


    quarta-feira, 24 de abril de 2019 15:05
  • Caio,

    Os locais de armazenamento estão corretos, o que esta me chamando a atenção é justamente a diferença de tamanho dos arquivos e dados, aparentemente estamos se deparando com versões diferentes.

    Qual é a versão do drivers ODBCs instalados no Servidor?

    E qual é a versão do SQL Server Native Client instalados na sua máquina e servidor?


    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]

    quarta-feira, 24 de abril de 2019 18:29
  • Caio,

    Os locais de armazenamento estão corretos, o que esta me chamando a atenção é justamente a diferença de tamanho dos arquivos e dados, aparentemente estamos se deparando com versões diferentes.

    Qual é a versão do drivers ODBCs instalados no Servidor?

    E qual é a versão do SQL Server Native Client instalados na sua máquina e servidor?


    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]

    Galvão,

    Estavam em versão diferente realmente (ODBC Microsoft Excel Driver - minha máquina versão 16, no servidor versão 12), instalei a mesma versão que estou usando no servidor e não resolveu.

    O SQL Server Native Client é a 10 em ambos.


    • Editado Caio Morais quarta-feira, 24 de abril de 2019 20:49
    quarta-feira, 24 de abril de 2019 20:39
  • Caio,

    Poxa, pergunta besta, tem Excel na sua máquina e no servidor?

    Se sim qual versão!!!

    Por acaso você teria alguma outra máquina similar as configurações da sua para testar?


    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]

    quarta-feira, 24 de abril de 2019 22:36
  • Caio,

    Poxa, pergunta besta, tem Excel na sua máquina e no servidor?

    Se sim qual versão!!!

    Por acaso você teria alguma outra máquina similar as configurações da sua para testar?


    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]

    Bom dia Galvão,

    Minha máquina tem sim Excel, o Servidor não tem, já que é o servidor de SQL Server. Todos da empresa possuem o Office 365 E3. Já testei em várias máquinas e não funcionou em nenhuma.

    quinta-feira, 25 de abril de 2019 11:28
  • Caio,

    Ótimo, faça um teste, tem como instalar uma versão do Excel 2016 ou 2013, os produtos Office 365 E3 são versões corporativas, na última empresa que trabalhei em Sorocaba, também utilizavamos e tinhamos cenário com algumas incompatibilidades com o SQL Server nas versões 2008, R2 e 2012.

    Será que você teria como fazer este teste em outra máquina, que não possui o Office 365 e instalar uma versão do Excel?


    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]

    quinta-feira, 25 de abril de 2019 12:13
  • Caio,

    Ótimo, faça um teste, tem como instalar uma versão do Excel 2016 ou 2013, os produtos Office 365 E3 são versões corporativas, na última empresa que trabalhei em Sorocaba, também utilizavamos e tinhamos cenário com algumas incompatibilidades com o SQL Server nas versões 2008, R2 e 2012.

    Será que você teria como fazer este teste em outra máquina, que não possui o Office 365 e instalar uma versão do Excel?


    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]

    Vou solicitar a criação dessa máquina nesse senário para testar, mas não creio que irá funcionar, já que o Provider que deveria ser utilizado é o que se encontra no servidor SQL Server.

    Basicamente gero uma requisição via SSMS da minha máquina para o Servidor (Instancia) e o recurso é utilizado do server e não da minha máquina.

    Mas não custa tentar.

    quinta-feira, 25 de abril de 2019 14:47
  • Caio,

    Ótimo, faça um teste, tem como instalar uma versão do Excel 2016 ou 2013, os produtos Office 365 E3 são versões corporativas, na última empresa que trabalhei em Sorocaba, também utilizavamos e tinhamos cenário com algumas incompatibilidades com o SQL Server nas versões 2008, R2 e 2012.

    Será que você teria como fazer este teste em outra máquina, que não possui o Office 365 e instalar uma versão do Excel?


    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]

    Vou solicitar a criação dessa máquina nesse senário para testar, mas não creio que irá funcionar, já que o Provider que deveria ser utilizado é o que se encontra no servidor SQL Server.

    Basicamente gero uma requisição via SSMS da minha máquina para o Servidor (Instancia) e o recurso é utilizado do server e não da minha máquina.

    Mas não custa tentar.


    Você não pode votar em sua própria postagemCaio,

    Ok, mas não custa tentar, pois um exemplo simples, você já deve ter utilizado o Word 2016 para abrir arquivos .pdf.

    Então, mas o Word existente no Office 365 já não trabalha da mesma forma, o comportamento dos produtos Office 365, seus demais recursos e funcionalidades são direferente das versões tradicionais e individuais.


    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]


    quinta-feira, 25 de abril de 2019 16:37
  • Bom dia Galvão,

    Montei o ambiente conforme sua orientação e não funcionou, ainda apresenta o mesmo erro.

    Estou desistindo de solucionar o problema, como ele roda local eu vou executar dentro de um Job, por quê desta maneira funciona.

    Mas mesmo assim agradeço a ajuda de todos.

    segunda-feira, 29 de abril de 2019 13:51
  • Caio,

    Ok, tentamos, obrigado pelo retorno.


    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]

    terça-feira, 30 de abril de 2019 13:15