Job Step

Hi All,

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

AS

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:

execute msdb.dbo.usp_RebuildIndexes

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.|||Thanks for the help.