Tinus EngOps Wiki

Logo

OpsaC - Operating as PowerShell code

Links

Home

PowerShell Blog

PowerShell Index

PowerShell Search

Additional Websites

View my GitHub Profile

View my GitHub Gists

View Tinus IT Wiki

View my Photo Website

Invoke-DbaQuery with Tables

published: March 13, 2021 author: Tinu tags: PowerShell categories: PowerShell-SQL

Table of Contents

Create a table with dbatools

Create a new table with the rows LastRunTime, ComputerName, WUServer, LastPatchRun, LastPatchStatus, CumulativeUpdate, CumulativeUpdate and RestartHistory.

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [String] $SqlInstance = 'MySQLInstance',

    [Parameter(Mandatory=$true)]
    [String] $Database = 'MyDatabase',

    [Parameter(Mandatory=$true)]
    [String] $Table = 'MyTable'
)

Begin{
$SqlQuery = @"
USE $($Database);
GO

CREATE TABLE $($Table) (
  ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  LastRunTime datetime,
  ComputerName nvarchar(255) NOT NULL,
  WUServer nvarchar(255),
  LastPatchRun nvarchar(255),
  LastPatchStatus nvarchar(255),
  HotFix nvarchar(255),
  CumulativeUpdate nvarchar(255),
  RestartHistory nvarchar(255),
);
GO
"@
}

Process{

    if(-not($MySqlCredentials)){
        $MySqlCredentials = Get-Credential -Message "Enter the user to connect to $SqlInstance" -UserName "$($env:USERDNSDOMAIN)\$($env:USERNAME)"
    }
    try{
        $PSBoundParameters | Out-String | foreach {Write-Verbose $_}
        $StartDate = Get-Date
        $result    = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $MySqlCredentials -Database $Database -Query $SqlQuery
    }
    catch{
        Write-Host "An error occured, details:" -ForegroundColor Red
        [PSCustomObject]@{
            Activity   = $($_.CategoryInfo).Activity
            Scriptname = $($_.InvocationInfo.ScriptName)
            LineNumber = $($_.InvocationInfo.ScriptLineNumber)
            Message    = $($_.Exception.Message)
            Category   = $($_.CategoryInfo).Category
            Exception  = $($_.Exception.GetType().FullName)
            TargetName = $($_.CategoryInfo).TargetName
        }        
        $Error.clear()
        $MySqlCredentials = $null
    }
    finally{
        $EndDate = Get-Date
        $TotalMS = "{0:N0}" -f (New-TimeSpan –Start $StartDate –End $EndDate | Select-Object -ExpandProperty MilliSeconds)
        Write-Host "[$($TotalMS)ms] Query return $($result.ID.count) rows" -ForegroundColor Green
    }
}

End{
    return $result
}

Delete a table with dbatools

Delete the table WindowsPatching.

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [String] $SqlInstance = 'MySQLInstance',

    [Parameter(Mandatory=$true)]
    [String] $Database = 'MyDatabase',

    [Parameter(Mandatory=$true)]
    [String] $Table = 'MyTable'
)

Begin{
$SqlQuery = @"
USE $($Database);
GO

DROP TABLE $($Table);
GO
"@
}

Process{

    if(-not($MySqlCredentials)){
        $MySqlCredentials = Get-Credential -Message "Enter the user to connect to $SqlInstance" -UserName "$($env:USERDNSDOMAIN)\$($env:USERNAME)"
    }
    try{
        $PSBoundParameters | Out-String | foreach {Write-Verbose $_}
        $StartDate = Get-Date
        $result    = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $MySqlCredentials -Database $Database -Query $SqlQuery
    }
    catch{
        Write-Host "An error occured, details:" -ForegroundColor Red
        [PSCustomObject]@{
            Activity   = $($_.CategoryInfo).Activity
            Scriptname = $($_.InvocationInfo.ScriptName)
            LineNumber = $($_.InvocationInfo.ScriptLineNumber)
            Message    = $($_.Exception.Message)
            Category   = $($_.CategoryInfo).Category
            Exception  = $($_.Exception.GetType().FullName)
            TargetName = $($_.CategoryInfo).TargetName
        }        
        $Error.clear()
        $MySqlCredentials = $null
    }
    finally{
        $EndDate = Get-Date
        $TotalMS = "{0:N0}" -f (New-TimeSpan –Start $StartDate –End $EndDate | Select-Object -ExpandProperty MilliSeconds)
        Write-Host "[$($TotalMS)ms] Query return $($result.ID.count) rows" -ForegroundColor Green
    }
}

