Solved

Maintenance Plan Works, But Not Through SQL Job

Posted on 2010-08-23
3
978 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Contained Database Collations 6 28
Best Query To Insert One Year Calendar 6 44
Benefits of SMB Fileshare 3 63
sql Total query 2 14
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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 …
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 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

16 Experts available now in Live!

Get 1:1 Help Now