Generate SQL scripts in powershell

I want to be able to generate simple repetitive scripts in SQL using Powershell as the scripting language.
For example, If I want to generate a SQL GRANT command for every table in a database, and for every category of user in the database, the generic grant command might look like this:

grant $privs 
   on $table 
   to $user;

Here, $privs, $table, and $user are parameters to the Grant command, expressed as powershell variables.

I want to step through a list of data that could be applied to this generic form to produce the actual script. The list of data might look like this:

privs       table       user                
-----       -----       ----                
ALL         Employees   DBA                 
READ        Employees   Analyst             
READ, WRITE Employees   Application         
ALL         Departments DBA                 
READ        Departments Analyst, Application

This should generate five SQL GRANT commands.

Full Disclosure: I am asking this question so as to be able to provide my own answer.

Go to Source
Author: Walter Mitty

Azure Pipeline PowerShell@2 Task can’t run ‘invoke-build’ command as inline script (build PowerShell Module)

I’m trying to build a powershell module in an Azure pipeline (dev.azure.com).

I’ve setup an initial pipleline and then building it up incrementally in small steps, because inexplicable errors are encountered far too easily on Azure.

I’ve added a ‘Prepare’ stage:

name: $(Build.DefinitionName)_$(Date:yyyyMMdd))

trigger:
- master

pool:
  vmImage: 'ubuntu-latest'

variables:
  major: 0
  minor: 0
  patch: $(Build.BuildID)
  buildVersion: $(major).$(minor).$(patch)

stages:
- stage: Prepare
  jobs:
    - job: Prepare
      steps:
      - powershell: .bootstrap.ps1
        displayName: 'Install pre-requisites'

This is based on the original yaml that you get by default when you start a new Pipeline, so the part invoking the bootstrap file is what I got generated for free (This part works ok).

The bootstrap uses a dependencies file into which you can declare your dependencies and these are subsequently imported. One of those dependencies is InvokeBuild which contains the Invoke-Build command. So I would expect to be able to use the Invoke-Build command in subsequent stages.

The next part of the yaml pipeline is as follows:

- stage: Build
  jobs:
    - job: Build
      steps:
        - task: PowerShell@2
          inputs:
            targetType: 'inline'
            script: 'invoke-build build'

The invoke-build build, in itself is ok valid and works on a local host.

The Build job fails with the following error:

/usr/bin/pwsh -NoLogo -NoProfile -NonInteractive -Command . '/home/vsts/work/_temp/edb3758b-8e16-448d-bea2-9ba18acb9693.ps1'
Invoke-Build: /home/vsts/work/1/s/do-build.ps1:3
Line |
   3 |  Invoke-Build build
     |  ~~~~~~~~~~~~
     | The term 'Invoke-Build' is not recognized as the name of a
     | cmdlet, function, script file, or operable program. Check the
     | spelling of the name, or if a path was included, verify that
     | the path is correct and try again.

I discovered there is another way to invoke the build with the PowerShell@2 task using the filePath input:

- stage: Build
  jobs:
    - job: Build
      steps:
        - task: PowerShell@2
          inputs:
            filePath: '$(System.DefaultWorkingDirectory)/do-build.ps1'

the do-build.ps1 script is defined as:

Set-Location .Elizium.FakeBuddy
Invoke-Build build

(Elizium.FakeBuddy is the name of my PowerShell test module being built and that directory contains the InvokeBuild build script)

but this fails with the exact same error.

So why is the invoke-build command not available in the powershell session at the build stage, even though the Prepare stage has already imported the module.

The only thing I can think of is that different stages somehow don’t share the same powershell session, but I don’t know if this is correct, this seems unlikely, it doesn’t make sense that different stages dont share the same session.

EDIT: I tried an alternative version where the Build job is declared inside the same stage as Prepare, but this makes no difference, still a problem with Invoke-Build command.

Go to Source
Author: Plastikfan