SQL Server DBmail间歇性异常：多配置文件无法稳定发件求助

Intermittent issues like this are always tricky, but let’s break down the most likely causes and fixes based on the error message you’re seeing ("A recipient must be specified") and the fact that only your SQL Server Agent-linked profile works consistently.

1. Dynamically Generated Recipient Lists Are Sometimes Empty #

This is the top suspect for intermittent "recipient required" errors. If your sp_send_dbmail calls rely on a query, variable, or stored procedure to generate the recipient list, there’s a chance that logic occasionally returns no valid email addresses.

• Fix: Add logging to capture the recipient string every time you send mail. Insert the value of @recipients (or @copy_recipients, @blind_copy_recipients) into a dedicated log table right before calling sp_send_dbmail. When the error occurs, you can check if the list was actually empty.
• Fallback: Modify your recipient logic to include a default fallback address (e.g., an admin email) if the dynamic list returns no results. For example:

  DECLARE @Recipients NVARCHAR(MAX)
  SELECT @Recipients = STRING_AGG(Email, ';') FROM dbo.RecipientList WHERE Active = 1
  IF @Recipients IS NULL OR @Recipients = ''
      SET @Recipients = 'admin@yourdomain.com'
  

2. Execution Context & Permission Differences #

The working profile is used in SQL Server Agent, which runs under the Agent service account. Other profiles might be executed under different contexts (e.g., a user’s login, a different service account) that have intermittent access to the data sources used to fetch recipients.

• Check: Compare the permissions of the account running non-Agent DBmail calls against the Agent service account. Ensure both have read access to any tables/views used to generate recipient lists.
• Test: Manually run sp_send_dbmail using the same credentials that trigger the intermittent failure (e.g., via an application’s service login) and see if you can reproduce the error consistently.

3. Malformed Recipient Strings #

Even if the list isn’t empty, invalid formatting (like trailing semicolons, invalid characters, or malformed email addresses) can cause the mail server to reject the message with a misleading "recipient required" error.

• Validate: Use a function to check each email address in the list for validity before sending. For example, a simple regex check:

  CREATE FUNCTION dbo.IsValidEmail (@Email NVARCHAR(255))
  RETURNS BIT
  AS BEGIN
      RETURN CASE WHEN @Email LIKE '%_@__%.__%' AND CHARINDEX(' ', @Email) = 0 THEN 1 ELSE 0 END
  END
  

• Clean: Trim extra spaces or semicolons from the recipient string. For example: SET @Recipients = TRIM(';' FROM @Recipients)

4. Query Plan or Parameter Sniffing Issues #

If your recipient list comes from a query that uses parameters, parameter sniffing could lead to inconsistent results (sometimes returning rows, sometimes not) based on cached execution plans.

• Fix: Update statistics on the tables used in the recipient query to help SQL Server generate better plans:

  UPDATE STATISTICS dbo.RecipientList
  

• Force Recompile: Add OPTION (RECOMPILE) to your recipient query to ensure it generates a fresh plan each time:

  SELECT @Recipients = STRING_AGG(Email, ';') FROM dbo.RecipientList WHERE Department = @Dept OPTION (RECOMPILE)
  

5. Timing Conflicts With Data Modifications #

If there are jobs, triggers, or applications modifying the recipient data (e.g., updating active status, deleting records) around the same time DBmail runs, you might hit moments where the recipient list is temporarily empty.

• Check: Review the timing of your DBmail calls against any data modification jobs. Add a short delay or use transactional logic to ensure the recipient data is stable when fetching the list.
• Transaction Wrap: Wrap the recipient fetch and mail send in a transaction to prevent reads of partially modified data:

  BEGIN TRANSACTION
  DECLARE @Recipients NVARCHAR(MAX)
  SELECT @Recipients = STRING_AGG(Email, ';') FROM dbo.RecipientList WITH (NOLOCK) WHERE Active = 1
  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'YourProfile', @recipients = @Recipients, @subject = 'Test'
  COMMIT TRANSACTION
  

Final Notes #

Since only the SQL Server Agent profile works consistently, focus on the differences between that profile’s execution context and the others. The Agent’s service account likely has stable permissions and runs in a predictable environment, which might be the key to resolving the intermittent failures.

内容的提问来源于stack exchange，提问作者Shams7353