O Unit Separator (US) e o erro ao tentar desabilitar um JOB no SQL Server Agent!

Olá pessoal tudo certo? Espero que sim!

Recentemente no cliente passei por algo bem interessante, havia um JOB que não conseguia desabilitar no SQL Server Agent, quando eu tentava o erro abaixo ocorria:


Via T-SQL também dava o mesmo erro, porém surgiu a primeira dica:


Msg 6841, Level 16, State 1, Procedure tr_job_disable, Line 27

FOR XML could not serialize the data for node 'td' because it contains a character (0x001F) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

Que objeto seria este? Como o ambiente era bem tranquilo com relação ao volume de transações, decidi rodar um Profiler capturando os seguintes eventos:

·         Errors & Warnings (All).
·         SP: StmtStarting
·         SQL: BatchStarting
·         SQL: StmtStarting

Com o Profiler no ar, tentei o Disable novamente no JOB para provocar o erro, vejam que interessante. Antes de ocorrer um Exception foi capturado o seguinte trecho de código:




Olhando a coluna Object Name e Object Type foi possível confirmar que o objeto envolvido era uma trigger:


Esta trigger estava criada dentro da tabela de sistema sysjobs no MSDB, e seu intuito era que toda vez que algum JOB fosse desabilitado um e-mail fosse enviado para os DBA’s. Voltando ao nosso erro:

Msg 6841, Level 16, State 1, Procedure tr_job_disable, Line 27

FOR XML could not serialize the data for node 'td' because it contains a character (0x001F) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

Nosso problema dentro da trigger está no comando abaixo, mais precisamente no que esta marcado em amarelo:

SET @tableHTML =   
   N'<H1>Job desabilitado!</H1>' +   
   N'<table border="2">' +   
   N'<tr><th>Job</th>' +   
   N'<th>Server</th>' +             
   N'<th>Data</th>' +       
   N'<th>Usuário</th></tr>' +   
   CAST ( ( SELECT td = Job,       '',   
       td = @@servername, '',   
       td = data, '',    
       td = Usuario, ''          
       FROM #TableIC            
       FOR XML PATH('tr'), TYPE    
   ) AS NVARCHAR(MAX) ) +   
   N'</table>' ;  

O SQL está dizendo que existe um caracter inválido 0x001F para formatação do e-mail, mas quem seria 0x001F?

Pesquisando um pouco descobri que este é o valor hexadecimal correspondente ao UNIT SEPARATOR (US), um caracter que seria uma espécie de “underline”, visível apenas em alguns editores de texto:

Unicode Map : Public Unicode Character Map

What is character 0x1f?

Já sabendo do US notei outra coisa, vejam como ficou a seleção do job no SQL Server Agent quando foi "clicado":

Ele tem realmente um “espaço em branco” na frente. Pois bem, gerei o script do mesmo. No Management Studio do SQL Server, tudo ok:

Mas no Notepad++, notem o US aparecendo!

E no Word, notem o “_” aparecendo!

Desabilitando a trigger, ou então removendo o US do nome do JOB:


Sucesso! Foi possível desabilitar o job.

Para fechar o assunto, deixo como recomendação que todo script escrito fora do SSMS seja analisado com cuidado e lembro ainda, que triggers sobre tabelas de sistema não são suportadas pela Microsoft, conforme link abaixo:

System Databases

Espero que tenha sido útil! Um abraço!

Monitore o Event Viewer combinando o SQL + Powershell + Database Mail!

Olá pessoal tudo certo? Espero que sim!

Recentemente tenho me aventurado com algumas queries SQL Server combinadas com o Powershell e o resultado tem sido bem interessante. Vou compartilhar com vocês uma procedure que desenvolvi que realiza uma varredura do log de sistema do Event Viewer e envia um e-mail automaticamente para o time de DBA’s caso as condições dentro do IF/ELSE sejam atendidas. Este script pode ser uma saída simples de monitoração caso o local em que você trabalhe não possua softwares que realizem este trabalho.

Para que esta procedure funcione corretamente é necessário:

