Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Job Step

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Copy and paste the sqlmaint command to Query Analyzer and see if you get more informative error messages.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2004
    Posts
    268
    Thank you very much for your help.

  6. #6
    Join Date
    Jul 2004
    Posts
    268
    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?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL Server does not like double quotes. Try this:
    Code:
    EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt M:\MaintPlanRpts\Redlight Maintenance Plan0.txt -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2004
    Posts
    268
    Just tried it. Same error message.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ok. Try eliminating the spaces in your file name. SQLMaint may be interpreting as separate parameters:
    Code:
    EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt M:\MaintPlanRpts\Redlight_Maintenance_Plan0.txt -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jul 2004
    Posts
    268
    That didn't work either. Same error.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2004
    Posts
    268
    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.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jul 2004
    Posts
    268
    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?

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I would guess it has something to do with the accounts under which you, SQL Server, and SQL Server Agent are logging in.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •