# Powershell

## tSQLt-Course

Learn how to use tSQLt to professionally unit test T-SQL code. To enroll in the free self-paced course visit here

## Running any AzureRM powershell command tells you to "Run Login-AzureRmAccount to login." even though you are logged in!

• Posted on: 15 November 2017
• By: Ed Elliott

Every AzureRM command I was running I would get an error message telling me to login, I then did a login, checked that I had the right subscription and I was still getting the error message - after logging in 7 times I figured that maybe there was something else wrong :)

It turns out that I had originally installed the Azure RM cmdlets via the Azure SDK and had somehow also managed to install the AzureRM.profile module from the powershell gallery. Having different versions of the AzureRM.profile and the rest of the cmdlets caused hilarity (well not exactly hilarity but hey ho).

The fix was simple, I just deleted the AzureRM.profile from the shared windows modules folder restarted powershell and I only had to login the once.

I found out what was wrong by doing (Get-Command SomeAzureRMCommand) which gave me the module name of the module, then a quick Get-Module for the module that I was using and a ".Path" on the output for that showed I had a different set of modules for the commands compared to AzureRM.profile which contains Login-AzureRMAccount. Something like:

 (Get-Module (Get-Command Get-AzureRMVM).Source).Path
 (Get-Module (Get-Command Login-AzureRMAccount).Source).Path

Tags:

## Watching for powershell changes and running Invoke-Pester

• Posted on: 14 November 2017
• By: Ed Elliott

It seems like more and more recently I have been writing powershell and typescript rather than c# and t-sql and there are quite a few things to like about the tools for both of these (typescript and powershell). One thing I really like with typescript and javascript in general is that it seems everything has a file system watcher so you can have your code ide, a couple of terminals and all your tests run etc in the background.

I missed this from powershell so I wrote a quick module (literally took about 30 mins so is hopefully pretty simple), if you want to run Invoke-Pester in a terminal whenever your code or tests run then you can now do:

 Install-Module PestWatch Import-Module PestWatch Invoke-PesterWatcher

Nice and simple hey :) If you have any arguments that you want pester to see just add them to the call to Invoke-PesterWatcher and this silently forwards them on for you.

The source code is available:

https://github.com/GoEddie/PestWatch

The module:

https://www.powershellgallery.com/packages/PestWatch/

Still not sure? Watch the full glory here:

Enjoy!

Tags:

## DacFxed - Powershell Nugetized DacFx wrapper

• Posted on: 2 August 2016
• By: Ed Elliott

Deploying a dacpac from powershell should be pretty easy, there is a .net api which you can use to open a dacpac, compare to a database and either create a script or get the database to look the same as the dacpac but there are a couple of problems with the approach.

## Problem 1 - Is the DacFx installed?

The first problem is whether the DacFx is installed, the chances are if Visual Studio or SQL Server have been installed then it will be there.

## Problem 2 - Where is the DacFx installed?

Once you know that the DacFx is available, where is it? Depending how you installed it, whether when you installed Visual Studio or via an MSI it will be installed in a different location. Further if you get a recent build it will be in the 130 folder, older builds the 120 or 110 folder. Which one do you have??

## Solution 1

