GSerg's questions

Ao migrar nosso banco de dados do SQL Server 2012 para 2016, não podemos recriar um trabalho do SQL Agent que envolva uma etapa de script ActiveX escrita em VBScript.

A mensagem de erro sugere que 'ACTIVESCRIPTING'não é um valor permitido para o @subsystemparâmetro de sp_add_jobstep.

Eu sei que o ActiveX Scripting está marcado como "será removido em uma versão futura do SQL Server". No entanto, não consigo ver nada sobre isso ser removido nesta versão do SQL Server.

A documentação do SQL Server parece sugerir que ainda está disponível no SQL Server 2016:

• Criar uma etapa de trabalho de script ActiveX (SQL Server 2016)
• A documentação emsp_add_jobstep , atualizada em março de 2017, ainda lista @subsystem ='ACTIVESCRIPTING'como disponível.

Na verdade, foi @subsystem ='ACTIVESCRIPTING'removido secretamente de maneira não documentada ou perdemos um componente instalável que o habilita?

Existe um pacote SSIS com Requiredtransação no nível do pacote e Supportedno nível das tarefas. A parte principal do pacote é excluir os dados e inserir novos dados:

Aqui, o bloco Delete exclui tudo de seis tabelas e o bloco Parse carrega seis arquivos nas seis tabelas.

Se o Delete usar delete from dbo.table, tudo funcionará bem, mas será lento.
Mas se Delete usar truncate table dbo.tableem vez disso, a exclusão não leva tempo, mas o pacote fica bloqueado quando se trata da Parsetarefa. Ficaria sentado sem fazer nada.

Neste momento, no lado do servidor, posso ver um spid SSIS sendo bloqueado por spid -2, que é "transação distribuída órfã". Parece que usado para representar a conexão truncada. Essa conexão paira e o pacote é bloqueado pelas travas que ele colocou.

Se eu alternar o único gerenciador de conexão do SQL Server usado para RetainSameConnection = True, o pacote falhará assim que entrar na tarefa de análise . A tarefa solicita uma conexão ao gerente e o gerente responde com

[Destino do SQL Server [471]] Erro: Código de erro SSIS DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. A chamada do método AcquireConnection para o gerenciador de conexões "Connection Manager" falhou com o código de erro 0xC001A004. Pode haver mensagens de erro postadas antes disso com mais informações sobre o motivo da falha na chamada do método AcquireConnection.

Existe uma maneira de configurar o pacote para que ele use truncatee não se bloqueie?

Considere um procedimento armazenado CLR que aceita um parâmetro XML e chama outro procedimento armazenado no mesmo esquema:

