gitfvb

Show last executed queries on sqlserver

SELECT deqs.last_execution_time AS [Time]
	,dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE lower(dest.TEXT) LIKE '%binaries%'
ORDER BY deqs.last_execution_time DESC

gitfvb

<#

# A helpful code snippet to change console encoding

# Change encoding of powershell console (this does not change [System.Text.Encoding]::Default
ping | Out-Null
# Change the console output to UTF8
$originalConsoleCodePage = [Console]::OutputEncoding.CodePage
[Console]::OutputEncoding = [text.encoding]::utf8

gitfvb

<#

Sometimes it is hard to make sure a function passes an array back if it is only one element
With @( ) you can make sure you have an array, even with one element. But in the function to always return an array,
do something like below, inspired by https://evotec.xyz/powershell-returning-one-object-from-function-as-an-array/

#>

Function Do-Foo() {
    

gitfvb

# Check the installed capabilites

Get-WindowsCapability -Online | Where-Object Name -like 'OpenSSH*'

# Install OpenSSH Server

Add-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0

# Start service and set it automatically
Start-Service sshd

gitfvb

Used the following files from nuget

        FileDescription                        CompanyName           FileVersionRaw
        ---------------                        -----------           --------------
        Microsoft.Bcl.AsyncInterfaces          Microsoft Corporation 4.700.20.21406
        Npgsql                                 Npgsql                4.0.12.0
        System.Buffers                         Microsoft Corporation 4.6.28619.1
        System.Memory                          Microsoft Corporation 4.6.28619.1

gitfvb

if you have multiple columns and want to calculate the differences in seconds and milliseconds, a query like this can help to produce output like in the csv file in this gist.

This is the query

select
	"timestamp"
	,strftime('%f',julianday("inserted")-julianday("timestamp")) "Timestamp to inserted"
	,strftime('%f',julianday("response_calculated")-julianday("inserted")) "Response calculated"
	,strftime('%f',julianday("response_timestamp")-julianday("response_calculated")) "API Call finished"

gitfvb

################################################
#
# CREATE WINDOWS TASK
#
################################################

 # Confirm you read the licence details
 $createTask = $Host.UI.PromptForChoice("Confirmation", "Do you want to create a scheduled task for the check and refreshment?", @('&Yes'; '&No'), 0)

gitfvb

<Extras>
  <AddColumn>
    <runcommand>
      <command>powershell.exe</command>
      <arguments> -ExecutionPolicy Bypass -File "D:\Scripts\GenericFTP\test.ps1" -inputFile "{%directory%}{%filename%}.{%ext%}" -scriptPath "D:\Scripts\GenericFTP"</arguments>
      <workingdirectory></workingdirectory>
      <waitforcompletion>true</waitforcompletion>
    </runcommand>
  </AddColumn>
</Extras>

gitfvb

When downloading the current (today: 2021-03-23) precompiled binaries or the cli of sqlite, the json extension is already compiled into the exe/dll

When you want to use it for db browser (which does not have the json1 extension right now), just download the precompiled binaries for windows from https://www.sqlite.org/download.html

Unzip it and replace the sqlite3.dll in C:\Program Files\DB Browser for SQLite

After re-opening DB Browser, you can execute queries like

SELECT JSON('{"a": "b"}')

gitfvb

function getRandomString([int] $length) {
    $random = [Random]::new()
    $chars = @("0", "2", "3", "4", "5", "6", "8", "9", "a", "b", "c", "d", "e", "f", "g", "h", "j", "k", "m", "n", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z")
    $stringBuilder = ""
    for ($i = 0; $i -lt $length; $i++) {
        $stringBuilder += $chars[$random.Next($chars.Length)]
    }
    return $stringBuilder
}