Definir uma base de dados onde ela será criada.
Powershell v.2.0 >
Diretório: C:\Temp (ou qualquer outro em que deseje colocar o arquivo csv que o Powershell irá gerar).
Database Mail do SQL Server devidamente configurado (Veja o link http://sqlmagu.blogspot.com.br/2015/06/problema-database-mail-x-smtp.html).
Permissões de BULK INSERT para a conta que rodará a procedure.
‘show advanced options’ habilitado, esta opção pode ser acessada via “sp_configure”.
‘xp_cmdshell’ habilitado no SQL Server para rodar o comando do Powershell, igual ao item anterior, esta opção pode ser acessada via “sp_configure”.

Como é feita a varredura do Event Viewer com o Powershell?

A coleta da informação é feita através da cmdlet Get-Eventlog, a qual tem a função especifica de fazer a leitura de diversos logs existentes no Windows (https://technet.microsoft.com/en-us/library/hh849834.aspx).

Condicionais da procedure:

Qual período de busca no Event Viewer?
Em dias.
Quais eventos devem ser procurados no Event Viewer?
Já deixei alguns pré-definidos no código.
Para qual ou quais e-mails serão enviados os alertas?
Definido na criação da procedure.

Abaixo esta o código e um exemplo do e-mail de alerta:

USE [MAGUDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*****************************************************************************************************

Monitoração dos erros do Event Viewer (SYSTEM) com o Powershell.
Criação: 17/08/2015
Atualização: 23/09/2015
Autor: André César Rodrigues

@Dias = Deve-se informar a quantidade de dias no passado que o PowerShell deverá ler do EventViewer.
@emails = Deve-se definir a lista de destinatários.

Exemplo de execução: exec stp_Verifica_EventViewer 1

******************************************************************************************************/

CREATE PROCEDURE [dbo].[stp_Verifica_EventViewer] (@Dias INT, @emails NVARCHAR(MAX)='testmail@sqlmagu.com.br')
AS
BEGIN

PRINT 'Dias configurados para busca: ' + convert(varchar(10),@Dias)

SET NOCOUNT ON

--Declaração das váriaveis...

DECLARE @eventlist NVARCHAR(MAX)
DECLARE @cmd VARCHAR(8000)

--Cria tabela temporária que irá armazenar os eventos...

CREATE TABLE #ErrosEvtVwr
(
ID_Evento NVARCHAR(max) null,
Ocorreu_em NVARCHAR(max) null,
Mensagem NVARCHAR(max) null
)

--Abaixo está a lista de eventos a serem buscados no Event Viewer, estes devem estar com "" pois serão processados pelo Powershell...

SET @eventlist = '"55","1001","1006","1037","1038","1069","1074","1119","1127","1135","1146","1168","1172","1177","1183","1196","1205","1230","1564","6005","6006","6009","7024","7031","7034"'

PRINT 'Eventos que foram buscados: ' + @eventlist

--Abaixo atribuo a váriavel @cmd o comando do PowerShell, notem que no meio do comando se espera o valor da
--váriavel @Dias, ou seja, para executar a procedure você deve informar a quantidade de dias passados para o EventViewer ser lido...

SELECT @cmd = 'powershell -command "$eventLog = Get-EventLog -Log System -After (Get-Date).AddDays(-' + convert(varchar(10),@Dias) + ') | Select-Object EventID, TimeGenerated,Message;$list = @(' + @eventlist + ');$events = @();foreach($event in $list){$events += $eventLog | Where {$_.EventID -eq $event}}$events | Export-CSV c:\temp\erros.csv -noTypeInformation -ErrorAction SilentlyContinue'

--Execução do cmd preparado acima...

EXEC xp_cmdshell @cmd,no_output

--Insert na tabela baseado no csv gerado pelo PS...

BEGIN TRY
    BULK
          INSERT #ErrosEvtVwr
          FROM 'c:\temp\erros.csv' --Diretório de sua preferência...
          WITH
          (
          FIELDTERMINATOR = ',',
          ROWTERMINATOR = '\n'
          )
END TRY
BEGIN CATCH
    BULK
          INSERT #ErrosEvtVwr
          FROM 'c:\temp\erros.csv' --Diretório de sua preferência...
          WITH
          (
          FIELDTERMINATOR = ',',
          ROWTERMINATOR = '''+CHAR(10)+''' --Proteção contra um erro ao realizar o Bulk de um arquivo CSV...
          )
END CATCH;

--Se houver eventos (contagem de linhas >0), prepara e envia o e-mail...

IF (Select count(1) from #ErrosEvtVwr) >0
BEGIN

DECLARE @subject NVARCHAR(MAX)
DECLARE @servername NVARCHAR(150)

SET @servername = @@SERVERNAME
SET @subject = 'Erros registrados no EventViewer (SYSTEM) - Instância: ' + @servername

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Erros registrados no EventViewer (SYSTEM) - Instância: ' + @servername + '</H1>' +
          N'<table border="1">' +

    CAST ( ( SELECT td = ID_Evento, '',
                                                td = Ocorreu_em, '',
                                                td = Mensagem, ''
              FROM #ErrosEvtVwr
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

          EXEC msdb.dbo.sp_send_dbmail @recipients=@emails,
    @subject = @subject,
    @body = @tableHTML,
    @body_format = 'HTML' ;
PRINT 'E-mail enviado para: ' + @emails
END
ELSE -- Se não houver eventos, nenhum e-mail é enviado...
PRINT 'Os eventos buscados não foram encontrados no Event Viewer de Sistema no período configurado!'

PRINT 'Fim da execução!'

--Drop da tabela temporária criada no começo do script...

DROP TABLE #ErrosEvtVwr

END
GO

Qualquer problema com o script por favor reportem e fiquem a vontade para mudarem o código =)

É isso pessoal! Um abraço e até a próxima!