A Fine Slice Of SQL Server
Shane O’Neill shows the trick to excluding properties in Powershell:
Hmm,
Properties,ExecutionManager, andURN(hidden from view) are not needed. That is not a problem, I’ll just pipe them toSelect-Objectand include them in the-ExcludePropertyparameter.
It’s not quite as straightforward as you’d imagine, but Shane shows you how to do it.
Comments closedBut what Sander wanted was to validate the value of the second parameter depending on the value of the first one. So lets say we wanted
- If word is sun, number must be 1 or 2
- If word is moon, number must be 3 or 4
- If word is earth, number must be 5 or 6
We can use the ValidateScriptAttribute to do this. This requires a script block which returns True or False. You can access the current parameter with $_ so we can use a script block like this
Any Powershell post with a link to June Blender is automatically 5% better in my book.
Comments closedDrew Furgiuele explains how to use the .NET Stopwatch class in Powershell:
We can see I cleared the history buffer of my session ( Clear-History ), then ran a script. It’s nothing fancy; just connecting to my local instance of SQL Server and outputting a list of tables to a text file. With Get-History , I can see every command I put in the buffer, and using expressions I can calculate how long a command took.
And that’s great and all, but that’s the entire execution time. If there are multiple steps to your function, how long does each step take? If your script execution is 186 seconds, how much of time is spent on a database query? What about a loop? How long does each iteration take? Are you writing to a network share, and want to know what the latency is? Get-History isn’t the tool of this, but thankfully we have other methods.
I use the stopwatch a lot for similar things; it’s a useful tool.
Comments closedIn this post, I want to publish a few functions that I created around SSRS. They are related to and depend on each other.
Get-SSRS – Given the SSRS URI returns the WSDL endpoint
Get-SSRSReport – Returns one or more reports based on inputs
Get-SSRSSharedDataSource – Returns one or more shared data sources based on inputs
Get-SSRSReportDataSource – Returns the data source information on a report by report basis based on inputs
Set-SSRSReportDataSource – Sets the data source of a report to the given data source.
Install-SSRS – Deploys an SSRS report to a specific folder and also optionally sets the datasource for the deployed report
Very useful.
Comments closedDrew Furgiuele shows how to install the SqlServer Powershell module from the Powershell Gallery:
That’s because, out of the box, Server 2012 R2 is running PowerShell 4.0. These Gallery cmdlets require PowerShell 5. To upgrade, you either need to upgrade to PowerShell 5.0 and that means installing Windows Management Framework 5.0. This is compatible with versions of Windows as far back as Windows 7, and Windows Server as far back as 2008 R2. Anything earlier, and you’re out of luck. This also requires the .NET framework 4.5 (or above). That means system updates, which could (potentially) lead to system reboots. Plan (and for the love God test) accordingly!
There’s a couple other hitches as well. One, and this sort of goes without saying, you need internet access for this to work. If your machines are behind any kind of filtering or firewall restrictions that prevent them from talking out to the internet, you’ll need to either open them up or use the Save-Module feature to download the bits and install them yourself. Secondly, you need Administrator access for this to work. And three, if you do install them manually, you might have different versions installed for different users (or service accounts).
They’ve made it nice and easy, so read Drew’s post and give it a try.
Comments closedThe aim of this blog post is twofold, it is to explain how:
- A “Self building pipeline” for the deployment of a SQL Server Data Tools project can be implemented using open source tools
- A build pipeline can be augmented using PowerShell
What You Will Need
Jenkins automation server
cURL
SQL Server 2016 (any edition will suffice)
Visual Studio 2015 community edition
A windows server, physical or virtual to install all of the above on, I will be using Windows Server 2012 R2 as the operating system
Automated integration via CI is extremely helpful, and Chris makes it look easy in this post.
Comments closedShane O’Neill compares table columns with T-SQL and Powershell:
That is not really going to work out for us…
So I’m not liking the look of this, and going through the results, it seems to me that these results are just not useful. This isn’t the computers fault – it’s done exactly what I’ve told it to do – but a more useful result would be a list of columns and then either a simple ‘Yes’, or a ‘No’.There’s syntax for this…
PIVOT…
This is helpful for normalizing a bunch of wide, related tables into a subclass/superclass pattern.
Comments closedChris Lumnah shows how to use dbatools to reset a SQL authenticated administrative account:
As I was going through my environment, I realized I created a new domain controller for my tests. This DC has a new name and domain name which is different from my other VMs. I quickly realized that this will cause me issues later with authentication. No worries. I will just boot up the VMs and then and join them to the new domain. Easy-peasy. Now let met go test out my SQL Servers.
DOH!!
I received a login failure with access is denied. Using Windows Authentication with my new domain and recently joined server is not working. Why?…..Oh right, my new user id does not have access to SQL Server itself. As I sit there smacking myself in the head, I am also thinking about the amount of time it will take me to rebuild those VMs. Then it hit me!!!
Read on to see the solution, including a Powershell one-liner showing how it’s done.
Comments closedDave Mason shares some difficulties he has had grokking Powershell:
The developer in me thinks this is nuts. Run the same few lines of code twice, with no changes in between, and get different outputs? Madness!
Here’s another example. Nothing too complex here: I connect to an instance of SQL, SELECT CURRENT_TIMESTAMP, and show the returned value in the output window. (There’s a fixable issue here that I would go on to discover later. But hold that thought for now.)
Even when you’re conceptually familiar with a language, getting into the particular foibles of that language can expose all sorts of behavior which is strange to newcomers.
Comments closedPhil Factor shows how to generate a quick linear regression using SQL, Powershell, and Gnuplot:
It looks a bit like someone has fired a shotgun at a wall but is there a relationship between the two variables? If so, what is it? There seems to be a weak positive linear relationship between the two variables here so we can be fairly confident of plotting a trendline.
Here is the data, and we will proceed to calculate the slope and intercept. We will also calculate the correlation.
It’s good to know that this is possible, but I’d switch to R or Python long before.
Comments closed