[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Maintenance Plan Works, But Not Through SQL Job

Posted on 2010-08-23
3
Medium Priority
?
1,101 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
ID: 33504101
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
ID: 33504441
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
ID: 33504755
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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 …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

865 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