posts from database land

I’ve been working on a PowerShell script which will be used to build out and configure our new SQL Server AGs. As part of the build, I wanted to install Ola Hallengren’s Maintenance solution and Adam Machanic’s sp_WhoIsActive, as those are standard in our production environment.

I have a function which reads a list of .sql files into an array, then iterates through the array reading in each file into a string using get-content, and then calls a function to execute the string.

So far, so good. That works great for my scripts which configure MIN/MAX memory, setup Database Mail so we get our alerts, create our server management jobs, and correct tempdb. However, 3 of the scripts were not running. No errors, just failing silently.

At one of my coworker’s suggestion, I wrote the contents of the string imported from the .sql file to the script log. It looked fine, nothing had been truncated. Curiouser and curiouser! I then copied the string from the log into SSMS. AHA!

Get-content removes line feed, pulling the entire file into a single long line. That first ‘–‘ commented out the remainder of the script!
After a bit more googling, and running get-help, I discovered that PowerShell 3.0 and greater have a new parameter for get-content, -Raw, which preserves carriage returns. Hooray!

Happily, I now only have one script which will need to be run by hand; I’ll just have to solve that problem another day :)

Happy coding!