Powershell vs Sqlcmd

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  January 17, 2017 at 11:21 am

  #1923236

  Yes, execute in SQL Agent, but could be with another scheduler. Some companies I work at have had enterprise schedulers with agents on each machine, but I've tended to stick with SQL Agent. The error handling and reporting can be better in PoSh, though for backups or simple tasks, it isn't needed.

  No large reasons to use Posh or T-SQL or to use T-SQL over Posh. Either can work well. Some tasks seem to fit better in one or the other.

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  January 17, 2017 at 11:39 am

  #1923246

  Sue_H - Tuesday, January 17, 2017 11:07 AM

  Jeff Moden - Tuesday, January 17, 2017 9:57 AM

  So, again... how do you schedule it to run and from where?  Can this type of thing easily be done using some Posh task in SQL Agent?

  I agree with what you are saying and I don't do backups using Posh but I do manage the backup files with it (i.e. deleting the ones xx days old) since it is a really simple, easy to understand kind of script. .
  Job steps have Powershell for the type now. I think it was added in 2008, not sure on that.
  There are certainly issues with what can be done on what version of Powershell, what version OS/SQL you are running so I'm not sure it's as straightforward as is sometimes indicated. If a company hasn't really adopted Powershell, I would have concerns about using it as it seems every DBA can (or should) be able to troubleshoot a t-sql script. I don't think the same can be said for a Powershell script. Just my take.

  Sue

  I appreciate the feedback, Sue.  That's pretty much my take on it, as well.  I also find the resizing of columns in the returns from PowerShell a bit annoying because doing a screen-scrape from the output of xp_CmdShell does seem to be one of the easiest and highest performing methods of getting PowerShell returns into a table.  Yes, I agree that there are ways to do that from PowerShell but they seem too complex or require you to download someone else's code, which isn't always possible on more secure installations.

  --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)