none
Trigger e Procedure não funciona via Linked Server RRS feed

  • Pergunta

  • Boa tarde amigos, como vão?

    Estou com um problema um pouco específico. 

    Tenho dois bancos de dados. 

    Do banco A pro banco B, eu consigo fazer toda e qualquer transação via Linked Server.

    Do banco B pro banco A, consigo fazer update manualmente. Porém, quando um update está em uma Trigger ou Procedure, ele depois de um tempo exibe a seguinte mensagem:

    "OLE DB provider "SQLNCLI10" for linked server "xxxxx" returned message "No transaction is active.".

    Msg 7391, Level 16, State 2, Procedure TestaLinkedServer, Line 13
    The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "xxxxx" was unable to begin a distributed transaction."

    Já verifiquei alguns problemas parecidos, mas todas as configurações estão feitas, em ambos os lados (DTC Local tudo marcado e serviço startado).

    Alguém poderia me dar um auxílio nessa questão?

    Abraços!

    

    sexta-feira, 28 de agosto de 2015 17:59

Respostas

  • Ivan,

    Ok, não precisa me chamar de professor longe de mim!!!

    As versões de SQL Server entre as pontas são as mesmas?

    Por acaso você esta utilizando o comando Begin Distributed Transaction?

    Veja se este passo á passo ajuda:

    • Ensure Distributed Transaction Coordinator service is running on both machies:

      enter image description here

      enter image description here

    • Disable all MSDTC security on both machines:

      enter image description here

      enter image description here

    • Turn on random options on the linked server:

      enter image description here

    • Cursed and swore.

    • Smashed things.

    • Checked that a SELECT can use the linked server:

         SELECT * FROM CMSLive.CashierManagementSystem.dbo.Users
         ....
      
         (763 row(s) affected)
    • Checked that client server can ping the remote server:

          C:\Documents and Settings\avatar>ping wclcmstest.windsor.wcl
      
          Pinging wclcmstest.windsor.wcl [10.180.12.40] with 32 bytes of data:
      
          Reply from 10.180.12.40: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.40: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.40: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.40: bytes=32 time<1ms TTL=128
      
          Ping statistics for 10.180.12.40:
              Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
          Approximate round trip times in milli-seconds:
              Minimum = 0ms, Maximum = 0ms, Average = 0ms
    • Checked that the remote server can commnicate back, by name, to the initiating server:

          C:\Documents and Settings\avatar>ping wcltestserver.windsor.wcl
      
          Pinging wcltestserver.windsor.wcl [10.180.12.22] with 32 bytes of data:
      
          Reply from 10.180.12.22: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.22: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.22: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.22: bytes=32 time<1ms TTL=128
      
          Ping statistics for 10.180.12.22:
              Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
          Approximate round trip times in milli-seconds:
              Minimum = 0ms, Maximum = 0ms, Average = 0ms
    • Checked that @@SERVERNAME matches the server name on both servers:

        SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
      
        -------------  -------------
        WCLTESTSERVER  WCLTESTSERVER

      and

        SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
      
        ----------  ----------
        WCLCMSTEST  WCLCMSTEST
    • Screamed

    • Issued SET XACT_ABORT ON before issuing my query:

      SET XACT_ABORT ON
      GO
      BEGIN DISTRIBUTED TRANSACTION
      SELECT TOP 1 * FROM Sessions
    • Granted Everyone Full Control to:

      HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

      on both servers.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 1 de setembro de 2015 15:11
    Moderador
  • Galera, desculpe a demora, eu estive com esse projeto parado um tempo e estou retomando. Ainda não efetuei o teste solicitado pelo Pedro. Mas consegui um log de atividade após eu disparar uma trigger e dentro dela, efetuar um update remoto sem sucesso. Segue o link:

    http://www.4shared.com/zip/ta5zN6usba/trace.html

    quinta-feira, 8 de outubro de 2015 20:02

