–A–

Access database (.accdb), open/read/write - see MS Access database (.accdb), open/read/write

–B–

bulk copy – aka: SqlBulkCopy – see also insert statement, convert from an object into, insert values into SQL Server database

$usersToDelete = @("bob", "sam", "charlie")
$table = "usersADDeleted"

$deleted = @()
foreach ($user in $usersToDelete) {
    $userToDelete = Get-ADUser $user -Properties samAccountName, displayName, SID, ObjectGUID, distinguishedName, whenCreated
    $userToDelete | Remove-ADUser -Confirm:$fals # remove user from AD
    # if user properties are null, set to empty string or the command to populate $columns below will fail which means the bulk copy will fail
    $userToDelete.displayName = $userToDelete.displayName ?? ""
    $deleted += New-Object -TypeName PSObject -Property @{
        samAccountName = $userToDelete.samAccountName
        displayName = $userToDelete.displayName
        SID = $userToDelete.SID.ToString()
        ObjectGUID = [System.Guid]$userToDelete.ObjectGUID # make sure datatype is correct for SQL
        distinguishedName = $userToDelete.distinguishedName
        whenCreated = $userToDelete.whenCreated
        deleted = [DateTime]::Now
    }
}

# SqlBulkCopy copy below expects columns in $deleted to be in the same order as encountered on SQL table; order is arbitrary from New-Object above
$deleted = $deleted | select samAccountName, displayName, SID, ObjectGUID, distinguishedName, whenCreated, deleted
 
# convert the $deleted array to a DataTable which is required for SqlBulkCopy
$dataTable = New-Object Data.DataTable
$columns = $deleted[0].PSObject.Properties | % {New-Object Data.DataColumn $_.Name, $_.Value.GetType()}
$dataTable.Columns.AddRange($columns)
$deleted | % {
    $row = $dataTable.NewRow()
    $_.PSObject.Properties | % {$row[$_.Name ] = $_.Value}
    $dataTable.Rows.Add($row)
}

# Define the server and database
$dbServerName = "SQLserver"
$database = "SysAdmin"
$ConnectionString = "Server=$dbServerName;Database=$database;Integrated Security=True;MultipleActiveResultSets=True;TrustServerCertificate=True;"
$connection = New-Object Data.SqlClient.SqlConnection($connectionString)
$connection.Open()

# set up SqlBulkCopy
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($connection)
$bulkCopy.DestinationTableName = $table
try {
    $bulkCopy.WriteToServer($dataTable)
}
catch {
    Write-Host $_.Exception.Message -ForegroundColor Red
} finally {
    $connection.Close()
}

Above works OK if the table has no key or other field that is auto-incremented. If the table has such a key, then must do things a little differently:

# Create the DataTable. Extra parameter to fetch the data type - which we need to accommodate auto-increment field. This only works with PS 7, not 5.5
$dataTable = New-ObjectSystem.Data.DataTable
$columns = $deleted[0].PSObject.Properties | % {
    $column = New-Object Data.DataColumn $_.Name, ([System.Type]::GetType($_.Value.GetType().FullName))
    $dataTable.Columns.Add($column)
}

# Add the rows to the DataTable
$dataTable.Columns.AddRange($columns)
$deleted | % {
    $row = $dataTable.NewRow()
    $_.PSObject.Properties | ? {$_.Name -ne "logUsersADID"} | % {$row[$_.Name] = $_.Value}
    $dataTable.Rows.Add($row)
}
 
# Print DataTable column names to verify column order, spelling and case in DataTable matches SQL table when troubleshooting
# $dataTable.Columns | ForEach-Object { Write-Output $_.ColumnName }

# Create the destination connection
$connection = New-ObjectSystem.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()
 
# Create the SqlBulkCopy object
$bulkCopy = New-ObjectSystem.Data.SqlClient.SqlBulkCopy($connection)

# Set the destination table name
$bulkCopy.DestinationTableName = $table

# Set up the column mappings dynamically, suppress output to console with "$null =""
foreach ($column in $dataTable.Columns) {$null = $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName)}

# Perform the bulk copy using the DataTable
$bulkCopy.WriteToServer($dataTable)

In the case above, extra steps include

• get the type when creating columns
• explicitly exclude logUsersADID field when building the rows
• Set up the column mappings dynamically for bulkcopy

–C–

connect to SQL Server database twice

Useful if we want a progress bar. You can’t just read in the records and get a count of how many records you have like you can when reading in from a CSV file. Instead, you must first execute a command to get the count, kill the connection, and then re-establish connection with the actual select statement.

# conntect to DB
$ConnectionString = "Server=Skynet;Database=SysAdmin;Integrated Security=True;MultipleActiveResultSets=True"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()
 
