SQLvariant

SQLvariant

# > Before you begin, navigate to a local directory containing the Linked Services files from your ADF
c:\temp\ADF\linkedService
# First, run through all of the ADF LinkedService files to extract all the pieces of the connection information we're loking for.
$LinkedServices =  @()
$LinkedServiceFiles = dir -Filter *.json
foreach($LinkedServiceFile in $LinkedServiceFiles){
    $LinkedServiceContents = Get-Content -Path $LinkedServiceFile | ConvertFrom-Json -Depth 20
    switch($LinkedServiceContents.properties.type){
        'AzureSqlDatabase' {$Connection = $LinkedServiceContents.properties.typeProperties.connectionString}
        'AzureSqlDW' {$Connection = $LinkedServiceContents.properties.typeProperties.connectionString}

SQLvariant

Use with Registered Servers

Use Registered Servers or Central Management Server to run Invoke-SqlNotebook against multiple servers.

$datetime = Get-Date -Format yyyyMMddhhmm

dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' |
WHERE { $_.Mode -ne 'd'} |            
foreach {

SQLvariant

function Get-SqlChange
{    <#
    .SYNOPSIS
        This command runs SQLPackage.exe to compare a .DACPAC file to a database and generate a file.

    .DESCRIPTION
        This command runs SQLPackage.exe to compare a .DACPAC file to a database and generate a either a DeployReport or SQL change file.

    .PARAMETER Action
        Specify the action, currently only "DeployReport" and "Script" are supported.

SQLvariant

Describe "Invoke-DaxQuery" {
    Context "Invoke a DAX Query and test the result"{
        # Run queries and compare them
        $results = Invoke-DaxQuery -DAXQuery "EVALUATE ReportCatalogQuery"

        # Test if the config was retrieved
        It "Should verify the count is correct by comparing against a static number" {
            @($results).Count | Should -Be 36
        }
        

SQLvariant

SQLvariant

#######################
<#
.SYNOPSIS
Creates a DataTable for an object
.DESCRIPTION
Creates a DataTable based on an objects properties.
.INPUTS
Object
    Any object can be piped to Out-DataTable
.OUTPUTS

SQLvariant

$source_workspace_ID = (Get-PowerBIWorkspace -Name 'QA-Workspace').Id
$report = Get-PowerBIReport -Name 'Regional Sales' -WorkspaceId $source_workspace_ID
$target_workspace_ID = (Get-PowerBIWorkspace -Name 'Prod-Workspace').Id
$targetReport = Get-PowerBIReport -Name 'Regional Sales' -WorkspaceId $target_workspace_ID
Copy-PowerBIReport -Report $report -WorkspaceId $source_workspace_ID -TargetWorkspaceId $target_workspace_ID -TargetDatasetId ($targetReport).DatasetId -OutVariable NewReport

<# Now to remove the old version of the report in the Prod workspace #>
#$url = 'https://api.powerbi.com/v1.0/myorg/groups/' + $target_workspace_ID + '/reports/' + $targetReport.Id

#Invoke-PowerBIRestMethod -Method Delete -Url $url

SQLvariant

SQLvariant

<# SSRS / PBIRS #>
Register-ArgumentCompleter -ParameterName ReportServerUri -ScriptBlock {
    "http://localhost:8016/Reports_SQL2016", 'http://localhost:8081/PBIRServer' | ForEach-Object {
    $CompletionText = $_
    New-Object System.Management.Automation.CompletionResult (
              $CompletionText,
              $_,
              'ParameterValue',
              "$_ (SSRSInstance)"
                )