Todas as Respostas

  • Ivan,

    Você já verificou se o seu SQL Server esta configurado para trabalhar com transações distribuídas?

    Através da sp_Configure, habilite as opções:

    ad hoc distributed queries

    remote proc trans

    A conta de usuário que você esta utilizando neste linked server tem permissão de acesso em ambos os servidores?


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    domingo, 30 de agosto de 2015 02:15
    Moderador
  • Ivan,

    Você já verificou se o seu SQL Server esta configurado para trabalhar com transações distribuídas?

    Através da sp_Configure, habilite as opções:

    ad hoc distributed queries

    remote proc trans

    A conta de usuário que você esta utilizando neste linked server tem permissão de acesso em ambos os servidores?


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    Bom dia Pedro, obrigado pela ajuda.

    Pedro, você me passou dois links que estão redirecionando para a página que já estou. Eles estão certos?

    Abraços!

    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    Rodei a sp_Configure e ela me trouxe o seguinte resultado para a remote proc trans:

    Minimum Maximum Config_value run_Value

    remote proc trans 0 1 0 0

    ad hoc distributed queries não tem na lista. Segue a lista dos resultados (coluna "name"):

    allow updates
    backup compression default
    clr enabled
    cross db ownership chaining
    default language
    filestream access level
    max text repl size (B)
    nested triggers
    remote access
    remote admin connections
    remote login timeout (s)
    remote proc trans
    remote query timeout (s)
    server trigger recursion
    show advanced options
    user options

    • Editado ivan_castro segunda-feira, 31 de agosto de 2015 12:06
    segunda-feira, 31 de agosto de 2015 12:01
  • Ivan,

    Acabei errando os links(kkkkk).

    Você deve ativar ambos as opções que eu te passei, provavelmente as opções ad hoc distributed query e  remote proc trans estão desativadas!!!

    Veja agora estes links:

    https://technet.microsoft.com/en-us/library/ms187569(v=sql.105).aspx

    https://msdn.microsoft.com/en-us/library/ms190773.aspx


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    segunda-feira, 31 de agosto de 2015 18:32
    Moderador
  • Tranquilo professor. Só de ter alguém ajudando já me adianta bastante.

    Depois efetuar os procedimentos (em ambos os bancos de dados), ficaram assim as duas linhas na sp_configure:

    (minimun, maximun, config_value, run_value)

    Ad Hoc Distributed Queries 0 1 1 1
    Ole Automation Procedures 0 1 0 0

    Mesmo assim, ainda continua dando o mesmo erro. 

    terça-feira, 1 de setembro de 2015 13:40
  • Ivan,

    Ok, não precisa me chamar de professor longe de mim!!!

    As versões de SQL Server entre as pontas são as mesmas?

    Por acaso você esta utilizando o comando Begin Distributed Transaction?

    Veja se este passo á passo ajuda:

    • Ensure Distributed Transaction Coordinator service is running on both machies:

      enter image description here

      enter image description here

    • Disable all MSDTC security on both machines:

      enter image description here

      enter image description here

    • Turn on random options on the linked server:

      enter image description here

    • Cursed and swore.

    • Smashed things.

    • Checked that a SELECT can use the linked server:

         SELECT * FROM CMSLive.CashierManagementSystem.dbo.Users
         ....
      
         (763 row(s) affected)
    • Checked that client server can ping the remote server:

          C:\Documents and Settings\avatar>ping wclcmstest.windsor.wcl
      
          Pinging wclcmstest.windsor.wcl [10.180.12.40] with 32 bytes of data:
      
          Reply from 10.180.12.40: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.40: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.40: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.40: bytes=32 time<1ms TTL=128
      
          Ping statistics for 10.180.12.40:
              Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
          Approximate round trip times in milli-seconds:
              Minimum = 0ms, Maximum = 0ms, Average = 0ms
    • Checked that the remote server can commnicate back, by name, to the initiating server:

          C:\Documents and Settings\avatar>ping wcltestserver.windsor.wcl
      
          Pinging wcltestserver.windsor.wcl [10.180.12.22] with 32 bytes of data:
      
          Reply from 10.180.12.22: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.22: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.22: bytes=32 time<1ms TTL=128
          Reply from 10.180.12.22: bytes=32 time<1ms TTL=128
      
          Ping statistics for 10.180.12.22:
              Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
          Approximate round trip times in milli-seconds:
              Minimum = 0ms, Maximum = 0ms, Average = 0ms
    • Checked that @@SERVERNAME matches the server name on both servers:

        SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
      
        -------------  -------------
        WCLTESTSERVER  WCLTESTSERVER

      and

        SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
      
        ----------  ----------
        WCLCMSTEST  WCLCMSTEST
    • Screamed

    • Issued SET XACT_ABORT ON before issuing my query:

      SET XACT_ABORT ON
      GO
      BEGIN DISTRIBUTED TRANSACTION
      SELECT TOP 1 * FROM Sessions
    • Granted Everyone Full Control to:

      HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

      on both servers.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 1 de setembro de 2015 15:11
    Moderador
  • Pedro, seguem as respostas:


    terça-feira, 1 de setembro de 2015 20:03
  • Continua:

    SERVIDOR:

    SELECT * FROM CMSLive.CashierManagementSystem.dbo.Users

    Msg 233, Level 20, State 0, Line 0

    A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

    LOCAL:

    SELECT * FROM CMSLive.CashierManagementSystem.dbo.Users

    Msg 7202, Level 11, State 2, Line 1

    Could not find server 'CMSLive' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

    SERVIDOR:

    SELECT * FROM [ELSRIOW-002246].CashierManagementSystem.dbo.Users

    Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "SQLNCLI10" for linked server "ELSRIOW-002246" does not contain the table ""CashierManagementSystem"."dbo"."Users"". The table either does not exist or the current user does not have permissions on that table.

    LOCAL:

    SELECT * FROM [ELSRIOSQLP001\NAVISION_HMLG].CashierManagementSystem.dbo.Users

    Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "SQLNCLI10" for linked server "ELSRIOSQLP001\NAVISION_HMLG" does not contain the table ""CashierManagementSystem"."dbo"."Users"". The table either does not exist or the current user does not have permissions on that table.

    SERVIDOR:

    C:\Users\fun-scielsriosql001>ping ELSRIOW-002246

    Pinging elsriow-002246.science.regn.net [10.47.56.73] with 32 bytes of data:

    Reply from 10.47.56.73: bytes=32 time=1ms TTL=126

    Reply from 10.47.56.73: bytes=32 time<1ms TTL=126

    Reply from 10.47.56.73: bytes=32 time<1ms TTL=126

    Reply from 10.47.56.73: bytes=32 time<1ms TTL=126

    Ping statistics for 10.47.56.73:

        Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

    Approximate round trip times in milli-seconds:

        Minimum = 0ms, Maximum = 1ms, Average = 0ms

    LOCAL:

    C:\Users\castroi001>ping 10.47.59.25

    Disparando 10.47.59.25 com 32 bytes de dados:

    Resposta de 10.47.59.25: bytes=32 tempo=1ms TTL=126

    Resposta de 10.47.59.25: bytes=32 tempo<1ms TTL=126

    Resposta de 10.47.59.25: bytes=32 tempo=1ms TTL=126

    Resposta de 10.47.59.25: bytes=32 tempo<1ms TTL=126

     

    Estatísticas do Ping para 10.47.59.25:

        Pacotes: Enviados = 4, Recebidos = 4, Perdidos = 0 (0% de

                 perda),

    Aproximar um número redondo de vezes em milissegundos:

        Mínimo = 0ms, Máximo = 1ms, Média = 0ms

     

     

     

     

    SERVIDOR:

    SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')

    ELSRIOSQLP001\NAVISION_HMLG          ELSRIOSQLP001

    LOCAL:

    SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')

    ELSRIOW-002246              ELSRIOW-002246

    SET XACT_ABORT ON

    Utilizado e persistiu o erro.

     

    Sobre o: Granted Everyone Full Control to:

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

    Não conheço o procedimento. Teria como me passar?


    • Editado ivan_castro terça-feira, 1 de setembro de 2015 20:07
    terça-feira, 1 de setembro de 2015 20:06
  • Ivan,

    Apenas complementando às informações do Junior, eu acredito que este problema possa estar relacionado à configuração no servidor de "origem" da procedure.

    Segue abaixo um link com um artigo interessante sobre como identificar e solucionar o problema:

    http://social.technet.microsoft.com/wiki/contents/articles/5141.solucionando-problemas-no-transaction-is-active-no-sql-server-pt-br.aspx


    Se ajudou na sua solução, não esqueça de marcar como resposta !


    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    terça-feira, 1 de setembro de 2015 20:17
    Moderador
  • Obrigado Durval, pela auda.

    Segue resposta do procedimento:

    C:\Windows\System32\Msdtc\Trace>msdtcvtr -tracelog dtctrace.log.2015-09-02-10-47
    -37-0618-00 -o c:\traceDTC
    Failed to convert the binary trace data to text format.
    Following reasons can cause this to happen:
    1) The utility TraceFmt.exe is missing
    2) The file dtctrace.log.2015-09-02-10-47-37-0618-00 is either missing or corrup
    ted
    3) The file C:\Windows\system32\msdtc\trace\msdtctr.mof is either missing or cor
    rupted
    The exact error message can be found in the file 'C:\Windows\system32\msdtc\trac
    e\errortrace.txt'

    Ai nesse errortrace.txt tem a mensagem:

    'tracefmt' is not recognized as an internal or external command,
    operable program or batch file.

    Para este erro eu li que será preciso instalar um Windows Kit. Fazer instalação em um servidor é algo complexo. O setor de infra da empresa, por ser Internacional, é bem burocrático. Teria alguma outra forma de efetuar esse procedimento?

    quarta-feira, 2 de setembro de 2015 17:39
  • Tente executar as instruções linked server utilizando OPENQUERY.

    https://msdn.microsoft.com/pt-br/library/ms188427(v=SQL.120).aspx

    quinta-feira, 10 de setembro de 2015 01:59
  • Opa, cara, infelizmente usando OPENQUERY deu o mesmo erro:

    OLE DB provider "SQLNCLI10" for linked server "ELSRIOW-002246" returned message "No transaction is active.".
    Msg 7391, Level 16, State 2, Procedure PEDIDO_INSERT_SITE, Line 31
    The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ELSRIOW-002246" was unable to begin a distributed transaction.

    segunda-feira, 14 de setembro de 2015 19:27
  • Ivan, como você está utilizando a função OPENQUERY?

    Poderia postar a query?

    Pergunto, pois tive um problema semelhante e só consegui executar uma procedure via linked server utilizando OPENQUERY.

    terça-feira, 15 de setembro de 2015 02:01
  • claro:

    UPDATE OPENQUERY ([201.20.24.203], 'select email from elsevier_homolog.dbo.pessoababylon where pessoaid = 189234') 
    SET email = 'UpdateTriggerOpenQuery';

    É o mesmo problema já citado: se eu executo manualmente, ela roda. O erro só ocorre dentro da Trigger ou Procedure.

    terça-feira, 15 de setembro de 2015 13:39
  • Ivan,

    Aparentemente esta ocorrendo uma troca no contexto conexão do usuário quando o trigger ou a procedure é chamado.

    Neste caso, tente utilizar o comando Execute As no seu Trigger ou Stored Procedure. 


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 15 de setembro de 2015 17:49
    Moderador
  • Bom dia Pedro, obrigado pela colaboração.

    Eu efetuei a mudança, na procedure que fica no banco de dados de destino, logo abaixo da declaração de variaveis, mas continuou o mesmo erro.

    Exemplo:

    WITH EXECUTE AS 'UserDoBancoDeDestino'

    quarta-feira, 16 de setembro de 2015 14:12
  • Ivan,

    Então, faça um teste. Você consegui se conectar no Servidor de Destino, acessar o banco de dados de destino e rodar este Stored Procedure com o login que esta utilizando no Execute As?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 18 de setembro de 2015 18:16
    Moderador
  • Galera, desculpe a demora, eu estive com esse projeto parado um tempo e estou retomando. Ainda não efetuei o teste solicitado pelo Pedro. Mas consegui um log de atividade após eu disparar uma trigger e dentro dela, efetuar um update remoto sem sucesso. Segue o link:

    http://www.4shared.com/zip/ta5zN6usba/trace.html

    quinta-feira, 8 de outubro de 2015 20:02