Post reply

Maintenance plan with powershell

  • June 22, 2012 at 4:35 am

    #276832

    Hi,

    I'm quite new on this forum but it's really a great place.

    I have a question for some great experts here:-)

    I have some wizard made maintenance plan in SQL 2012 but I'm asked to execute these with powershell.

    Executing the plans is not a big issue, the big issue is that I need to get the execution return code and I have no clue how to do that.

    If any body could help it would be great

    Thanks a lot

    Matthieu

  • June 22, 2012 at 8:44 am

    #1504529

    What are you using within your PowerShell script to invoke the Maintenance Plan? DTExec.exe? If so, then generically the question becomes "how do I capture the return code when calling an executable via PowerShell?". There is plenty of info online about how to do that. Here is one of the first hits I received from Google:

    Powershell Blog > ErrorLevel equivalent

    PS Welcome to SSC, it is a great place 🙂

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 23, 2012 at 1:49 am

    #1504731

    Hi and tanks for thé welcome 🙂

    In my powershell script I'm trying to execute a query with invoke-sqlcmd. :

    Sqlps then

    Invoke-sqlcmd -serverinstance xxxxxxx\xxxxxx -query ""

    I manage to get the result of the power shell query but I don't know how to get the result of the SQL job I want to execute within the query.

    The thing is that I have problems executing a complex query with this.

  • June 23, 2012 at 7:43 am

    #1504747

    Invoke-SqlCmd is good for issuing commands, but not necessarily for interacting with the database iteratively.

    For that you'll be better off using the .Net SqlClient classes from PowerShell.

    Here is an example to get a resultset:

    Run a SQL Server Command from PowerShell without the SQL Server Provider

    Google will turn up lots more examples on how to use it from PS.

    On your job exec specifically, are you using sp_start_job? If so, did you know the call was async and that the return code is not indicative of whether the job succeeded, only whether it started?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 23, 2012 at 3:23 pm

    #1504777

    matthieu178 (6/22/2012)

    Hi,

    I'm quite new on this forum but it's really a great place.

    I have a question for some great experts here:-)

    I have some wizard made maintenance plan in SQL 2012 but I'm asked to execute these with powershell.

    Executing the plans is not a big issue, the big issue is that I need to get the execution return code and I have no clue how to do that.

    If any body could help it would be great

    Thanks a lot

    Matthieu

    Considering that everything that needs to be done in a maintenance plan can be done in a maintenance plan, I have to know... why does someone want to complicate this with PowerShell?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 24, 2012 at 1:32 am

    #1504803

    Hi firt of all thanks for the answers.

    Indeed I'm using the sp startjob.

    Unfortunately I don't have much knowledge in .net programming either, even with powershell commandlets.

    For the second question on why I want to complicate the wonderfully well done maintenance pans is because I'm required to launch the maintenance plan with a scheduler (INDESCA).

    I know, this is completely stupid but I'm required to 🙁

  • June 24, 2012 at 8:49 am

    #1504824

    matthieu178 (6/24/2012)

    Hi firt of all thanks for the answers.

    Indeed I'm using the sp startjob.

    Unfortunately I don't have much knowledge in .net programming either, even with powershell commandlets.

    For the second question on why I want to complicate the wonderfully well done maintenance pans is because I'm required to launch the maintenance plan with a scheduler (INDESCA).

    I know, this is completely stupid but I'm required to 🙁

    I have not used INDESCA but it sounds similar to a lot of other schedulers. After a quick search it appears to have a CmdShell agent on each server meaning you can have it invoke your MPs using a local call to DTExec on the server hosting SQL Server, i.e. you do not need to involve SQL Server Agent to kick off the job unless pushing them through Agent is a requirement to store job history, although if your org planned to leverage INDESCA for centralizing job history (what I would have done) then using Agent should not be a concern.

    SQL Server Agent is useful however to determine the command line you should use for DTExec. SQL Server Agent jobs steps that call MPs are just wrappers for calls to DTExec. If you open the SQL Server Agent job in question and view to the Step properties you'll see a Command Line tab which contains the info SQL Agent would pass to DTExec.

    If you prepend the info with "<path to DTExec on the target server>\DTExec.exe " and pass that to the INDESCA agent it will run your MP. Again, if INDESCA is like a lot of other schedulers I have looked at, it will easily be able to interpret the return code from DTExec. The call to DTExec is synchronous and will be accurate in terms of whether the MP succeeded (0) or failed (1).

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 24, 2012 at 9:12 am

    #1504827

    matthieu178 (6/24/2012)

    Hi firt of all thanks for the answers.

    Indeed I'm using the sp startjob.

    Unfortunately I don't have much knowledge in .net programming either, even with powershell commandlets.

    For the second question on why I want to complicate the wonderfully well done maintenance pans is because I'm required to launch the maintenance plan with a scheduler (INDESCA).

    I know, this is completely stupid but I'm required to 🙁

    If INDESCA has some sort of wonderful schedule reporting tools, it may not be so stupid. If it doesn't, then, yeah, I agree... completely stupid.

    Either way, thanks for taking the time to post the feedback on this. I appreciate it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 24, 2012 at 9:52 am

    #1504830

    Jeff Moden (6/24/2012)

    matthieu178 (6/24/2012)

    Hi firt of all thanks for the answers.

    Indeed I'm using the sp startjob.

    Unfortunately I don't have much knowledge in .net programming either, even with powershell commandlets.

    For the second question on why I want to complicate the wonderfully well done maintenance pans is because I'm required to launch the maintenance plan with a scheduler (INDESCA).

    I know, this is completely stupid but I'm required to 🙁

    If INDESCA has some sort of wonderful schedule reporting tools, it may not be so stupid. If it doesn't, then, yeah, I agree... completely stupid.

    Either way, thanks for taking the time to post the feedback on this. I appreciate it.

    "Reporting capabilities" is quite ambiguous. Under any definition however, it is only one of many relevant features to evaluate when looking into centralizing job scheduling.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 24, 2012 at 3:07 pm

    #1504849

    Actually, I said "Scheduling Reporting Capabilities" which is a bit less ambiguous. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 24, 2012 at 3:58 pm

    #1504852

    Jeff Moden (6/24/2012)

    Actually, I said "Scheduling Reporting Capabilities" which is a bit less ambiguous. 🙂

    When I evaluated enterprise job schedulers I evaluated at least 10 products on at least 20 criteria, reporting being only one. If your definition of "scheduling reporting" is the same as mine (reports showing upcoming jobs in a Gantt-like chart) then the product that was purchased did not include them. The job schedules for the tool that was chosen were stored in a SQL Server database and could be drawn into a report, but it was not provided as part of the package. Cross-platform job running capabilities, load balancing, granular security to the scheduler console and extensibility ended up being the major decision points.

    One could write fancy reporting off the msdb job tables, and one would need to if they wanted that because Microsoft has not invested the time to include it in the product, as you no doubt know given your comments. However the lack of built-in reporting in SQL Agent does not seem to bother most that use it enough to switch to something else. It all boils down to whether the benefits outweigh the cost of having to develop your own reports, if you even want them. I would venture a guess that fancy reporting might begin at the top of everyone's list when it comes to picking an enterprise job scheduler, however it drops down the list through the course of the evaluation as the value of core functionality bubbles to the top.

    edit: spelling

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 25, 2012 at 12:54 am

    #1504896

    Thanks for all your replies.

    Basically, the place I'm working at has strange ways of working, I totally get the fact that a scheduler in a big environment is fine, problem is SQL has such nice tools integrated.

    In the end I used a powershell command to execute DTExec as shown before and for the moment it seems to work fine (great idea).

    Otherwise apparently I would need to use some management shells but I have no clue on how to do this.

    I was requested to do this in a synchronous way...

    DTExec might not be THE best solution but for the moment it seems to give me a good result

    Thanks for the help.

    I will still be looking this post to see if anybody has another great idea 🙂

Viewing 12 posts - 1 through 11 (of 11 total)

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