But 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.
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.
In 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
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.
The 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
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.
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…
This is helpful for normalizing a bunch of wide, related tables into a subclass/superclass pattern.
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.
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.
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.
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.
So in just 1 minute and 34 seconds you have migrated all of the following from one server to another.
All SP_Configure settings
Any Custom Error Messages
Any SQL Credentials
All Linked Servers
Database Mail along with Configuration and Profiles and Accounts
All user objects in System Databases
All Backup Devices
Any System Triggers
All User Databases
Any Data collectors
Any Security Audits
All Endpoints, Policy Management, Resource Governor, Extended Events
And Finally All SQL Server Agent Jobs, Schedules, Operators, Alerts
These are probably very small databases (as it was a test instance), but dbatools is quite impressive.