Starting July 1st, 2021, SQL Server Agent fails when using sp_send_dbmail to execute a stored procedure containing a linked server. If I remove the linked server portion of the code from the stored procedure the job runs successfully.
The job simply executes the following SQL statement:
EXECUTE msdb.dbo.sp_send_dbmail
@recipients = 'My.Name@MyEmailAddress',
@subject = 'Daily Security Check',
@query = 'EXECUTE MyDatabase.sec.pr_LinkedServerQuery',
@attach_query_result_as_file = 1
I can successfully run this code manually from a query window while logged on as the Agent. But if I R-click the job and select “Start job at step…” the agent job fails. Also, if I edit the job by removing the dbmail procedure and simply run EXECUTE MyDatabase.sec.pr_LinkedServerQuery the agent job is successful. It is only when nested inside the dbmail procedure that it fails. This job has previously run successfully for over a year.
I am using SQL Server 2017.
The logs show an error about sp_send_dbmail but searches on this error don’t seem to reflect the issue I am having.
Executed as user: HCI\AgentName. Microsoft (R) SQL Server Execute Package Utility Version 14.0.3381.3 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 2:19:52 PM Progress: 2021-07-06 14:19:52.96 Source: {457716A2-22BF-47F5-A08E-8A48A218911F} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2021-07-06 14:19:53.11 Code: 0xC002F210 Source: Execute T-SQL Statement Task Execute SQL Task Description: Executing the query "EXECUTE msdb.dbo.sp_send_dbmail @recipients = '..." failed with the following error: "Failed to initialize sqlcmd library with error number -2147467259.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2021-07-06 14:19:53.11 Code: 0x80019002 Source: Subplan_1 Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Progress: 2021-07-06 14:19:53.16 Source: Notify Operator Task Executing query "EXECUTE msdb.dbo.sp_notify_operator @name=N'Andy H...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:19:52 PM Finished: 2:19:54 PM Elapsed: 1.719 seconds. The package execution failed. The step failed
This issue has bested me so far. Thank you for any insight into slaying this beast!
