March 12, 2010 at 6:51 am
Hi there,
I'm wondering if anyone can explain whether I should be using Powershell instead of Sqlcmd, or as well as, which is best for certain situations and what the main differences are between them.
Currently, I'm looking to widen my SQL knowledge and Sqlcmd looked a logical next step (I've already managed to get it to perform some usual admin tasks) - but then Powershell came along!
Thanks
March 12, 2010 at 6:58 am
sqlcmd is a command line utility for firing sql commands off within SQL, and so can be incorporated into a .bat file.
powershell can be used to run SQL commands but can do a whole lot more, it was actually designed more for windows admins as a shell scripting language.
If you just want to run simple SQL commands against a single server, sqlcmd should be fine, if its getting more complex and say involves multiple servers, powershell is the way to go.
SQLcmd is easy to use. Powershell needs learning, but its definitely worth knowing.
---------------------------------------------------------------------
March 12, 2010 at 7:04 am
simply sead :
Powershell is the new vbscript.
SQLCMD is only intended for usage related to sqlserver.
So .... two different worlds with a bit of overlap.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2010 at 10:14 am
PowerShell all the way. It will give you so much more. MS is betting the house on it like it did with .NET. I reckon that all new admin tools will be based on it.
SQLCMD will live forever for backwards compatibility though.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 16, 2010 at 6:33 am
I may as well pile on here since we clearly don't have a consensus.
Either will work.
Either will work with multiple servers, multiple databases, etc.
However, PowerShell offers much more flexibility and power (ha) than sqlcmd. It can do everything that sqlcmd can do and then more. BUT, powershell is only available in SQL Server 2008 and the newer OS's. To have it available for 2005 & 2000 (which it works with, just fine) you either have to have those installed on new servers or you'll need to install PowerShell. It's something to keep in mind before you dedicate yourself to using this tool as your primary command-line operating method.
My recommendation, go for PowerShell. It offers more and with Microsoft's huge push to use PowerShell in all it's various server applications, you'll be well served by learning it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2017 at 4:54 am
george sibbald (3/12/2010)
sqlcmd is a command line utility for firing sql commands off within SQL, and so can be incorporated into a .bat file.powershell can be used to run SQL commands but can do a whole lot more, it was actually designed more for windows admins as a shell scripting language.
If you just want to run simple SQL commands against a single server, sqlcmd should be fine, if its getting more complex and say involves multiple servers, powershell is the way to go.
SQLcmd is easy to use. Powershell needs learning, but its definitely worth knowing.
January 9, 2017 at 11:55 am
I frequently call sqlcmd (Invoke-SqlCmd) from within powershell scripts, so... both?
January 10, 2017 at 8:13 am
I'm in the both camp. I use SQLCMD to do lots of simple stuff, it's easy to work with and I'm comfortable.
However, when I want to do more scripting that's programming, that requires me to process results and do more than run a query, PoSh is what I turn to. I do struggle with it a bit still, but the new SqlServer module the dbatools are winning me over.
It does take time to get used to the syntax and structure. The $_.property threw me for ahwile, and -eq instead of = still gets me. I'm getting better and part of that is mucking around on some project every week.
January 10, 2017 at 12:00 pm
Steve Jones - SSC Editor (1/10/2017)
-eq instead of = still gets me
Yep, catch myself out with that one all the time (and -gt instead of > etc. etc...)
January 15, 2017 at 11:28 pm
Gazareth - Monday, January 9, 2017 11:55 AMI frequently call sqlcmd (Invoke-SqlCmd) from within powershell scripts, so... both?
That is NOT the same. Calling "sqlcmd.exe" from within PowerShell will give you different output than calling the same with "Invoke-SqlCmd". Just look at the type you get with each.
If I run this in PowerShell I will get just a basic System.String type:
$results = sqlcmd -S myserver -Q "SELECT name FROM sys.databases"
$results.GetType()
If you run this in PowerShell you will get a more robust object, System.Array, that you can manipulate row-by-row utilizing the power in PowerShell:
$results = Invoke-Sqlcmd -ServerInstance myserver -Query "SELECT name FROM sys.databases"
$results.GetType()
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 16, 2017 at 7:37 am
Shawn Melton - Sunday, January 15, 2017 11:28 PMGazareth - Monday, January 9, 2017 11:55 AMI frequently call sqlcmd (Invoke-SqlCmd) from within powershell scripts, so... both?That is NOT the same. Calling "sqlcmd.exe" from within PowerShell will give you different output than calling the same with "Invoke-SqlCmd". Just look at the type you get with each.
If I run this in PowerShell I will get just a basic System.String type:
$results = sqlcmd -S myserver -Q "SELECT name FROM sys.databases"
$results.GetType()
If you run this in PowerShell you will get a more robust object, System.Array, that you can manipulate row-by-row utilizing the power in PowerShell:$results = Invoke-Sqlcmd -ServerInstance myserver -Query "SELECT name FROM sys.databases"
$results.GetType()
Oh, I'm aware they're different (which is why included the cmdlet name); I was more trying to get at that they're complimentary rather than competing tools.
January 16, 2017 at 5:17 pm
I'm in the "usually avoid both" camp, although there's always the rare exception. In many cases that I've seen, people are simply using PowerShell to do things that should actually be done in SQL Server. In fact and as some have already pointed out, many have taken to writing PowerShell to run SQLCmd instead of building a stored procedure.
Sure, there's some huge utility in both PowerShell and SQLCMD but they shouldn't be used as a replacement for stored procedures nor should they be used to build things like centralized backups, as seems to be all the rage. Just imagine what will happen to the log files on all of the remote systems if that centralized back system goes offline or fails. If that happens, I hope you have an excellent script to properly shrink and resize all of your transaction log files on those other systems. 😉
Just as people say about SQL, so it is true with PowerShell. "Just because you can do something in PowerShell, doesn't mean you should."
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2017 at 9:40 am
I hate centralized systems. That creates a single point of failure that depends on lots of stuff (Security, networking, etc.) working correctly.
I do think PoSh works well for backups, since I can more easily move files to places I want them, rename them, find the latest one, etc. easily. However, I would script this is the same process on every instance, letting each one manage itself. I don't know that this is easier in PoSh than T-SQL, but it's not really harder with new cmdlets, and the file work is easier.<
January 17, 2017 at 9:57 am
Steve Jones - SSC Editor - Tuesday, January 17, 2017 9:40 AMI hate centralized systems. That creates a single point of failure that depends on lots of stuff (Security, networking, etc.) working correctly.I do think PoSh works well for backups, since I can more easily move files to places I want them, rename them, find the latest one, etc. easily. However, I would script this is the same process on every instance, letting each one manage itself. I don't know that this is easier in PoSh than T-SQL, but it's not really harder with new cmdlets, and the file work is easier.<
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?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2017 at 11:07 am
Jeff Moden - Tuesday, January 17, 2017 9:57 AMSo, 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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply