Oh, hey, happy Saturday
First, I’d like to apologize to Conor Cunningham for the blatant theft of a blog title.
That’s what you get for not posting in three years (like the desert miss the rain~).
Why am I here? Why am I writing PowerShell, my sworn enemy?
I do it for you. I’m trying to be a good open source maintainer, so you don’t end up with a useless (hmpf! useless!) script on your servers. Instead of saying “go ahead and drop it if you don’t want it”, I’m going to properly address the issue.
Here’s where I’ve been so far:
Well, alright, so at least I’m narrowing things down.
He’s going to spend $10,000 on brunch for Ernie after this.
Adventures In PowerHELL
But hey, you know, PowerShell has a Copy-Item command.
According to the laws of “what’s the most obtuse way we can give people to clobber files together”, we have to use not one but TWO commands!
We have to Get-Content and pipeline (|) it to Set-Content.
Sounds reasonable, let’s get going.
Where will we go next? Follow along!
Alright, let’s try a different command. Maybe the administrator thing is silly. Maybe I’m stepping in the wrong direction.
What… What doesn’t exist?
Maybe I’ll try using -LiteralPath, because that’s an option. I like options. If I could drive, my Citroen would have 42 ski racks.
WE ARE NOW RUNNING AS ADMINISTRATOR, EVERYONE STEP BACK
Access is still denied running in Admin mode. That’s nice. Time to stick in some humorous dialog while I try to figure out something else to do.
Breaking through!
ACCESS NOT DENIED!
DEAR GOD WE HAVE FILTERING
We’re having fun, really.
Alright, now we have to try to exclude sp_BlitzQueryStore!
So uh, about that.
Holy cow, it took 15 minutes of tinkering to get this working.
Apparently if you want to do what I’m trying to do, you can’t use (???) -Filter. Maybe? Something like that? You need to use -Include and -Exclude. And you need to… surround your path in quotes. And use a wildcard at the end? I don’t even understand how I ended up here, honestly, I just kept changing things until something worked.
[Sometime around 10:30]INTUITIVE AND USER-FRIENDLY THEY SAID
LEARN IT IN AN AFTERNOON THEY SAID
THERE GOES YOUR SATURDAY MORNING, THEY NEVER SAID
Finally this is what worked.
|
1 |
Get-ChildItem -Path "C:\Users\$env:username\Documents\GitHub\PublicTools\*" -Include sp_Blitz*.sql -Exclude sp_BlitzQueryStore.sql |
So an hour and a half later, I have a list of files. I still have to get their content and set their content. This is fun. Learning. Hey, does anyone have a book on Replication?
Alright, so knowing just enough to be dangerous, after a pipeline I can pass in [something] from the previous command. So, I tried $.Name, and $.Path and $.FullPath. They all threw about the same error.
[Sometime around 10:45]My wife is now standing in the office doorway asking me when I’ll be done. Good question.
At first glance, never.
Oh… Select-Object.
Shout out to Cody Konior for being the only person to blog about this using -Property *.
Alright, so now that I can get the right path, all I need to do is figure out how to pass that to Get-Content. Fingers crossed. Here we go.
Oh. I need a ForEach loop. Okay.
|
1 |
Get-ChildItem -Path "C:\Users\$env:username\Documents\GitHub\PublicTools\*" -Include sp_Blitz*.sql -Exclude sp_BlitzQueryStore.sql | ForEach-Object { Get-Content $_.FullName } |
If anyone’s wondering, Brent left 45 minutes ago to to try to smother himself with a pillow.
But success! We’re now able to print the contents of every file out to the console. That must mean we’re close to being able to dump them to a single file.
HOLY MOTHER OF GOD IT WORKED
|
1 |
Get-ChildItem -Path "C:\Users\$env:username\Documents\GitHub\PublicTools\*" -Include sp_Blitz*.sql -Exclude sp_BlitzQueryStore.sql | ForEach-Object { Get-Content $_.FullName } | Set-Content -Path "C:\Users\$env:username\Documents\GitHub\PublicTools\Install-Core-Blitz.sql" -Force |
Wrap up
If you’re out there wondering why people hate PowerShell, here’s a good example of why. There was a whole lot of nonsense just to get some files copied into a single file. None of it was intuitive, and it took a lot of searching and tinkering to get things right. I can’t imagine what it’s like when you have to do something more complicated.
Now I have to go watch the Emoji movie sober and my wife is mad at me.
Thanks, PowerShell.
42 Comments. Leave new
Guys, I love your SQL work, it has helped me a lot. But when it comes to pshell (as with most general purpose programming languages) if you aren’t going to RTFM then don’t expect to have much success typing random syntax that seems to you that it should work the way you would prefer.
In my ETL work I regularly convert complicated and unneccesarily complex SSIS packages using multiple temp tables, multiple sql statements, multiple stored procs (that still ends up being buggy) into a few lines of pshell. You just need to RTFM!
Ken! It’s almost like you found the point by accident. As someone who doesn’t regularly convert complex etc and so forth, even reading the manual and blog posts and Stack q&a doesn’t make things easy.
[[ It’s almost like you found the point by accident. ]]
LOL. story of my life!
I agree it is not easy to learn pshell. It is however IMHO a gigantic improvement for windows system management and general text and file tooling. Most very powerful tools are like that. You can hurt yourself very easily if you don’t know what you are doing.
Anyways, again, want to say thank you for the great SQL work you guys do. PLUS I should cut you some slack – because it is Saturday!
ROFL. I will say my initial work with Powershell was much the same example. I personally feel like the get child item is still one of the most confusing cmdlets there is for filtering. Once you get over the initial learning curve it can be incredible and a major Time Saver, but there is definitely work that has to be put in first. Some interesting use cases I’ve recently come across:
Ran network latency test from each server to push to capture average network latency as load scaled up.
Ran parallel queries against SQL server after dynamically replacing tokens in template query ( was less work than dynamic SQL)
Copied SQL backup from one server to another with 2 lines of code using dbatools.io
Generated average latency y metrics on SQL query for app by running dbatool.io Test-DbaNetworkLatency
For me it’s been a painful but rewarding process that is now more reward than pain. 🙂 don’t forget the slack channel has a PowerShell group and if you get stuck anyone would be glad to help, saving ya some time.
Great article though. Enjoyed it 🙂
^ what he said
Thanks, Sheldon. Yeah, the dba tools crowd does a great job. I have a high level of respect for their ability to deal with PowerShell.
Computers are only intuitive as your familiarity with them. PowerShell is no different. I am very familiar with PowerShell and the solution to your problem seems intuitive to me. But, this was not always the case. Now that I have become proficient with PowerShell, I have found that many things are easier than alternatives.
Stick with PowerShell. I am sure as you become more familiar with PowerShell, it will become more intuitive.
[[Computers are only intuitive as your familiarity with them]]
not sure I agree. As a programming language’s syntax moves closer to natural language, programming in that language become more “intuitive”. As the notation and syntax moves further away from natural language, it becomes less intuitive. But natural language is ambiguous,imprecise, and open to misinterpretation. Pshell is terse, almost formulaic. Very unlike like natural language. But like mathematical expressions vs. natural language, precise and concise, and capable of tremendously powerful expressions succinctly.
I’d respectfully disagree that posh is succint. Native cmdlets are really wordy unless using aliases which I know aren’t a great practice. For example….
The .net way in PowerShell
If(![io.directory]::exists($path)){do this}
The PowerShell cmdlet way
If(!(test-path $path -itemtype container)){do this}
That is also using positional arguments for the first path.
I’d say my favorite thing had been using .net in PowerShell to be flexible with devops type tasks for build and database automation.
It was VERY painful at the beginning but now I think nothing of throwing together a generic list of string and get generating a query queue with PowerShell parallel the runspaces. Lots of pain to get there but MAN is it worth it!
Kenneth,
Natural language is intuitive because we are familiar with it. It’s ambiguous nature is what makes natural language not intuitive. English is full of phrases and expressions that mean something different than the words used. This fact makes it difficult and non-intuitive for someone learning English. One difficulty English speaking people have a difficulty with learning Chinese is using the same sound with different pitches have different meanings. To the Chinese, this is intuitive, but not for English speaking individuals, such as myself.
The biggest thing about PowerShell, in my opinion, that makes it intuitive is it’s consistency, not just in it’s commands, but also the parameters of these commands. But, at the same time, some verbs are not always intuitive, such as when create, add, or new should be used for creating a function.
Intuitive goes beyond spoken, programming, or scripting languages in relation to computers. For example, selecting an object or multiple objects. With a mouse, click on the object, or control click for multiple. On a touch screen, long press for each object selected. Neither are intuitive for someone using a computer for the first time, but after couple of hours, it becomes second nature.
As an example of one approach I might take, not better just what I’d be more comfortable with…. Might give you some future ideas. Upgrade to PowerShell 5 for sure. Lots of cool stuff.
#requires -version 4.0
$WorkingDirectory = “C:\Users\$env:username\Documents\GitHub\PublicTools”
$InstallBlitzFileName = [io.directory]::combine($WorkingDirectory,”Install-Core-Blitz.sql”)
set-location $WorkingDirectory
$files = Get-ChildItem -Include sp_Blitz*.sql -Exclude sp_BlitzQueryStore.sql
@($Files).ForEach({
$_ | get-content -raw -encoding UT8 | out-file $InstallBlitzFileName -force -append -encoding UTF8
})
Hahaha, that looks great, but I understand none of it. If you write a blog post with an explanation, I’m all ears.
Or whatever it is that absorbs blog posts.
Liver? Kidneys?
oh boy, Erik vs. Powershell…who won? Were there any dead or injured? Erik, I’m quite suprised, you as a very cool SQL-Admin and still fighting PowerShell? Hm.
Yeah! Back when I had a real job, I didn’t have any use for PowerShell aside from running Cluster commands here and there.
So I get stuck in this weird feedback loop. Every time I use it it’s painful, so I never use it.
I’m fine with that, too. No one’s hiring me for my PowerShell prowess anyway 🙂
🙂 “back when I had a real job..”. What does Brent think about this statement? Does Erik take the job here seriously? 😉 but all joking aside: in my option being Windows-Admin using PowerShell is not just “nice to have”, it’s a must-have! You’re right: being a SQL-Admin it’s (still) not a must-have, but using PS in an environment with many SQL-Machines, makes the life so much easier…
Hi Brent , Erik, you both give so much to the sql community , it is really appreciated, reach out to the contributors at http://dbatools.io and I’m sure You will find Dba and power shell guru willing to give to you help you as you have help us. I sit on the fence I marvel at the sql MVP and the powershell MVP . Erik you could turn your pain of learning Powershell in to an instructional video basic Dba skills with powershell. SQL programming is about thinking in sets that is a different mind set solve problems Row by row. Powershell is again a different mind set. All Have advantages, I hope you have the time to gain the knowledge to choose which technology to use.
Your post remindes me of the the first steps of an accidental DBA discover, fear, frustration , failure then confusion why did that work. Please persist with power shell. It is very powerful
Hi Bob,
I think Brent also suggested that I ask someone in the SQL Community Slack channel, but I was overly optimistic about how simple it’d be.
As for a video course — I do very dumb things with PowerShell very clumsily. I’m not sure anyone would want to pay to watch me do impractical things incorrectly for hours on end 🙂
Thanks!
Another enjoyable read, Erik! Thanks for sharing!
I blame my gravitation towards PowerShell on my early DBA years that I spent on the unix platform (Oracle and Informix). There was no GUI and we did a sh*t ton of scripting in either ksh or bash for performing many of the tasks that a SQL DBA would probably do in SSMS. So when I came to Windows/SQL Server about 10 years ago, one of the first things I searched for was a similar tool, and it just so happened that PowerShell was really starting to take hold at the time. The inner workings of PowerShell are much different than a unix shell, but the concepts hold true, so it was a pretty natural fit for me.
That said, I can totally understand your frustration. It can be frustrating to learn anything new, but PowerShell/.NET is vast and I have to agree with you, sometimes not very intuitive. You never know what you don’t know, right?
Thanks for the dbatools.io plug. Great people there and we’re doing a lot of great things! We have a very active presence on slack and on twitter and love to help all levels of PowerShell programmers.
Hey Chris,
Yeah, it can be frustrating, but the problem I run into with PowerShell (vs. other stuff that I need/want to learn) is an enthusiasm gap. I’ll give you an example: the first SQL code I was ever really confused by was Jeff Moden’s Delimited Split 8k function. It was really hard for me to wrap my head around at first, but I thought it was so cool that I really wanted to figure it out. Any frustration didn’t really feel frustrating because I was mesmerized by how it worked. I’m yet to have that kind of moment with PowerShell.
That’s why I’m glad other folks have, and they they write those dbatools. Heh.
Thanks!
Hi,
I would solve filter/exclude with an additional “Where-Object”. Advantage is that you can filter also different properties like date or size.
Get-ChildItem -Path “C:\Users\$env:username\Documents\GitHub\PublicTools*” | Where-Object { $.Name -ne ‘sp_BlitzQueryStore.sql’ } | ForEach-Object { Get-Content $.FullName } | Set-Content -Path “C:\Users\$env:username\Documents\GitHub\PublicTools\Install-Core-Blitz.sql” -Force
if you want exclude mulitple filenames use a string array and filter with “-notcontains”
Get-ChildItem -Path “C:\temp*.txt” | ? { (‘b.txt’,’c.txt’) -notcontains $.Name } | foreach { $.name }
Hi Steffen,
Yeah, if I needed to filter out directories or something, I probably would have ended up there. Just didn’t have any other ‘types’ to work around here.
Nice example, though!
Thanks!
This reminds me of my time trying to understand CROSS APPLY … I still don’t get it 🙂
I wanted to like PowerShell… I was so excited when it first came out, and have a background in both sysadmin (*nix and Windows) as well as programming (including .Net). However, I just find it very awkward and also struggle to find documentation that is actually helpful. Most times I need a task done quickly and end up rapidly failing back to batch files just to get things done in a reasonable timeframe.
Howdy Brian,
Yeah, that’s unfortunately been my experience as well. “Oh I’ll just throw something together with PowerShell and…” hours later I just do it by hand.
I will have to agree with Eric on all this. I’ve been banging my head against PS for a few years. I am somewhat competent (I have deployed a good number of production scripts), but whenever I have to start something new I usually fumble around for awhile (I hate that!!). Even trying to ‘retro fit’ some existing PS code functionality in a new solution, I usually find at the end my new code looks nothing like what I started with (after a few hours of trial and error and research). That is also frustrating. I don’t ever really feel like I am making progress on ‘truly’ understanding the ‘sweet spots’ of programming with PS. Don’t get me wrong, PS is super super powerful and it does stuff amazingly fast and well. Maybe I am just not over the ‘hump’ yet. Of course the latest wrinkle is the new SQL provider (after having spent so much time learning how to configure, invoke and use the last one). The new SQL provider is clearly better and easier to use but it’s yet another thing that needs to be assimilated into my head, while I try to ‘un-learn’ the old ways. Oh well… that’s programming!!
Curtis — my favorite is when you write one script that does one thing, and another script that does another thing, and then you try to feed one into the other and… neither works!
Powershell is great and can help a ton!
For this example sql cmd mode syntax could possibly be a simpler solution.
:r “...\scripts\file1.sql”
:r “...\scripts\file2.sql” etc and the install sql cmd script can live under source control along with everything else.
Try this: Get-ChildItem * | Get-Content | Set-Content output.sql -Force
Add filters where needed
For a DOS solution robocopy would probably be the way to go. It allows excluding files on the command line and it supports wildcards in the file name.
Comparing powershell to DOS is probably not the best comparison.
The tool you’re familiar with is usually going to be less painful than the tool you have never used.
A more apt comparison might be to compare the powershell to C#.
It’s more of a middle ground between a shell and a large language.
Mike — Right, but I explain in the post why I chose not to go with Robocopy.
I should read the entire post before commenting…
“Learn in an afternoon” – that’s after you’ve spent all morning with it, too. See? It all works out.
I appreciate the glimpse into your time w/ PS to do a simple task.
Peter — yes, it’s like the meaning of “day” in Genesis 😉
I’m not trying to minimize your effort, but this appears to do the same thing but with less pain:
Get-ChildItem -Path ‘.*’ -Include sp_Blitz*.sql -Exclude sp_BlitzQueryStore.sql | Get-Content | Out-File -Force Install-Core-Blitz.sql;
It saves a few keystrokes, I guess, but it introduces things that I don’t understand.
What does
'.*'do?Why is
Out-Filebetter thanSet-Content? Or different?I’m not sure getting to this code instead would have been less painful.
for the ‘.*’, I was running this command in the directory where the scripts were. It serves the same purpose as the ‘C:\Users\$env:username\Documents\GitHub\PublicTools\*’ in your eventual command does. As for out-file vs set-content, I feel like the latter is a more general purpose “write to a thing that can be written to” vs “I’m definitely writing to a file”. The main point I was trying to make is that you don’t need to iterate over the files with a foreach loop; get-content will accept and output a stream.
It didn’t seem to do that when I tried, but alright. Anyway, I’m sticking with what I have. I think this blog post pretty thoroughly documents the purpose of the code.
Thanks!
A co-worker and I feel your pain…..we have doubts about this book. LIES
https://www.amazon.com/Learn-Windows-PowerShell-Month-Lunches/dp/1617291080
Ah, well, you know, the other problem is about right here:
> Publisher: Manning Publications; 2 edition (November 25, 2012)
This Thanksgiving, the second edition will be 5 years old. As soon as you finish the book, your skills will be outdated.
@Kelly @Erik There is a more recent book 😉 https://www.manning.com/books/learn-windows-powershell-in-a-month-of-lunches-third-edition I started learning and working with PowerShell before working with SQL Server so my <3 for PowerShell came about from that.
oh god my sides. it was like watching an old man yell at kids to get off his lawn, but in words.
definitely been there though. “what’s this dollar sign underscore nonsense?”
the fun part is when you get into comparison operators. muscle memory says “=” after a where clause, powershell laughs in your face with “-eq”
[…] spend an hour or so with it and tell us how you got on and what and how you learned. Just like Erik and Brent did. You could install one of the community modules like dbatools, dbareports , SQLDiagAPI or the […]