##################################################################
# open database reader 1st time to get records count
##################################################################
# break SQL into two parts so I can use the 2nd "from where" part again later below
# so I don't have to remember to copy & paste the "from where" in both places
$SQLSelectCount = "select count(*)"
$SQLFromWhere = "from servers where (Role like '%sql%' or ServerName like '%sql%')"
$SQLReadcount = "$SQLSelectCount $SQLFromWhere"
$reader = New-Object System.Data.SqlClient.sqlCommand
$reader.connection = $SqlConnection
$reader.CommandText = $SQLReadcount
$dr = $reader.ExecuteReader()
$dr.Read()
 
$countCharacter = $dr.GetValue(0)
[int]$count = [convert]::ToInt32($countCharacter, 10)
Write-Host "$count records to go through" -ForegroundColor DarkCyan
 
$dr.close | Out-Null
$reader.close | Out-Null
 
##################################################################
# open database reader again to read actual records
##################################################################
$reader = New-Object System.Data.SqlClient.sqlCommand
$reader.connection = $SqlConnection
$SQLSelectRows = "select ServerName, ServerID, SQLEdition, SQLVersion"
$SQLFromWhere = "from servers where (Role like '%sql%' or ServerName like '%sql%')"
$SQLReadRecords = "$SQLSelectRows $SQLFromWhere"
$reader.CommandText = $SQLReadRecords
$dr = $reader.ExecuteReader()
 
##################################################################
# open 2nd database connection to populate records
##################################################################
$cmd = New-Object System.Data.SqlClient.sqlCommand
$cmd.connection = $SqlConnection
 

$records = @() # Initialize an empty array to store custom objects
$i=0
while ($dr.Read() -and $i -le 5)
{
   $i++; $percentTxt = ($i/$count).ToString("P")
    $record = New-Object PSObject # Create a custom object with properties dynamically
    # Iterate through the fields and add them to the object
    for ($j = 0; $j -lt $dr.FieldCount; $j++) {
        $fieldName = $dr.GetName($j)
        $fieldValue = $dr[$j]
        $record | Add-Member -MemberType NoteProperty -Name $fieldName -Value $fieldValue
    }
    $records += $record # Add the custom object to the array
    # display the first two fields
    $fieldName1 = $record.PSObject.Properties | select -First 1 | % {$_.Name}; $fieldValue1 = $record.$fieldName1
    $fieldName2 = $record.PSObject.Properties | select -Skip 1 -First 1 | % {$_.Name}; $fieldValue2 = $record.$fieldName2
    Write-Host "$i of $($count) ($percentTxt): $($fieldName1) = $($fieldValue1), $($fieldName2) = $($fieldValue2)" -ForegroundColor Green
}

$records | ogv
$dr.close | Out-Null
$reader.close | Out-Null
$SqlConnection.Dispose()

I suppose we could just create two separate readers and not mess with all this. But I think this is cleaner and more clear that we’re getting the count ahead of time and we’ll never need that data reader again in the program once we capture that little bit of info into a variable ahead of time. We shouldn’t need a whole new reader just to get the count of records satisfying the same “from” and “where”; it’s really essentially the same query. Also, I often end up changing the “where” clause and this way I only have to remember to change it once.

connection string

Windows Integrated

# conntect to DB
$ConnectionString = "Server=Skynet;Database=SysAdmin;Integrated Security=True;MultipleActiveResultSets=True"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()

SQL user

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$ConnectionString = "Server=someserver;Database=somedb;User Id=someuser;Password=yourpwd"
$SqlConnection.ConnectionString = $ConnectionString

connectivity to remote server – see test network connectivity to PCs/server

–D–

database, rename using PowerShell includes renaming the underlying files – see also rename database from within SQL

this runs in PowerShell so it can rename the files and invokes SQL before and after to change the logical file names, take the database offline, rename the files, and then restore the database to being online

<#
renameDatabase.ps1

This dan be run remotely from a VDI or other machine that has access to the SQL Server instance and the UNC admin shares for the database files.
It does not require local access to the SQL Server machine.

Purpose:
1) Start in PowerShell.
2) Run SQL steps to validate state, rename database + logical file names, and set DB OFFLINE.
3) Rename physical files over UNC admin shares (remote-safe from VDI) while DB is OFFLINE.
4) Run SQL again to bring DB ONLINE, set MULTI_USER, and verify final state.
5) Exit safely when already renamed, and stop on ambiguous old/new DB state.
#>

$ErrorActionPreference = 'Stop'

############################
# Variables to set
############################
$SqlInstance = 'CoreTest'
$SqlHostForUnc = 'CoreTest'
$OldDatabaseName = 'Test1'
$NewDatabaseName = 'Test2'

if ($OldDatabaseName -eq $NewDatabaseName) {
    throw 'OldDatabaseName and NewDatabaseName are the same. Set different values before running.'
}

# UNC admin-share folders for physical files (used by PowerShell file operations from VDI)
$TargetDataFolderUnc = '\\hc-vSQLCoreTest\d$\MSSQL15.MSSQLSERVER\MSSQL\Data'
$TargetLogFolderUnc = '\\hc-vSQLCoreTest\e$\MSSQL15.MSSQLSERVER\MSSQL\Data'

############################
# Helpers
############################
function Escape-SqlLiteral {
    param([Parameter(Mandatory = $true)][string]$Value)
    return $Value.Replace("'", "''")
}