So what I used to do to combat these first two issues is to check into my test projects the DAC folder which includes sqlpackage and just shell out to that to do the install, this works but updates to SSDT come between every 1 and every 3 months, the projects I have done this on are all on old builds of the DacFx and probably will be until something goes wrong and someone updates it. That sucks :(

## Solution 2

It was with great excitement, and I don't say that lightly, that in July the SSDT team announced that they would be maintaining a nuget package of the DacFx. This is really exciting because it means that problem 1 and 2 no longer exist, we can simply reference the nuget package and keeping up to date is pretty simple. While you recover from the excitement that is the DacFx in a nuget package I have something else to get you really excited...

## This means no install to Program Files

I know right, exciting! What this means is that even on hosted build servers (vsts for example) where we don't have admin rights we can still keep up to date with recent copies of the DacFx without having to commit the source control sin of checking in libraries.

## Problem 3 :( Deployment Contributors

If we no longer need admin rights to use the DacFx it means we no longer can rely on admin rights to deploy dacpacs - this means that deployment contributors cannot be copied to program files which is where the DacFx loads contributors from. Remember also that contributors are specifically loaded from the program files (or x86) version of SQL Sever or Visual Studio or whatever version of SSDT you have so it could be from any one of the version DAC folders i.e. 110, 120, 130, 1xx etc.

## Solution 3

There is a solution? Well yes of course otherwise I wouldn't have been writing this! DacFxed is a powershell module which:

• 1. References the DacFx nuget package so updating to the latest version is simple
• 2. Implements a hack (ooh) to allow contributors to be loaded from anywhere
• 3. Is published to the powershell gallery so to use it you just do "Install-Module -Name DacFxed -Scope User -Force"
• 4. Has a Publish-Database, New-PublishProfile and Get-DatabaseChanges CmdLets

Cool right, now a couple of things to mention. Firstly this is of course open source and available: https://github.com/GoEddie/DacFxed

Secondly, what is up with the cool name? Well I did't want to call the module DacFx as I was publishing it to the powershell gallery and hope that one day maybe the SSDT team will want to create a supported powershell module that publishes dacpac's and didn't want to steal the name. DacFxed is just DacFx with my name appended, what could be cooler than that?

Currently to use a deployment contributor you either need to copy it into the Program Files directory or use sqlpackage and put it in a sub-folder called Extensions - neither of these two options are particularly exciting. I needed a better way to be able to include a deployment contributor in some user writable folder and then load the dll's from there. I hope (there is a connect somewhere) that one day the SSDT team will give us an option when using the DacFx to say where to load contributors from - when that happens I commit here to modify this package to support their method so if you do use this then fear not, I will make sure it stops using a hack as soon as possible.

What is this so called hack?

When the DacFx tries to load deployment contributors it does a search of a number of well known directories to find the dll's, it also has a fairly unique way to determine which folder it should use when being called from visual studio - what happens is that it checks whether two folders above the folder the dll is in, there is a file called "Microsoft.VisualStudio.Data.Tools.Package.dll" - if this file exists then it searches in the folder the dll is in to find out if there are any deployment contributors to load. The interesting thing about this is that it doesn't actually load the file, just checks the existence of it - if it exists it searches itself for extensions. So if we have this structure:

Folder 1\Microsoft.VisualStudio.Data.Tools.Package.dll (this can be an empty text file)
Folder 1\Folder 2\folder 3\DacFx Dll's

When you load the DacFx dll's from this folder (to be specific "Microsoft.Data.Tools.Schema.Sql.dll") we get the ability to load contributors from user writable folders (which is the end goal for this).

Problem Solved?

Well no, it would be if it wasn't for the way .net resolved assemblies and powershell CmdLets. If our powershell module is structured like this:

WindowsPowershell\Modules\DacFxed\Cmdlet.dll
WindowsPowershell\Modules\DacFxed\Microsoft.VisualStudio.Data.Tools.Package.dll (remember this is an empty text file)
WindowsPowershell\Modules\DacFxed\bin\dll\Microsoft.Data.Tools.Schema.Sql.dll
WindowsPowershell\Modules\DacFxed\bin\dll\OtherDacFxDll's

What would happen is that out Cmdlet.dll would try to resolve the DacFx and it would not find it as .net doesn't search every sub-folder of the current directory to find dll's to load. If .net can't find the dll locally it will search horrible things like the GAC and if it finds the dll there, load it. This means our sneaky trick to trick the DacFx to load our extensions doesn't work.

Phew, I said hack :)

### This sounds dangerous

Well yes and no, yes it is a little exotic but no in that if you tell the DacFx to load a contributor if this process doesn't work for some reason the worst thing that will happen is you get a "Deployment contributor could not be loaded" error - you won't deploy to a database without contributor you weren't expecting.

So no not really dangerous, just annoying if it doesn't work. I have tests setup and a release pipeline for this that I will cover in another post that make it easy for me to ensure each update to the DacFx can be taken while this still works. If the SSDT team break this behaviour then I won't deploy and then anyone using it can update in their own time.

## How does it work?

You need a dacpac and a publish profile, if you have a dacpac and no publish profile then New-PublishProfile will create a template you can use to get started with.

Publish-Database -DacpacPath "path\to\dacpac" -PublishProfilePath "path\to\publish-profile"

or

Publish-Database -DacpacPath "path\to\dacpac" -PublishProfilePath "path\to\publish-profile" -verbose

or

Publish-Database -DacpacPath "path\to\dacpac" -PublishProfilePath "path\to\publish-profile" -verbose -DacFxExtensionsPath "c:\path\one;c:\path-two"

Anyway, enjoy!