brian_appliedcpu
asked on
Maintenance Plan Works, But Not Through SQL Job
I have a maintenance place that executes a stored proc. The stored proc is as followings:
CREATE PROCEDURE spFirst
@Err AS INT = -1
AS
EXEC @Err = xp_cmdshell 'dir *.exe'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail_Proj',
@recipients = 'me@xyz.com',
@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?
CREATE PROCEDURE spFirst
@Err AS INT = -1
AS
EXEC @Err = xp_cmdshell 'dir *.exe'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail_Proj',
@recipients = 'me@xyz.com',
@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?
Marcjev
It must have something to do with the credentials used to run your service. When you run the proc from the management studio you run in your context. When running the as a job, you run using the credentials of the service. Maybe these credentials have no network access?
ASKER
I changed the xp_cmdshell to do a dir on C:\Temp\ and gave the SQL Agent service account full control to this directory, but my SQL Job is still failing.
How do I verify which account is being using when xp_cmdshell is executed???
How do I verify which account is being using when xp_cmdshell is executed???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.