function Quote-SqlName {
    param([Parameter(Mandatory = $true)][string]$Name)
    return '[' + $Name.Replace(']', ']]') + ']'
}

function Convert-LocalPathToAdminShare {
    param(
        [Parameter(Mandatory = $true)][string]$Server,
        [Parameter(Mandatory = $true)][string]$Path
    )

    if ($Path -match '^\\\\') {
        return $Path
    }

    if ($Path -notmatch '^[A-Za-z]:\\') {
        throw "Path is not a local drive path: $Path"
    }

    $drive = $Path.Substring(0, 1).ToLowerInvariant()
    $rest = $Path.Substring(2).TrimStart('\\')
    return "\\$Server\$drive`$\$rest"
}

function Convert-AdminShareToLocalPath {
    param(
        [Parameter(Mandatory = $true)][string]$Server,
        [Parameter(Mandatory = $true)][string]$UncPath
    )

    if ($UncPath -match '^[A-Za-z]:\\') {
        return $UncPath
    }

    $prefix = "\\$Server\"
    if (-not $UncPath.StartsWith($prefix, [System.StringComparison]::OrdinalIgnoreCase)) {
        throw "UNC path is not an admin share on ${Server}: $UncPath"
    }

    $remainder = $UncPath.Substring($prefix.Length)
    $slashIndex = $remainder.IndexOf('\')
    $share = if ($slashIndex -ge 0) { $remainder.Substring(0, $slashIndex) } else { $remainder }
    $rest = if ($slashIndex -ge 0) { $remainder.Substring($slashIndex) } else { '\' }

    if ($share -notmatch '^[A-Za-z]\$$') {
        throw "UNC path share is not a drive admin share on ${Server}: $UncPath"
    }

    $drive = $share.Substring(0, 1).ToUpperInvariant()
    return "${drive}:$rest"
}

function Invoke-SqlQuery {
    param(
        [Parameter(Mandatory = $true)][string]$ServerInstance,
        [Parameter(Mandatory = $true)][string]$Database,
        [Parameter(Mandatory = $true)][string]$Query
    )

    $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
    $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $Query
    $command.CommandTimeout = 0

    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $table = New-Object System.Data.DataTable

    try {
        $connection.Open()
        [void]$adapter.Fill($table)
        return $table
    }
    finally {
        $connection.Dispose()
    }
}

function Invoke-SqlNonQuery {
    param(
        [Parameter(Mandatory = $true)][string]$ServerInstance,
        [Parameter(Mandatory = $true)][string]$Database,
        [Parameter(Mandatory = $true)][string]$Query
    )

    $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
    $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $Query
    $command.CommandTimeout = 0

    try {
        $connection.Open()
        [void]$command.ExecuteNonQuery()
    }
    finally {
        $connection.Dispose()
    }
}

############################
# Pre-checks
############################
$oldDbEsc = Escape-SqlLiteral -Value $OldDatabaseName
$newDbEsc = Escape-SqlLiteral -Value $NewDatabaseName
$oldDbQ = Quote-SqlName -Name $OldDatabaseName
$newDbQ = Quote-SqlName -Name $NewDatabaseName

$discoverQuery = @"
SELECT
    d.name AS DatabaseName,
    mf.type_desc,
    mf.name AS LogicalName,
    mf.physical_name
FROM sys.databases d
JOIN sys.master_files mf
    ON mf.database_id = d.database_id
WHERE d.name IN (N'$oldDbEsc', N'$newDbEsc')
ORDER BY mf.type_desc;
"@

$discoverRows = Invoke-SqlQuery -ServerInstance $SqlInstance -Database 'master' -Query $discoverQuery
$oldRows = @($discoverRows | Where-Object { $_.DatabaseName -eq $OldDatabaseName })
$newRows = @($discoverRows | Where-Object { $_.DatabaseName -eq $NewDatabaseName })

if (($oldRows.Count -gt 0) -and ($newRows.Count -gt 0)) {
    throw "Both '$OldDatabaseName' and '$NewDatabaseName' exist. Aborting to avoid ambiguous rename actions."
}

if ($oldRows.Count -lt 2) {
    if ($newRows.Count -ge 2) {
        Write-Warning "Old database '$OldDatabaseName' was not found, but '$NewDatabaseName' already exists. Assuming rename was previously completed. No changes made."
        $newRows |
            Select-Object DatabaseName, type_desc, LogicalName, physical_name |
            Format-Table -AutoSize
        return
    }

    throw "Could not find both data and log files for database '$OldDatabaseName'."
}

$fileRows = $oldRows

$dataRow = $fileRows | Where-Object { $_.type_desc -eq 'ROWS' } | Select-Object -First 1
$logRow = $fileRows | Where-Object { $_.type_desc -eq 'LOG' } | Select-Object -First 1

if ($null -eq $dataRow -or $null -eq $logRow) {
    throw "Missing ROWS or LOG file metadata for '$OldDatabaseName'."
}

$oldLogicalData = [string]$dataRow.LogicalName
$oldLogicalLog = [string]$logRow.LogicalName
$oldPhysicalData = [string]$dataRow.physical_name
$oldPhysicalLog = [string]$logRow.physical_name

$oldPhysicalDataUnc = Convert-LocalPathToAdminShare -Server $SqlHostForUnc -Path $oldPhysicalData
$oldPhysicalLogUnc = Convert-LocalPathToAdminShare -Server $SqlHostForUnc -Path $oldPhysicalLog

if (-not (Test-Path -LiteralPath $oldPhysicalDataUnc)) {
    throw "Data file does not exist on disk via UNC: $oldPhysicalDataUnc"
}
if (-not (Test-Path -LiteralPath $oldPhysicalLogUnc)) {
    throw "Log file does not exist on disk via UNC: $oldPhysicalLogUnc"
}

$dataFolderUnc = if ([string]::IsNullOrWhiteSpace($TargetDataFolderUnc)) { Split-Path -Parent $oldPhysicalDataUnc } else { $TargetDataFolderUnc }
$logFolderUnc = if ([string]::IsNullOrWhiteSpace($TargetLogFolderUnc)) { Split-Path -Parent $oldPhysicalLogUnc } else { $TargetLogFolderUnc }

$targetPhysicalDataUnc = Join-Path -Path $dataFolderUnc -ChildPath ($NewDatabaseName + '.mdf')
$targetPhysicalLogUnc = Join-Path -Path $logFolderUnc -ChildPath ($NewDatabaseName + '_log.ldf')

$targetPhysicalDataSql = Convert-AdminShareToLocalPath -Server $SqlHostForUnc -UncPath $targetPhysicalDataUnc
$targetPhysicalLogSql = Convert-AdminShareToLocalPath -Server $SqlHostForUnc -UncPath $targetPhysicalLogUnc

$oldLogicalDataEsc = Escape-SqlLiteral -Value $oldLogicalData
$oldLogicalLogEsc = Escape-SqlLiteral -Value $oldLogicalLog
$targetPhysicalDataEsc = Escape-SqlLiteral -Value $targetPhysicalDataSql
$targetPhysicalLogEsc = Escape-SqlLiteral -Value $targetPhysicalLogSql
$newLogicalDataEsc = Escape-SqlLiteral -Value $NewDatabaseName
$newLogicalLogEsc = Escape-SqlLiteral -Value ($NewDatabaseName + '_log')

Write-Host "Server           : $SqlInstance"
Write-Host "Old DB           : $OldDatabaseName"
Write-Host "New DB           : $NewDatabaseName"
Write-Host "Old Data Logical : $oldLogicalData"
Write-Host "Old Log Logical  : $oldLogicalLog"
Write-Host "New Data Logical : $NewDatabaseName"
Write-Host "New Log Logical  : $($NewDatabaseName + '_log')"
Write-Host "Old Data File    : $oldPhysicalData (SQL)"
Write-Host "Old Data File    : $oldPhysicalDataUnc (UNC)"
Write-Host "Old Log File     : $oldPhysicalLog (SQL)"
Write-Host "Old Log File     : $oldPhysicalLogUnc (UNC)"
Write-Host "New Data File    : $targetPhysicalDataSql (SQL)"
Write-Host "New Data File    : $targetPhysicalDataUnc (UNC)"
Write-Host "New Log File     : $targetPhysicalLogSql (SQL)"
Write-Host "New Log File     : $targetPhysicalLogUnc (UNC)"

if (($oldPhysicalDataUnc -ne $targetPhysicalDataUnc) -and (Test-Path -LiteralPath $targetPhysicalDataUnc)) {
    throw "Target data file already exists: $targetPhysicalDataUnc"
}
if (($oldPhysicalLogUnc -ne $targetPhysicalLogUnc) -and (Test-Path -LiteralPath $targetPhysicalLogUnc)) {
    throw "Target log file already exists: $targetPhysicalLogUnc"
}

############################
# SQL phase 1: metadata updates and transition to OFFLINE
############################
$sqlPhase1 = @"
ALTER DATABASE $oldDbQ SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE $oldDbQ MODIFY NAME = $newDbQ;

ALTER DATABASE $newDbQ MODIFY FILE (NAME = N'$oldLogicalDataEsc', NEWNAME = N'$newLogicalDataEsc');
ALTER DATABASE $newDbQ MODIFY FILE (NAME = N'$oldLogicalLogEsc', NEWNAME = N'$newLogicalLogEsc');

ALTER DATABASE $newDbQ MODIFY FILE (NAME = N'$newLogicalDataEsc', FILENAME = N'$targetPhysicalDataEsc');
ALTER DATABASE $newDbQ MODIFY FILE (NAME = N'$newLogicalLogEsc', FILENAME = N'$targetPhysicalLogEsc');

ALTER DATABASE $newDbQ SET OFFLINE WITH ROLLBACK IMMEDIATE;
"@

Invoke-SqlNonQuery -ServerInstance $SqlInstance -Database 'master' -Query $sqlPhase1

############################
# PowerShell phase: physical file rename/move while DB remains OFFLINE
############################
if ($oldPhysicalDataUnc -ne $targetPhysicalDataUnc) {
    Move-Item -LiteralPath $oldPhysicalDataUnc -Destination $targetPhysicalDataUnc -Force
}
if ($oldPhysicalLogUnc -ne $targetPhysicalLogUnc) {
    Move-Item -LiteralPath $oldPhysicalLogUnc -Destination $targetPhysicalLogUnc -Force
}

############################
# SQL phase 2: bring DB ONLINE, restore MULTI_USER, then verify
############################
$sqlPhase2 = @"
ALTER DATABASE $newDbQ SET ONLINE;
ALTER DATABASE $newDbQ SET MULTI_USER;

SELECT
    d.name AS DatabaseName,
    mf.type_desc,
    mf.name AS LogicalName,
    mf.physical_name,
    d.state_desc,
    d.user_access_desc
FROM sys.databases d
JOIN sys.master_files mf
    ON mf.database_id = d.database_id
WHERE d.name = N'$newDbEsc'
ORDER BY mf.type_desc;
"@

$verify = Invoke-SqlQuery -ServerInstance $SqlInstance -Database 'master' -Query $sqlPhase2
$verify | Format-Table -AutoSize

Write-Host "Rename completed successfully." -ForegroundColor Green

data table, convert to object

Read results of a stored procedure into a data table. Convert this data table into a custom object while also appending another DNS field at the beginning

#Define the stored procedure name
$StoredProcedure = "EventID2889groupByIPUserBindTypeComptrName"

# Load the .NET SQL Client assembly
Add-Type -AssemblyName "System.Data"

# Create and open a new SQL connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()

# Create a SQL command to execute the stored procedure
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
$SqlCommand.Connection = $SqlConnection
$SqlCommand.CommandText = $StoredProcedure
$SqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure

# Execute the command and load the results into a DataTable
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCommand
$DataTable = New-Object System.Data.DataTable
$SqlAdapter.Fill ($DataTable)

# Close the SQL connection
$SqlConnection.Close()

#Convert DataTable to a collection of custom objects but append new property DNS to beginning of each object
$eventsID2889 = $DataTable | % {
    $obj = [PSCustomObject]@{}
    $obj | Add-Member -MemberType NoteProperty -Name "DNS" -Value $null # add a new property DNS to the beginning of each object
    foreach ($column in $DataTable.Columns) {
        $obj | Add-Member -MemberType NoteProperty -Name $column.ColumnName -Value $_[$column.ColumnName]
    }
    $obj
}

–E–

–F–

–G–

–H–

how many records before reading records returned, know - see connect to SQL Server database twice

–I–

insert statement, convert from an object into – see also bulk copy

$tableName = "DeleteLog"
$fieldNames = @($toDelete.PSObject.Properties.Name) # Get all property names
$fields = $fieldNames -join ", "
 
$valuesArray = @()
$fieldNames | % {$valuesArray += "'$($toDelete.$_)'"} # Enclose values in single quotes
$values = $valuesArray -join ","
 
$insertQuery = "INSERT INTO $tableName ($fields) VALUES ($values)"

insert values into SQL Server database – see also bulk copy

Start by opening a database connection

$server = "yourServer\mssql2019"
$database = "yourDatabase"
$table = "yourTable"
$ConnectionString = "Server=$server;Database=$database;Integrated Security=True;MultipleActiveResultSets=True"
try
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $ConnectionString
    $cmd = New-Object System.Data.SqlClient.sqlCommand
    $cmd.connection = $SqlConnection
    $cmd.Connection.Open()
}
catch [System.Exception]
{
    Write-Host $_.Exception.Message -ForegroundColor Yellow
}

Normally the following might now be done inside a loop (not shown here)
If we don’t clear the parameters now using .Parameters.Clear() immediately below from when we set them during an earlier iteration of the loop further below, the system will complain because it thinks we’re trying to add the same field again from that earlier iteration (after the 1st iteration, anyway.) Trying to clear using $null won’t work

$cmd.Parameters.Clear()

Now start to compose the insert command. We’re inserting 4 fields.

$cmd.CommandText = "insert into $table (fileName, location,system,URL) values (@fileName,@location,@system,@URL)"

The "old" way we might have done this was just to put the values we wanted inserted directly into "values" part of the statement above. But using the "@" above and then filling in those parameters below helps us avoid problems with embedded single quotes and are more secure with regard to SQL injection attacks
The | Out-Null statements at the end of each .Parameters.Add() commands below prevents a whole bunch of annoying verbiage from going to your console

$cmd.Parameters.Add("@fileName",$attachment) | Out-Null
$cmd.Parameters.Add("@location",$location) | Out-Null
$cmd.Parameters.Add("@system",$system) | Out-Null
$cmd.Parameters.Add("@URL",$link) | Out-Null

Now that we’ve properly cued up our insert statement, try to run it:

try
{
    $cmd.ExecuteNonQuery() | Out-Null
}
catch [System.Data.SqlClient.SqlException]
{
    Write-Host "Problem: $($_.Exception.Message)" -ForegroundColor Red
}
catch [System.Exception]
{
    # A generic error occurred somewhere in the try area.
    Write-Host "Some other error occurred: $($_.Exception.Message)" -ForegroundColor Red
}

Once we’re done with our loop, close the database connection

$cmd.Connection.Close()

Install-Module SqlServer

Install-Module sqlserver

instance

This requires you to know the instance name to begin with. Usually, it’s just the computer name. It returns some basic info like version.

Get-SqlInstance -ServerInstance "MagicKindom"

Invoke-Sqlcmd not recognized - see Install-Module sqlserver

–J–

.jpg stored in varbinary(max) fields aren’t properly retrieved into a jpg file – see varbinary(max) field

–K–

–L–

log into SQL Server database, can't using non-integrated security - make sure your connection string includes, ";Trusted_Connection = yes"

–M–

MS Access database (.accdb), open/read/write

$mytemp = [environment]::getfolderpath("mydocuments")
$filename = "$mytemp\companies.accdb"
$conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.16.0;Data Source=$filename;Persist Security Info=False")
$conn.Open()
# read
$cmd=$conn.CreateCommand()
$cmd.CommandText="Select company.* from company;"
"connection is: $($conn.State)"
$rdr = $cmd.ExecuteReader()
$dt = New-Object System.Data.Datatable
$dt.Load($rdr)
$dt
# write
$cmd2 = $conn.CreateCommand()
$cmd2.CommandText ="INSERT into Company (CompanyName) values('Jumbotron')"
$cmd2.ExecuteNonQuery()
$conn.Close()
"connection is: $($conn.State)"

–N–

null, check if a field in a record which contains a string is null

infuriatingly, this does not work:

if ($null -eq $record.employeeType -or $record.employeeType -eq "") {

wierdly, this does work:

if ("'$($record.employeeType)'" -eq "''") {

So, better stick with this:

if ([string]::IsNullOrEmpty($record.employeeType)) {

–O–

object, convert into an insert statement – see insert statement, convert from an object into

ODBC entry, create

Add-OdbcDsn -Name "'SysAdmin'" -DriverName "SQL Server" -DsnType "System" -SetPropertyValue @("Server=DEV1\MSSQL2012", "Trusted_Connection=Yes", "Database=SysAdmin")

or first make sure the driver has been installed

If ($OdbcDriver.Name -eq "ODBC Driver 17 for SQL Server") # Only continue if this specific SQL ODBC driver is installed
{Add-OdbcDsn -Name "NetSuiteODBC17d" -DriverNameriver 17 for SQL Server" -Platform 64-bit -DsnType System -SetPropertyValue @("Server=flamboozl.database.windows.net", "Authentication=ActiveDirectoryPassword", "Database=flambuuzl", "Description=flambuuzl using ODBC version 17", "Encrypt=Yes", "TrustServerCertificate=No")}

–P–

password for SQL Server database doesn’t work using non-integrated security - make sure your connection string includes, ";Trusted_Connection = yes"

photos stored in varbinary(max) fields aren’t properly retrieved into a jpg file – see varbinary(max) field

port 1433 open to server?

Test-NetConnection -ComputerName vSQL5 -Port 1433

progress bar - see connect to SQL Server database twice

–Q–

–R–

read from Azure DB

$database = "SourceOfAll"
$ServerInstance = "sourceOfAll.database.windows.net"
$Username = "readerGuy"
$password = "yuulNeverGess!"
 
$query = "select FirstName, LastName, DisplayName, UserPrincipalName
from users
where domain = jupiter.com"
$results = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $database -Username $Username -Password $Password -Query $query
 
foreach ($result in $results) {
    "$($result.DisplayName)"
}

almost the same for insert, update; just different SQL

record count before reading records returned - see connect to SQL Server database twice

rename database from within SQL - from here – see also database, rename using PowerShell

EXEC master..sp_renamedb 'CoreDB','ProductsDB'

Once the above T-SQL has executed successfully the database name will change however the Logical Name and File Name will not change. You can verify this by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')
GO

follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and File Name of the database. This first set of commands put the database in single user mode and also modifies the logical names.

/* Set Database as a Single User */
ALTER DATABASE CoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB', NEWNAME=N'ProductsDB')
GO
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB_log', NEWNAME=N'ProductsDB_log')

Now we need to detach the database, so we can rename the physical files. If the database files are open you will not be able to rename the files.

/* Detach Current Database */
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'CoreDB'
GO

Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.

/* Rename Physical Files */
USE [master]
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf", "ProductsDB_log.ldf"'
GO

Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:

/* Attach Renamed ProductsDB Database Online */
USE [master]
GO
CREATE DATABASE ProductsDB ON
(FILENAME= N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf' )
FOR ATTACH
GO

–S–

SQL command, execute from PowerShell

Invoke-Sqlcmd -ServerInstance "One-Ring" -Database "SysAdmin" - HostName"One-Ring" -ApplicationName "ReadWrite" -ApplicationIntent ReadWrite -Query "select HOST_NAME() AS HostName, APP_NAME() AS ApplicationIntent, @@SERVERNAME AS ServerName"

SQL command, execute from command line

sqlcmd -S One-Ring -U sauron

The command line will ask you the password. You can optionally specify the password (not recommended, but sometimes is the only way to work):

sqlcmd -S One-Ring -U sauron -P Mypwd$%KillElves

This is great if you already have database credentials. But what if you want to run using Windows Authentication? supposedly, you can invoke a remote session and run the SQL command all in one command:

runas /user: mordor\sauron sqlcmd.exe -S One-Ring

But Ive never got it to run all as one command. Instead, I first run as:

runas.exe /user:mordor\sauron cmd.exe

Now at local command prompt, connect to remote server (“One-Ring” in this case):

C:\WINDOWS\system32>sqlcmd -S One-Ring

Which opens up a separate DOS-like command prompt where I can begin issuing commands:

1> PRINT SUSER_NAME();
2> GO

returns:

mordor\sauron

I can change database from default master:

1> use SysAdmin

2> go

returns:

Changed database context to 'SysAdmin'.

now I can finally run something useful:

1> select count(*) as howMany from servers where (Role like '%orc%' or ServerName like '%orc%')

2> go

returns:

howMany
-----------

         55

when done, exit:

1> exit

SQL Server database, can't connect to using non-integrated security - make sure your connection string includes, ";Trusted_Connection = yes"

SQL, execute - see also here and here for arrays

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$ConnectionString = "Server=someserver;Database=somedb;User Id=someuser;Password=yourpwd"
$SqlConnection.ConnectionString = $ConnectionString
try
{
    $sqlConnection.Open()
}
catch [System.Data.SqlClient.SqlException]
{
    Write-Host "Problem connecting to database"
    write-host $_.Exception.ToString()
    #write-host $_.Exception.state
    exit
}
$sqlCommand = $sqlConnection.CreateCommand()
$SQLStr = "select * from table"
$sqlCommand.CommandText = $SQLStr
$dr = $sqlCommand.ExecuteReader()
$SqlConnection.Close()
$DataSet.Tables[0]

if you then want to loop through the recordset returned:

while ($dr.Read())
{
  $firstFieldValue = $dr.GetValue(0)
  $secondFieldValue = $dr.GetValue(1)
}

This is all well and good for select statements.  But for update and inserts, need something else:

$updateSQL = "update table set somefield = 3"
$sqlCommand.CommandText = $updateSQL
$sqlCommand.ExecuteNonQuery()

Startup Programs, Generate a List

Get-CimInstance Win32_StartupCommand | Select-Object Name, command, Location, User | ogv

–T–

table, create

Set up connection, reference server and database

Import-Module SQLServer # Import the SQL Server module
# May need to import the SQL Server Management Objects (SMO) assembly
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=17.100.13.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$serverName = "DispicableMe" # Define the server and database
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName) # Create a connection to the server
$databaseName = "gru"
$tableName = "minions"
$db = $server.Databases[$databaseName] # Reference the database

Simple table with just one column

if ($null -ne $db.Tables[$tableName]) {$db.Tables["MyTable"].Rename("MyTableOld")} # If table already exists, rename it
$table = New-Object Microsoft.SqlServer.Management.Smo.Table($db, "MyTable") # Define the table
$column = New-Object Microsoft.SqlServer.Management.Smo.Column($table, "MyColumn", [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(50)) # Define a column
$table.Columns.Add($column) # Add the column to the table
$table.Create() # Create the table

More columns

# Initialize first array with alternating elements, each destined for its own array.
# With each user property’s name adjacent to its data type,
# this makes it easy to add, delete, change field order.
$userPropertiesWithDataTypes = @(
    "uSNCreated", [Microsoft.SqlServer.Management.Smo.DataType]::Int,
    "name", [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(50),
    "ObjectGUID", [Microsoft.SqlServer.Management.Smo.DataType]::UniqueIdentifier,
    "Created", [Microsoft.SqlServer.Management.Smo.DataType]::DateTime,
    "Info", [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(4000)
)

# Initialize empty arrays the two halves of the first array above
$userProperties = @()
$propertyDataTypes = @()
 
# Split the first array into two arrays: one for user property names and the other for each propertys data type
foreach ($userPropertyWithDataType in $userPropertiesWithDataTypes) {
    if ($userPropertyWithDataType -is [string]) {
        $userProperties += $userPropertyWithDataType # Add to the user properties array
    } else {
        $propertyDataTypes += $userPropertyWithDataType # Add to the data types array
    }
}
# bonus 3rd array which pre-pends an “@” to the first array which we'll use later for parameters
$propertyNames = $userProperties | % { "@$_" }

create table with many columns as specified above

# Create the table and add columns
$table = New-Object Microsoft.SqlServer.Management.Smo.Table($db, $tableName)
for ($j = 0; $j -lt $userProperties.Length; $j++) {
    $column = New-Object Microsoft.SqlServer.Management.Smo.Column($table, $userProperties[$j], $propertyDataTypes[$j])
    $table.Columns.Add($column)
}
 
#Save the changes
$table.Create()

populate table

$ConnectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True;MultipleActiveResultSets=True"
try {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $ConnectionString
    $cmd = New-Object System.Data.SqlClient.sqlCommand
    $cmd.connection = $SqlConnection
    $cmd.Connection.Open()
}
catch [System.Exception]
{
    Write-Host $_.Exception.Message -ForegroundColor Yellow
}

$insertErrors=@()

#Insert data into SQL Server table
$i=0; $count = $usersHC.Count
foreach ($user in $usersHC) {
    $i++; $percentTxt = ($i/$count).ToString("P")
    Write-Host "$i of $($count) ($percentTxt): $($user.SamAccountName) - $($user.DisplayName)" -ForegroundColor Green
    $cmd.Parameters.Clear()
    $cmd.CommandText = "INSERT INTO $tableName ($($userProperties -join ",")) VALUES ($($propertyNames -join ","))"
    #Write-Host "$i $($cmd.CommandText )" -ForegroundColor Green
    # Add parameters dynamically
    for ($j = 0; $j -lt $propertyNames.Length; $j++) {
        $thisUserProperty = $userProperties[$j]
        $parameter = $cmd.Parameters.AddWithValue($propertyNames[$j], $thisUserProperty)
        $propertyDataType = $propertyDataTypes[$j]
        # Handle special data types
        switch ($propertyDataType) {
            "uniqueidentifier" {$parameter.Value = $user.$thisUserProperty}
            "nvarchar" {
                if ($thisUserProperty -eq "SID") {
                    $parameter.Value = $user.SID.ToString() # Set the SID
                } elseif ($thisUserProperty -eq "OU") {
                    $OU = GetOUfromDistinguishedName ($user.DistinguishedName)
                    $parameter.Value = $OU
                }
                else {
                    if ($null -eq $user.$thisUserProperty) {
                        $parameter.Value = [DBNull]::Value
                    } else {
                        $parameter.Value = $user.$thisUserProperty
                    }
                }
            }
            "datetime" {
                if ($null -eq $user.$thisUserProperty) {
                    $parameter.Value = [DBNull]::Value
                } else {
                    $parameter.Value = $user.$thisUserProperty
                }
            }
            "int" {
                if ($null -eq $user.$thisUserProperty) {
                    $parameter.Value = [System.DBNull]::Value
                } else{
                    $parameter.Value = $user.$thisUserProperty
                }
            }
            default {
                if ($null -eq $user.$thisUserProperty) {
                    $parameter.Value = [DBNull]::Value
                } else {
                    $parameter.Value = $user.$thisUserProperty
                }
            }
        }
    }
    try {
        $cmd.ExecuteNonQuery() | Out-Null
    }
    catch [System.Data.SqlClient.SqlException]{
        Write-Host "Problem: $($_.Exception.Message)" -ForegroundColor Red
        $insertErrors += New-Object -TypeName PSObject -Property @{
            SamAccountName = $user.SamAccountName
            DisplayName= $user.DisplayName
            Error = $_.Exception.Message
        }
    }
    catch [System.Exception]{Write-Host "Some other error occurred: $($_.Exception.Message)" -ForegroundColor Red} # A generic error occurred somewhere in the try area.
}
 
$cmd.Connection.Close()

test network connectivity to PCs/server

Test-NetConnection -ComputerName "MutantNinjaTurtles" -Port 1433

will try to communicate with server over port 1433 (SQL Server)

total record count before reading records returned – see connect to SQL Server database twice

–U–

–V–

varbinary(max) field

Invoke-Sqlcmd doesn’t work very good with varbinary(max) fields with lots (e.g., megabytes) of data in each record and field

# this exports the photo but only a shrunken, worthless version of 1 KB.  Invoke-Sqlcmd is not reliable for large binary data.
Invoke-Sqlcmd-Query "SELECT photo FROM PhotoWarehouse.dbo.Person WHERE ID = 5" -ServerInstance "person"-TrustServerCertificate |
ForEach-Object {[System.IO.File]::WriteAllBytes("C:\Temp\photo.jpg",$_.uihcCustomPhoto)}
 
# this version using .NET directly works to get the full image.  Use ADO.NET for full fidelity.
$cn=New-ObjectData.SqlClient.SqlConnection "Data Source=person.beautysalon.com;Initial Catalog=PhotoWarehouse;Integrated Security=True"
$cmd=$cn.CreateCommand()
$cmd.CommandText="SELECT photo FROM PhotoWarehouse.dbo.Person WHERE ObjectID = 5"
$cn.Open()
$val=$cmd.ExecuteScalar()
$cn.Close()
[System.IO.File]::WriteAllBytes("C:\\Temp\\photo.jpg",$val)

–W–

–X–

–Y–

–Z–