Por que o sp_send_dbmail falha em tarefas do SQL Agent?

Question

Eu tenho uma consulta simples que executo para enviar notificações por e-mail para nossos administradores da conta do AD quando as contas foram desativadas por mais de 30 dias. Ele funciona perfeitamente quando eu o executo por conta própria, logado como SA, mas ele falha quando executado em trabalhos do SQL Server Agent.

Aqui está a consulta, com itens específicos de negócios e nomes de objetos substituídos:

DECLARE @QueryString varchar(max)
SET @QueryString = 'Select TrackingTable.Username FROM dbName.Schema.TrackingTable inner join dbName.Schema.viewName on DisabledAccounts.username = viewName.username WHERE DATEDIFF(dd,DateDisabled,GETDATE()) > 25 AND viewName.OU = ''InactiveAccounts'''
EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'Profile', @body = 'This is the body text. Nothing interesting here.
', @recipients = '[email protected]', @subject='Account status update', @query = @QueryString, @importance = 'High'

Quando eu o executo como SA, a mensagem é enviada. Dentro de um trabalho do SQL Server Agent, recebo este erro:

Executed as user: DOMAIN\MemberOfDomainAdmins. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050).  The step failed.

O usuário do domínio em que a tarefa é executada recebe a função db_owner no msdb e o banco de dados usado para a consulta anexada à mensagem. Seu esquema padrão em ambos os bancos de dados é dbo.

Também é atribuída a função sysadmin no servidor e é membro do DatabaseMailuserRole no msdb. Ele também tem acesso privado e público ao perfil do Database Mail usado pela consulta.

Já vi dezenas de exemplos do mesmo problema on-line, mas já tomei as medidas que corrigiram esse problema nos exemplos que vi. O que mais eu posso tentar?

    

Answer 1

Eu percebi isso esta manhã. O problema estava com a função DATEADD . Quando está dentro de uma instrução EXECUTE (que é como uma tarefa do SQL Server Agent a executaria), o intervalo deve ser uma datapart específica (dia), não um dos tokens ("dd").

Então, esta função: DATEDIFF(dd,DateDisabled,GETDATE())

Necessário para ser mais assim: DATEDIFF(day,DateDisabled,GETDATE())

Houve várias chamadas para essa função, mas você teve a ideia.

Veja como eu descobri: eu tinha o SQL Server Management Studio script a tarefa como um script CREATE em um novo documento editor de consulta. Uma vez que encontrei o passo que estava tentando executar, copiei. Os pedaços suculentos são parecidos com isto:

@command = N'[my query]'

Copiei esse grupo de linhas para uma nova janela e adicionei um DECLARE para a variável de comando.

Por fim, usei EXECUTE (@command) AS USER = '[the user the job runs as]' para ver o que aconteceria. Executando a consulta dessa maneira, recebi muito mensagens de erro mais detalhadas do que eu no log do trabalho.

Eu o corrigi agora e o trabalho funciona perfeitamente.