Skip to content

Instantly share code, notes, and snippets.

View aev-mambro2's full-sized avatar

André E. Veltstra aev-mambro2

View GitHub Profile
Use IFF for field expressions in TSQL
TSQL, Microsoft's SQL dialect for SQLServer, does not accept simple equations and expressions in place of its fields.
So this it won't execute:
SELECT NAME='aev-mambro2' as Found FROM USERS;
Instead, Microsoft wants us to use IIF as a field expression, like so:
@aev-mambro2
aev-mambro2 / use-cte-when-unit-testing-in-tsql.txt
Last active February 4, 2021 20:38
Use Common Table Expressions when Unit Testing in TSQL
Use Common Table Expressions when Unit Testing in TSQL
TSQL, Microsoft's SQL dialect for SQLServer, is capable of executing CTE, also known as Common Table Expression.
We can use these to separate concerns when setting up unit tests, like so, to determine whether a new process
returns the same records as an old process:
WITH old_batch (found) as (SELECT FIELD AS found FROM OLD_QUERY_OR_PROCESS),
new_batch (found) as (SELECT FIELD AS found FROM NEW_QUERY_OR_PROCESS),
compare_old_to_new (found) as (select found from old_batch where found not in (select found from new_batch)),
compare_new_to_old (found) as (select found from new_batch where found not in (select found from old_batch)),
@aev-mambro2
aev-mambro2 / disable-remote-scheduled-tasks-using-powershell.ps
Last active February 24, 2021 16:02
disable-remote-scheduled-tasks-using-powershell
## This only works if you have CIM, WMI, and task change privileges on the remote host servers.
# List the known hosts, and for each host their known scheduled task paths.
# We're only interested in production task paths - no need to specify test task paths.
# (You did separate those, didn't you?)
#
# The host name is just the NetBUI name: no slashes, ports, protocols, or paths.
# The scheduled task path starts with a backslash (which stands for Task Scheduler -> Task Scheduler Library)
# and then follows the hierarchy you see in the task scheduler. Use a wildcard * to include any task in a path.
$taskHostPaths = @{
@aev-mambro2
aev-mambro2 / use-powershell-to-generate-db-regeneration-scripts.ps
Last active March 30, 2021 13:46
use-powershell-to-generate-db-regeneration-scripts
#Based on the article by Phil Factor, 2012, for Redgate:
#https://www.red-gate.com/simple-talk/sql/db-administration/automated-script-generation-with-powershell-and-smo/
Write-Host "This script writes 1 file that contains the SQL to regenerate an SQL Server Database. What file name should the script give it?";
$save_chooser = New-Object -Typename System.Windows.Forms.SaveFileDialog;
$save_chooser.ShowDialog();
$write_output_to=$save_chooser.FileName;
write-host "Saving to: " $write_output_to
$server_instance=(Read-Host -Prompt "Which server?");
$db_name=(Read-Host -Prompt "Which database?");
@aev-mambro2
aev-mambro2 / start-scheduled-task-on-remote-mswindows-server.ps1
Last active March 11, 2021 20:14
How to start a scheduled task on a remote microsoft windows server
<#
# Goal: to run a scheduled task on a remote windows server.
# Requirements: user must have CIM, WMI, and task admin privileges on the remote server.
# (c) 2021, A.E.Veltstra
#>
# Access the remote server. The computer name should be the short NETBUI name, without a domain.
$_session = New-CimSession -ComputerName "NETBUI NAME";
# Point to where the task is stored in the task scheduler. The opening \ points to what the
@aev-mambro2
aev-mambro2 / keybase.md
Created March 31, 2021 18:08
Keybase Proof

Keybase proof

I hereby claim:

To claim this, I am signing this object:

@aev-mambro2
aev-mambro2 / get-difference-between-2-lists-of-values-in-tsql.sql
Created April 19, 2021 15:50
Get difference between 2 lists of values in T-SQL
-- Using common table expressions
with
-- Supply list A
a (i) as (select i from (values
('6GA00215S'),
('6GU00208S'),
('6PO00396I'),
('6PO00398I')
) A(i)),
-- Supply list B
@aev-mambro2
aev-mambro2 / build.gradle
Last active May 3, 2021 12:59
build.gradle
/*
* Gradle user guide: https://docs.gradle.org/
* Gradle install: https://gradle.org/install/
* Based on Gradle version 5.4.1
* @author A.E.Veltstra
* @since 2.20.0121.1100
* @version 2.21.302.1025
*/
import io.franzbecker.gradle.lombok.task.DelombokTask
@aev-mambro2
aev-mambro2 / export-scheduled-tasks-from-remote-server-to-local-folder-structure.ps1
Created July 19, 2021 15:36
export-scheduled-tasks-from-remote-server-to-local-folder-structure.ps1
$servers = @{
"netbui-server-name" = @("\task\scheduler\path\*"),
"other-netbui-server" = @(
"\task\scheduler\path1\*",
"\task\scheduler\path2\*",
)
}
$outputFolder = "C:\Users\Me\Documents\scheduled-task-exports\";
foreach ($h in $servers.Keys) {
@aev-mambro2
aev-mambro2 / import-scheduled-tasks-from-local-folder-structure-to-remote-server.ps1
Created July 19, 2021 18:48
import-scheduled-tasks-from-local-folder-structure-to-remote-server.ps1
$targetServer = "netbui-server-name";
$inputFolder = "C:\Users\Me\Documents\scheduled-task-exports\the-date\$($targetServer)";
$tasksNamespace = "\my\Scheduled\Tasks\Path";
$taskRunnerUserName = "WHO?";
$taskRunnerPassword = "how?";
$c = New-CimSession -ComputerName $targetServer;
Get-ChildItem -Recurse -Path (Join-Path $inputFolder $tasksNamespace) -Filter "*.xml" | foreach {
$newTaskName = $_.BaseName;
$newTaskPath = $_.DirectoryName.Replace($inputFolder,"");