End{
    return $result
}

Insert a new record with dbatools

Insert a new record with a ComputerName and the current TimeStamp..

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [String] $SqlInstance = 'MySQLInstance',

    [Parameter(Mandatory=$true)]
    [String] $Database = 'MyDatabase',

    [Parameter(Mandatory=$true)]
    [String] $ComputerName = 'MyComputer.domain.com',

    [Parameter(Mandatory=$true)]
    [String] $Table = 'MyTable'
)

Begin{
$SqlQuery = @"
USE $($Database);
GO

INSERT INTO $($Table) (ComputerName, LastRunTime)
VALUES ('$($ComputerName)',CURRENT_TIMESTAMP);
GO
"@
}

Process{

    if(-not($MySqlCredentials)){
        $MySqlCredentials = Get-Credential -Message "Enter the user to connect to $SqlInstance" -UserName "$($env:USERDNSDOMAIN)\$($env:USERNAME)"
    }
    try{
        $PSBoundParameters | Out-String | foreach {Write-Verbose $_}
        $StartDate = Get-Date
        $result    = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $MySqlCredentials -Database $Database -Query $SqlQuery
    }
    catch{
        Write-Host "An error occured, details:" -ForegroundColor Red
        [PSCustomObject]@{
            Activity   = $($_.CategoryInfo).Activity
            Scriptname = $($_.InvocationInfo.ScriptName)
            LineNumber = $($_.InvocationInfo.ScriptLineNumber)
            Message    = $($_.Exception.Message)
            Category   = $($_.CategoryInfo).Category
            Exception  = $($_.Exception.GetType().FullName)
            TargetName = $($_.CategoryInfo).TargetName
        }        
        $Error.clear()
        $MySqlCredentials = $null
    }
    finally{
        $EndDate = Get-Date
        $TotalMS = "{0:N0}" -f (New-TimeSpan –Start $StartDate –End $EndDate | Select-Object -ExpandProperty MilliSeconds)
        Write-Host "[$($TotalMS)ms] Query return $($result.ID.count) rows" -ForegroundColor Green
    }
}

End{
    return $result
}

Update a record with dbatools

Update a record with ID 1 and insert a value in the field LastPatchRun.

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [String] $SqlInstance = 'MySQLInstance',

    [Parameter(Mandatory=$true)]
    [String] $Database = 'MyDatabase',

    [Parameter(Mandatory=$true)]
    [String] $Table = 'MyTable',

    [Parameter(Mandatory=$true)]
    [Int] $ID = 1,

    [Parameter(Mandatory=$true)]
    [String] $LastPatchRun = '2021.02.10 05:42:50'
)

Begin{
$SqlQuery = @"
USE $($Database);
GO

UPDATE $($Table)
SET LastPatchRun = $($LastPatchRun)
WHERE ID = $($ID);
GO
"@
}

Process{

    if(-not($MySqlCredentials)){
        $MySqlCredentials = Get-Credential -Message "Enter the user to connect to $SqlInstance" -UserName "$($env:USERDNSDOMAIN)\$($env:USERNAME)"
    }
    try{
        $PSBoundParameters | Out-String | foreach {Write-Verbose $_}
        $StartDate = Get-Date
        $result    = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $MySqlCredentials -Database $Database -Query $SqlQuery
    }
    catch{
        Write-Host "An error occured, details:" -ForegroundColor Red
        [PSCustomObject]@{
            Activity   = $($_.CategoryInfo).Activity
            Scriptname = $($_.InvocationInfo.ScriptName)
            LineNumber = $($_.InvocationInfo.ScriptLineNumber)
            Message    = $($_.Exception.Message)
            Category   = $($_.CategoryInfo).Category
            Exception  = $($_.Exception.GetType().FullName)
            TargetName = $($_.CategoryInfo).TargetName
        }        
        $Error.clear()
        $MySqlCredentials = $null
    }
    finally{
        $EndDate = Get-Date
        $TotalMS = "{0:N0}" -f (New-TimeSpan –Start $StartDate –End $EndDate | Select-Object -ExpandProperty MilliSeconds)
        Write-Host "[$($TotalMS)ms] Query return $($result.ID.count) rows" -ForegroundColor Green
    }
}

