Trigger e Procedure não funciona via Linked Server

Question

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!



Answer 1

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]

Answer 2

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

a 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

Answer 3

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]

Answer 4

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. 

Answer 5

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:

  

  

• Disable all MSDTC security on both machines:

  

  

• Turn on random options on the linked server:

  

• 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]

Answer 6

Pedro, seguem as respostas:

Answer 7

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?

Answer 8

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"

Answer 9

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?

Answer 10

Tente executar as instruções linked server utilizando OPENQUERY.

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

Answer 11

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.

Answer 12

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.

Answer 13

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.

Answer 14

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]

Answer 15

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'

Answer 16

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]

Answer 17

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