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

Dynamic config file generation using powershell

I am trying to build a config file for a monitoring app for all our SQL Servers. For this I need to add all instance details of a server in a config file(i.e. one file will be generated per server and not instance). So even if server has multiple instances, it will only have one config file with its details of all instances mentioned in it. For a server with only single instance it is not a problem. What I need help is on a server with multiple instances. For e.g. If server1 has two instances Instance1 and 2 then config file should look like below and exported to a text file. Likewise for a single instance server there will be only 1 line without comma at the end and exported to a text file.

"Server=Server1Instance1;Port=50001;User ID:sqltest;password:****",
"Server=Server1Instance2;Port=50002;User ID:sqltest;password:****"

I am getting all server instance details from a SQL Server database stored in format ServerName, InstanceName, PortName. Could someone please guide me how do I achieve it using Powershell? Thanks!

Go to Source
Author: SQLPRODDBA

Powershell – include Get-Date in .log file inside the Add-Content Cmdlet

I’ve made a small Powershell Script which deletes alle files and folders except specific ones.
The script itself works pretty good but I have a lot of troubble getting the logging to work. I’m currently on a good way with the Add-Content Cmdlet which works good. The only thing I now want to include is a small Get-Date Cmdlet inside the Add-Content which also includes the current time in the log when the specific file/folder was deleted. But I just can’t get it to work properly. Can someone help me?

Here is what I got so far:

Get-ChildItem -Path 'C:sample*notesdata' -Recurse -exclude names.nsf |
Select -ExpandProperty FullName |
Where {$_ -notlike 'C:sample*notesdataRoaming*'} |
Where {$_ -notlike 'C:sample*notesdataArchive*'} |
sort length -Descending |
Remove-Item -force -Recurse -Verbose 4>&1 | Add-Content -Path .ergebnis.log, .ergebnis2.log -Value (Get-Date)

The file “names.nsf” and the folders “Roaming”, “Archive” will not get deleted.

Thanks for your help 🙂

Go to Source
Author: Valle