Solved

Maintenance Plan Works, But Not Through SQL Job

Posted on 2010-08-23
3
973 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:brian_appliedcpu
  • 2
3 Comments
 
LVL 10

Expert Comment

by:Marcjev
Comment Utility
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?
0
 
LVL 2

Author Comment

by:brian_appliedcpu
Comment Utility
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???
0
 
LVL 2

Accepted Solution

by:
brian_appliedcpu earned 0 total points
Comment Utility
Instead of manually creating a SQL Job, I went back to the design of the maintenance plan, and created a schedule for the plan. This, in turn, automatically created a new SQL Job. This new SQL Job works, whereas the one I created manually still fails.

If I compare the property settings of the manual Job vs. the auto-created Job, the settings appear to be virtually the same. Job owner is the same, Run As is the same, connection is the same, datasource is the same, and authentication is the same. The only difference that I can see is when you open the SQL Job in design mode, select Steps, select the step (I only have the one step), click Edit, and go to the Set Values tab. The manual Job has nothing under the Set Values tab whereas the auto-created Job has \Package\Subplan_1.Disable under the Set Values tab with a value of False.

If you look under the Command Line tab, here is the command-line for the manual Job vs. auto-created Job:

Manual Job:
/SQL "\Maintenance Plans\MaintenancePlanTest" /SERVER "MainSvr\Trag"  /CHECKPOINTING OFF /REPORTING E

Auto-Created Job:
/SQL "Maintenance Plans\MaintenancePlanTest" /SERVER "MainSvr\Trag"  /CHECKPOINTING OFF /SET "\Package\Subplan_1.Disable";false /REPORTING E

Not that the above command-lines shed any more light on the problem, but it is interesting that when creating the Job from within the maintenance plan, the new job works.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now