End{
    return $result
}

Delete a record with dbatools

Delete the record with ID 1.

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [String] $SqlInstance = 'MySQLInstance',

    [Parameter(Mandatory=$true)]
    [String] $Database = 'MyDatabase',

    [Parameter(Mandatory=$true)]
    [String] $Table = 'MyTable',

    [Parameter(Mandatory=$true)]
    [Int] $ID = 1,
)

Begin{
$SqlQuery = @"
USE $($Database);
GO

DELETE
FROM $($Table)
WHERE ID = $($ID);
GO
"@
}

Process{

    if(-not($MySqlCredentials)){
        $MySqlCredentials = Get-Credential -Message "Enter the user to connect to $SqlInstance" -UserName "$($env:USERDNSDOMAIN)\$($env:USERNAME)"
    }
    try{
        $PSBoundParameters | Out-String | foreach {Write-Verbose $_}
        $StartDate = Get-Date
        $result    = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $MySqlCredentials -Database $Database -Query $SqlQuery
    }
    catch{
        Write-Host "An error occured, details:" -ForegroundColor Red
        [PSCustomObject]@{
            Activity   = $($_.CategoryInfo).Activity
            Scriptname = $($_.InvocationInfo.ScriptName)
            LineNumber = $($_.InvocationInfo.ScriptLineNumber)
            Message    = $($_.Exception.Message)
            Category   = $($_.CategoryInfo).Category
            Exception  = $($_.Exception.GetType().FullName)
            TargetName = $($_.CategoryInfo).TargetName
        }        
        $Error.clear()
        $MySqlCredentials = $null
    }
    finally{
        $EndDate = Get-Date
        $TotalMS = "{0:N0}" -f (New-TimeSpan –Start $StartDate –End $EndDate | Select-Object -ExpandProperty MilliSeconds)
        Write-Host "[$($TotalMS)ms] Query return $($result.ID.count) rows" -ForegroundColor Green
    }
}

End{
    return $result
}

Select TOP with dbatools

Select the TOP 10 records.

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [String] $SqlInstance = 'MySQLInstance',

    [Parameter(Mandatory=$true)]
    [String] $Database = 'MyDatabase',

    [Parameter(Mandatory=$true)]
    [String] $Table = 'MyTable'
)

Begin{
$SqlQuery = @"
USE $($Database);
GO

SELECT TOP (10) * 
FROM [$($Database)].[dbo].[$($Table)];
GO
"@
}

Process{

    if(-not($MySqlCredentials)){
        $MySqlCredentials = Get-Credential -Message "Enter the user to connect to $SqlInstance" -UserName "$($env:USERDNSDOMAIN)\$($env:USERNAME)"
    }
    try{
        $PSBoundParameters | Out-String | foreach {Write-Verbose $_}
        $StartDate = Get-Date
        $result    = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $MySqlCredentials -Database $Database -Query $SqlQuery
    }
    catch{
        Write-Host "An error occured, details:" -ForegroundColor Red
        [PSCustomObject]@{
            Activity   = $($_.CategoryInfo).Activity
            Scriptname = $($_.InvocationInfo.ScriptName)
            LineNumber = $($_.InvocationInfo.ScriptLineNumber)
            Message    = $($_.Exception.Message)
            Category   = $($_.CategoryInfo).Category
            Exception  = $($_.Exception.GetType().FullName)
            TargetName = $($_.CategoryInfo).TargetName
        }        
        $Error.clear()
        $MySqlCredentials = $null
    }
    finally{
        $EndDate = Get-Date
        $TotalMS = "{0:N0}" -f (New-TimeSpan –Start $StartDate –End $EndDate | Select-Object -ExpandProperty MilliSeconds)
        Write-Host "[$($TotalMS)ms] Query return $($result.ID.count) rows" -ForegroundColor Green
    }
}

End{
    return $result
}

See also

dbatools

← Previous Post ◇ [ Top ] ◇ Copyright © 2024 by tinuwalther ◇ [ Blog ] ◇ Next Post →