I have a maintenance place that executes a stored proc. The stored proc is as followings:
CREATE PROCEDURE spFirst
@Err AS INT = -1
EXEC @Err = xp_cmdshell 'dir *.exe'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail_Proj',
@recipients = 'firstname.lastname@example.org',
@body = 'Complete',
@subject = 'Dir Listing'
The proc runs successfully and when done, I always get an email message in my Inbox, as expected. When the proc is executed from a maintenance plan, it still runs successfully, and the email message is successfully received in my Inbox.
A SQL Job was setup to run the maintenance plan. The history of the job indicates that it is completing successfully, but this cannot be true, as my inbox never receives the expected email message.
I tried recreating the Job, testing with both Windows and SQL credentials, recreating the maintenance place, but cannot resolve this issue. Any ideas?