[SqlFunction(DataAccess = DataAccessKind.Read,
     IsDeterministic = false, IsPrecise = true,
     SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlBoolean TestTest(SqlXml Data)
{
    using (var c = new SqlConnection("context connection=true"))
    {
        c.Open();

        using (var cmd = new SqlCommand(@"select case when exists(select 0 from [testing].WillBeCalledByCLR(@d)) then 1 else 0 end;", c))
        {
            var p = cmd.Parameters.Add("@d", SqlDbType.Xml);

            p.Direction = ParameterDirection.Input;
            if (Data.IsNull)
                { p.Value = DBNull.Value; }
            else
                { p.Value = Data; }  // Or Data.Value

            return (bool)cmd.ExecuteScalar();
        }
    }
}

Esta função CLR é visível no SQL como:

create function [testing].[TestTest] ( @data xml )
returns bit
WITH CALLED ON NULL INPUT
AS
EXTERNAL NAME [Test].[Test.Test].[TestTest]

A função armazenada que ele chama é esta:

create function testing.WillBeCalledByCLR (@x xml = null)
returns table
as
return (
  select 1 as one, 2 as two, 'three' as three
);

Nesta configuração, se eu chamar a função CLR assim:

if testing.TestTest(null) = 1
begin
  select 'Meaningful actions';
end;

ou assim:

declare @res bit = testing.TestTest(null);

ou assim:

declare @res bit;
set @res = testing.TestTest(null);

ou assim:

declare @res bit;
select @res = testing.TestTest(null);

então eu recebo:

Msg 2905, Nível 25, Estado 1, Linha 6

Msg 0, Level 20, State 0, Line 0 Ocorreu um erro grave no comando atual. Os resultados, se existirem, deveriam ser descartados.

Mas se eu chamar assim:

select testing.TestTest(null);

ou assim:

declare @res bit;
set @res = (select testing.TestTest(null));

Recebo um valor adequado de volta (por exemplo 1).

Se, ao invés de null, eu passar uma string vazia '', a função é chamada com sucesso em todos os casos.

Por quê? Fiz algo errado na minha função CLR?

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
16 de setembro de 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64 bits) no Windows NT 6.0 (Build 6002: Service Pack 2) (MV)

_{Em minha busca sem fim para dar um tiro no próprio pé com uma transação salva, parece que encontrei ainda mais maneiras de cumprir o propósito da missão. A própria cláusula de transação de salvamento desta vez está fora de questão, mas é por causa dela que escrevi o código abaixo.}

Considere o seguinte exemplo completo com manipuladores de erro aninhados:

begin try

  begin try
    select 'Step 1';
  end try
  begin catch
    select 'Step 1 handler - handling ''' + error_message() + '''';
    goto commit_and_exit;
  end catch;

  begin try
    select 'Step 2';
    raiserror('Step 2 error', 16, 1);
  end try
  begin catch
    select 'Step 2 handler - handling ''' + error_message() + '''';
    goto commit_and_exit;
  end catch;

end try
begin catch
  select 'Outer handler - handling ''' + error_message() + '''';
  goto commit_and_exit;
end catch

commit_and_exit:

raiserror('Error raised for the caller to see', 16, 1);

Está documentado que

GOTOAs instruções podem ser usadas para pular para um rótulo dentro do mesmo bloco TRYou CATCHou para sair de um bloco TRYou CATCH.

Ou pode?

Dado o código acima, um programador sensato descobriria que a saída seria

Etapa 1
Etapa
2 Manipulador da etapa 2 - manipulação de 'Erro na etapa 2'
<Erro gerado para o chamador ver>

Na verdade o que está acontecendo é:

Etapa 1
Etapa 2
Etapa 2 manipulador - tratamento de 'Erro de etapa 2'
Manipulador externo - tratamento de 'Erro gerado para o chamador ver'
<Erro gerado para o chamador ver>

Ao depurar passo a passo, posso ver que o controle deixa completamente os blocos try/catch, então um erro é gerado, o controle é retornado ao bloco mais externo catch, esse bloco é executado, o controle vai para commit_and_exit:novamente e esse bloco final é executado novamente .

Se você tiver alguns commit trans ou rollback transs abaixo commit_and_exit:de , tentará confirmar o tran duas vezes. Com resultados imagináveis, dado que pode haver transações externas iniciadas pelo chamador.

Também tentei criar outro label, end_of_outer:, logo antes do end try, para que o controle saia do bloco externo try"normalmente". Interessante o suficiente, isso não fez nenhuma diferença.

O que diabos está acontecendo e qual é a maneira correta de fazer isso?

Acho que quero associar um dado a cada sessão.

A associação deve ter as seguintes propriedades:

• Os dados associados são fornecidos como parte da string de conexão (se isso for possível) ou como um parâmetro para um procedimento armazenado que deve ser o primeiro SP chamado na sessão.
• Os dados devem estar facilmente disponíveis para qualquer procedimento armazenado que a sessão chame, sem que esse dado seja passado como parâmetro.
  Este ponto é essencial para a ideia: obviamente, eu poderia adicionar um parâmetro em cada um dos meus procedimentos armazenados e obrigar o software a passar o dado toda vez que qualquer procedimento armazenado for chamado, mas é exatamente isso que quero evitar.
• Os dados devem expirar automaticamente e ser inválidos assim que a sessão for desconectada. Nenhuma ação do cliente deve ser necessária para marcar os dados como expirados. Se uma sessão for desconectada inesperadamente, isso ainda deve tornar seus dados inválidos.

Eu considerei várias maneiras de fazer isso, mas não gostei de nenhuma:

• Tabelas temporárias locais .
  Escolha um nome de tabela (com base em um GUID recém-criado para evitar conflitos). Tem uma stored procedure que cria uma tabela temporária com esse nome e insere uma linha com os dados passados ​​como parâmetro. Então todos os outros procs podem referenciar #tablee obter os dados. A ideia é que cada sessão tenha seu próprio #table, enquanto o nome do #tablepermanece o mesmo.
  Não vai funcionar. A tabela temporária criada no procedimento armazenado será descartada ao sair do procedimento. Fazer o usuário criar #tablediretamente, sem chamar um SP, não é uma opção.
• Uma tabela normal em que os dados são pesquisados ​​por @SPID .
  Não vai funcionar. Isso requer a exclusão dos dados manualmente após a desconexão.
• APP_NAME() .
  Provavelmente funcionará, mas esse não é realmente o nome do aplicativo e não será facilmente extensível.

Existem maneiras melhores de associar dados a uma sessão?

Alguns tiros no pé acabaram de ocorrer em um servidor de produção. Corrigi o problema, mas agora estou bastante confuso.

Há um subconjunto de procedimentos armazenados que preservam o estado em caso de falha. Ou seja, se o procedimento gerar um erro, ele pode ser confirmado de qualquer maneira com segurança. Isso é feito com save transaction.

Agora, meu entendimento inicial era que o nome do ponto de salvamento definido para a transação é local para o procedimento armazenado que o utiliza. E em um cenário aninhado, se o procedimento armazenado interno salvar com o mesmo nome, na verdade é um nome diferente e está tudo bem.

Para ter certeza de que esse meu entendimento estava correto, configurei um caso de teste:

create table dbo.[footest] (
  v varchar(50) NULL
);
go

insert into dbo.footest(v) values ('Nothing'); go

create procedure dbo.[foo_test_tran_inner]
as
begin
  set nocount on;

  declare @foo int;

  begin tran;

  update dbo.footest set v = 'Inner, before savepoint';

  save tran the_constant_name;

  begin try
    update dbo.footest
    set v = 'Inner, after savepoint';

    set @foo = 1/0;
  end try
  begin catch
    rollback tran the_constant_name;
  end catch;


  commit tran;

  set @foo = 1/0;

  return 0;
end;
go

create procedure dbo.[foo_test_tran_outer]
as
begin
  set nocount on;

  begin tran;

  update dbo.footest set v = 'Outer, before savepoint';


  save tran the_constant_name;

  begin try
    update dbo.footest
    set v = 'Outer, after savepoint';

    exec dbo.foo_test_tran_inner;
  end try
  begin catch
    rollback tran the_constant_name;
  end catch;



  commit tran;

  return 0;
end;
go

begin tran;
exec dbo.foo_test_tran_outer;
commit tran;
select * from dbo.footest;

Isso produz "Outer, antes do ponto de salvamento". O que significa que os nomes dos pontos de salvamento eram locais para o procedimento e foram revertidos corretamente em um cenário aninhado.

Na produção, esses procedimentos armazenados de preservação de estado têm exatamente esse esquema. Há muito mais código neles, mas se você remover, deixando apenas saves, rollbacks e commits, será exatamente o que está mostrado acima.

Mas. Não funcionou na produção. Em vez disso, cada ponto de salvamento aninhado com o mesmo nome parecia substituir um ponto de salvamento anterior, feito a partir do procedimento de chamada. E quando o código externo após receber uma exceção executou um commit(acreditando que as reversões internas foram feitas corretamente), o banco de dados foi deixado em um estado meio parafusado. Se aplicado ao exemplo acima, isso significaria que o select retorna Inner, before savepoint.

Percorri todas as etapas do procedimento de produção com um depurador e confirmei que a execução passou corretamente por todos os esperados save tran a_namee rollback tran a_name.

Para corrigir o problema na produção, substituí isso:

save tran the_constant_name;
...
rollback tran the_constant_name;

com isso:

declare @savepoint varchar(32) = replace(newid(), '-', '');
save tran @savepoint;
...
rollback tran @savepoint;

e isso corrigiu imediatamente.

Então o que dá? O nome do ponto de salvamento é local para o procedimento armazenado ou não?
Se for, então por que o código de produção fez o que fez e foi corrigido com sucesso conforme mostrado?
Se não for, então por que o exemplo de teste acima faz o que faz?

Isso só me deixa louco.

Considere uma tabela simples com colunas irrelevantes removidas:

create table boxes (
  row_id int not null identity(1,1) primary key,
  location varchar(15) null,
  dismantled bit not null default 0,
  rank int not null default 0
  /* irrelevant columns, text and numbers */
)

Para uma determinada consulta importante, quero um índice filtrado:

create nonclustered index anindex ON boxes (rank)
where (dismantled=0 and location is null)

Isso cria um índice que contém cerca de 150 registros de cerca de 150 mil. O que é bom.

Agora consultamos a tabela:

select top (1) row_id
from boxes
where dismantled = 0 and location is null
order by rank;

O plano de execução é estranho. Varredura de índice anindexacompanhada por uma pesquisa de marcador usando o procurado row_idpara confirmar que locationé null. Os registros que passam na pesquisa são selecionados.

Por que na Terra? locationé nullpela própria definição do índice utilizado, não é?

Agora, se eu fizer uma besteira e incluir locationno índice filtrado...

create nonclustered index anindex ON boxes (location, rank)
where (dismantled=0 and location is null)

Então, de repente, o plano de execução é ótimo, o índice continua anindexsem pesquisas.

Este é apenas um exemplo, me deparo com esse problema toda vez que quero usar um índice filtrado. Toda vez acabo sendo forçado a incluir os campos inúteis, sobre os quais o índice é filtrado, na lista de campos indexados, e só assim o servidor usa o índice corretamente; caso contrário, ele o verifica ou o ignora completamente.

O que da? É uma prática recomendada incluir os campos inúteis?

Eu tenho uma tabela de log genérica, com cerca de 5m de linhas.
Há um campo de "digitação forte" que armazena o tipo de evento e várias colunas de "digitação imprecisa" que contêm dados relevantes para o evento. Ou seja, o significado dessas colunas "digitadas imprecisamente" depende do tipo de evento.

Essas colunas são definidas como:

USER_CHAR1 nvarchar(150) null,
USER_CHAR2 nvarchar(150) null,
USER_CHAR3 nvarchar(150) null,
USER_CHAR4 nvarchar(150) null,
USER_CHAR5 nvarchar(150) null,

USER_INTEGER1 int null,
USER_INTEGER2 int null,
USER_INTEGER3 int null,
USER_INTEGER4 int null,
USER_INTEGER5 int null,

USER_FLAG1 bit null,
USER_FLAG2 bit null,
USER_FLAG3 bit null,
USER_FLAG4 bit null,
USER_FLAG5 bit null,

USER_FLOAT1 float null,
USER_FLOAT2 float null,
USER_FLOAT3 float null,
USER_FLOAT4 float null,
USER_FLOAT5 float null

As colunas 1 e 2 em cada tipo são muito usadas, mas a partir do número 3, poucos tipos de evento forneceriam tanta informação. Portanto, decidi marcar as colunas 3-5 em cada tipo como SPARSE.

Fiz algumas análises primeiro e vi que, de fato, pelo menos 80% dos dados em cada uma dessas colunas são null, e em cerca de 100% dos dados são null. De acordo com a tabela de limite de economia de 40% , SPARSEseria uma grande vitória para eles.

Então eu fui e apliquei SPARSEnas colunas 3-5 em cada grupo. Agora, minha tabela ocupa cerca de 1,8 Gb no espaço de dados, conforme relatado por sp_spaceused, enquanto antes do sparsing era de 1 Gb.

Eu tentei dbcc cleantable, mas não teve efeito.
Então dbcc shrinkdatabase, nenhum efeito também.

Intrigado, removi SPARSEe repeti o dbccs. O tamanho da mesa permaneceu em 1,8 Gb.

O que da?