Solved

Maintenance Plan Works, But Not Through SQL Job

Posted on 2010-08-23
3
1,005 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What can the "OPTION" in the GROUP help me in my query? 4 45
Display SQL 2008 last modified/update Database 11 72
SQL Server Express or Standard? 5 59
Search Text in Views 2 23
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…

740 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