Post reply

What is the TSQL to execute a maint plan?

  • November 14, 2008 at 9:31 am

    #383390

    I thought this would be simple, but I must just be missing something.

    I have a maint plan on SQL Server 05 and I want to be able to execute it using TSQL.

    I'd also like to know what to do to check on its progress so I can see if it executed successfully or failed.

    This works fine in the SSMS GUI, but I want to be able to do this with TSQL.

    If I do a trace with profiler among other things I see this executed:

    EXECUTE msdb..sp_maintplan_start (with the planID then)

    However, I also see SSMS appear to update logs manually, I was really hoping there would just be a command that was basically "Run this maintplan... return success or failure..."

    Could someone help me out with this please?

    Thank you.

    EDIT:

    I see sqlmaint as an option: http://msdn.microsoft.com/en-us/library/ms162827(SQL.90).aspx but it is being depreciated... so what is the replacement option?

  • November 24, 2008 at 9:59 am

    #902595

    According to BOL, they recommend now using the dtexec command prompt utility (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm)

    This is utility appears to have many options for executing and logging SSIS packages, which are precisely what Maintenance plans are.

  • November 24, 2008 at 10:11 am

    #902604

    Thanks, I'll check that out.

    I ended up just using (I forget now... sp_agent_somethingOrOther) to start the SQL Agent job and doing it that way.

    It works fine, but it just doesn't feel like a "good" solution.

    I'll read up on the dtexec command prompt utility and hopefully that will get it taken care of for future use.

    Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply