var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Job Step
I am executing a stored procedure within a job step. This stored procedure executes the xp_sqlmaint stored procedure with some parameters. I run this job as 'sa'.
The job fails with the following error message:
sqlmaint.exe failed. Step failed.
What could cause this error? Please help. Thanks.
Copy and paste the sqlmaint command to Query Analyzer and see if you get more informative error messages.
This is the stored procedure that I have created.
CREATE PROCEDURE usp_RebuildIndexes
DECLARE @PlanID char(36)
SET @PlanID = NEWID()
EXECUTE master.dbo.xp_sqlmaint N'-PlanID '' + @PlanID + '' -Rpt "M:\MaintPlanRpts\Redlight Maintenance Plan0.txt" -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '
Then within the job step I run:
and when I run the job I get that error message.
You seem to be confused about PlanIDs. They are created by the Maintenance Plan Wizard and have to correspond to an existing Maintenance Plan. You are create a random PlanID on the fly using newid(), which of course does not correspond to any existing plan, so your sqlmaint statement fails.
Here's a tip: you can refer to your maintenance plans by name rather than by PlanID by using the "N'-PlanName" parameter. Take a few minutes with Books Online to review all the parameters that are available for sql_maint. It is quite powerful.
Thank you very much for your help.
This is what the job step looks like now and I am still getting the same error message.
EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt "M:\MaintPlanRpts\Redlight Maintenance Plan0.txt" -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '
What am I doing wrong now?
SQL Server does not like double quotes. Try this:
EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt M:\MaintPlanRpts\Redlight Maintenance Plan0.txt -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10'
Just tried it. Same error message.
Ok. Try eliminating the spaces in your file name. SQLMaint may be interpreting as separate parameters:
EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt M:\MaintPlanRpts\Redlight_Maintenance_Plan0.txt -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10'
That didn't work either. Same error.
Not sure what is going on then. Is that the error you get when you run the statement through Query Analyzer?
Try removing parameters one at a time to narrow in on the culprit.
I executed it as a step in the job in EM. When I looked in the maintenance report I found the following error:
Error 21268: [SQL-DMO]Row or column specified is outside the range of the specified query result set.
That's the error I got when I executed your statement in Query Analyzer, so try dropping off parameters to see which one is causing the problem.
Once I added the Server name, the user and the password to the script it worked. I made this user a sysadmin.
So now it looks like this:
EXEC master.dbo.xp_sqlmaint ' -S servername -U user -P password
-PlanName OptimizationPlan -Rpt "C:\MaintPlanRpts\Plan.txt" -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '
I don't understand why I need to supply these 3 parameters. Any ideas?
I would guess it has something to do with the accounts under which you, SQL Server, and SQL Server Agent are logging in.