Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm having a problem with a maintenance plan in SQL Server 2005. It's a very basic maintenance plan - it has one 'Execute T-SQL Statement Task' which simply inserts a row into a table.

When I execute the maintenance plan the T-SQL statement is executed and the row is entered into the table, but I get a message box displaying the following:

TITLE: Execute Maintenance Plan

Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.


ADDITIONAL INFORMATION:

Job 'CobraTest.Subplan_1' failed. (SqlManagerUI)


BUTTONS:

OK

This happens when I execute the maintenance plan manually in SQL Server Management Studio by going Management --> Maintenance Plans --> Right Click --> Execute and also when I schedule it through SQL Server Agent.

There are other Maintenance Plans on the server which run ok both manually and scheduled.

One thing that I'm curious about is that the scheduled SQL Server Agent Job was running ok, then failed for a few nights, then ran ok for a few nights and is now failing again and there has been no intervention or changes to the SQL server which makes me think it could be someone changing things elsewhere on the network?

Any suggestions?

share|improve this question

3 Answers 3

Random thought...

Is the exit code for the stored proc non-zero? For example, the RETURN statement gives @@ROWCOUNT or similar.

I saw similar a long time ago with SQL agent jobs... pre SSIS though

share|improve this answer
    
Hi, The procedure just does a single insert, so the only message was '1 row(s) inserted'. I've added 'SET NOCOUNT ON' to surpress this message, but the problem still occurs. –  Gavin Jul 20 '09 at 8:45

Have you opened the job history to view the error message? Make sure you expand the history entry and view the message from the actual job step.

A common causes for false negatives is Warnings during execution. For example, the old "Warning: Null value is eliminated by an aggregate ..." message often causes SSIS jobs to report failure (depending on the the setting chosen for the allowable number of warnings.)

If the actual SQL being run isn't dynamic, run it manually in a query window and check the results there.

share|improve this answer
    
Hi, I've gone down the history. There is only one step (as mentioned, T-SQL block, single insert, record is inserted). The message is as follows: Date 20/07/2009 09:29:47 Log Job History (MyDB.Subplan_1) Step ID 1 Server Server\PRODUCTION Job Name MyDB.Subplan_1 Step Name Subplan_1 Duration 00:00:06 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: Username. The package execution failed. The step failed. The sql is no dynamic, I can paste it into Query Editor and it runs with no warnings. –  Gavin Jul 20 '09 at 8:50

I don't have a definitive answer for this, but the server is running SQL Server 2005 with SP1. My client machine from which I was connecting using Managament Studio is running SP2. When I create the Maintenance Plan on the server (through Terminal Services), it runs successfully. When I create the Maintenance Plan on my machine (connected to the server), it fails.

This will require further investigation, because I would normally use that same client machine to work on both SP1 and SP2 servers, but in this instance that doesn't seem to be such a good idea.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.