Maintenance plan with powershell

Question

Maintenance plan with powershell

matthieu178

Old Hand

Points: 368
More actions
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

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

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

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

matthieu178 Old Hand Points: 368 More actions · Answer 2

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.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

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)

matthieu178 Old Hand Points: 368 More actions · Answer 5

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 🙁

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

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)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 9

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)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 10

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

matthieu178 Old Hand Points: 368 More actions · Answer 11

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 🙂