Powershell vs Sqlcmd

Question

Post reply

Powershell vs Sqlcmd

Viewing 2 posts - 16 through 16 (of 16 total)

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

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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)
Intro to Tally Tables and Functions