Início / dba / Perguntas / 185148
Accepted
jesijesi
Asked: 2017-09-06 18:02:27 +0800 CST

Selecionando e inserindo no servidor vinculado

  • 772

Estou tendo uma situação com o servidor vinculado que não consigo entender.

Portanto, temos um servidor vinculado de um servidor 2008R2 para um servidor 2014. A consulta de exemplo abaixo está sendo executada no servidor 2008R2 e funciona bem.

SET XACT_ABORT ON;
 Declare @BatchSize int = 10
      DELETE from LINKEDSRV.DB.DBO.Table
      INSERT INTO LINKEDSRV.DB.DBO.Table (ECN)
      SELECT TOP (10) C1 from LINKEDSRV.DB.DBO.Table22 --order by C1
      SELECT * FROM LINKEDSRV.DB.DBO.Table

Mas quando executo a mesma coisa com o order by C1ele não retorna nenhum resultado.

Segundo caso - Se eu substituir TOP(10)por TOP(@BatchSize)e não order by, também não obtenho nenhum resultado. por exemplo

SELECT TOP (@BatchSize) C1 from LINKEDSRV.DB.DBO.Table22

Todos os cenários funcionam se eu SET XACT_ABORT OFF. Então o XACT_ABORT tem algum tipo de restrição quando se trata de servidor vinculado?

Editar - fiz mais alguns testes e parece que tem algo a ver com o número de linhas também

Possível Repo

No Servidor A

use testdb
go
create table t1( c1 int, c2 datetime)
create table t2( c1 int, c2 datetime)

insert into t2 select 1, GETDATE()
insert into t2 select  * from t2 -- insert close to 5000 rows, I found the issue around over around 35000 rows

No Servidor B

Criar servidor vinculado ao ServerA

SET XACT_ABORT ON;
Declare @BatchSize int = 10
delete from ServerA.testdb.dbo.t1
insert into ServerA.testdb.dbo.t1 (c1) 
select top (@BatchSize) c1 from ServerA.testdb.dbo.t2 --order by c2
select * from ServerA.testdb.dbo.t1

Sem saída. Mas se você reduzir o número de linhas na tabela t2 para cerca de 2000, funcionará bem.

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Todos os cenários funcionam se eu SET XACT_ABORT OFF. Então XACT_ABORTtem algum tipo de restrição quando se trata de servidor vinculado?

    A configuração de XACT_ABORTé propagada para o SQL Server remoto, conforme documentado em Manipulando Erros em Procedimentos Armazenados Remotos de Servidor para Servidor .

    A configuração também afeta se as atualizações são possíveis e como elas são tratadas, conforme documentado em Consultas Distribuídas e Transações Distribuídas . Seu INSERTé permitido XACT_ABORT OFFporque o SQL Server oferece suporte a transações aninhadas.

    No entanto, parece haver um bug de implementação, porque rastrear a atividade no servidor 2014 durante a inserção revela que ocorre um erro quando o SQL Server tenta liberar um bloqueio de esquema:

    trecho de rastreamento

    Esse erro anula a instrução (tentando liberar o bloqueio de esquema) no servidor remoto, mas quando XACT_ABORTé OFF, o servidor remoto continua processando a próxima instrução. A inserção é concluída, apesar do erro de liberação de bloqueio de esquema.

    Quando XACT_ABORTé ON, todo o lote remoto é abortado, então a inserção é revertida.

    Consegui reproduzir seu problema localmente, mas a ORDER BYcláusula não era significativa.

    Você pode evitar o problema de várias maneiras, inclusive envolvendo o INSERTem uma transação explícita (supondo que o DTC esteja disponível para você).

    Eu recomendo que você evite a sintaxe de nome de quatro partes para alterações remotas, pois a implementação é baseada em um modelo de cursor. Normalmente, você obterá um melhor desempenho usando um método em massa ou puxando os dados no servidor remoto (em vez de enviá -los do servidor local).

    Veja as perguntas e respostas relacionadas. Qual é mais eficiente: selecionar no servidor vinculado ou inserir no servidor vinculado?

    • 4

  2. Na minha experiência, a execução de comandos de várias partes através do servidor vinculado geralmente enfrenta problemas, especialmente quando as tabelas são grandes. Tente isto:

    SET XACT_ABORT ON;
Declare @BatchSize int = 10
delete from ServerA.testdb.bbo.t1
select top (@BatchSize) c1 INTO #t2temp from ServerA.testdb.dbo.t2
insert into ServerA.testdb.dbo.t1 (c1) 
SELECT * FROM #t2temp
select * from ServerA.testdb.dbo.t1
    • 1

relate perguntas