?
Solved

Maintenance Plan Works, But Not Through SQL Job

Posted on 2010-08-23
3
Medium Priority
?
1,046 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

765 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