sp_send_dbmail fails with EXECUTE but succeeds with SELECT

Question

I have used this bit of code for years on SQL Server 2017 and it has always ran fine.

EXECUTE msdb.dbo.sp_send_dbmail 
@recipients = 'mymail@myaddress',
@subject = 'DailySecurityCheck',
@query = 'EXECUTE [database].[dbo].[pr_DailySecurityCheck]'

I just migrated to SQL Server 2019 and this now throws this error:

Failed to initialize sqlcmd library with error number -2147467259

I can run the EXECUTE statement successfully from SSMS.

I can replace the @query variable with a SELECT statement and it will run successfully.

I have also tried adding @query_result_header = 1 and @query_no_truncate = 0 with no luck.

I am not sure where to go from here.

Answer 1

Mitch, I looked at permissions and everything looked correct. I have not "solved" the issue but I have found a work around. The last step that [database].[dbo].[pr_DailySecurityCheck] performed was to query a results table. I simply moved that query block from the procedure to the @query variable in sp_send_dbmail. Works perfect now.

Thanks everyone for offering suggestions.