aev-mambro2

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

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

## 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

#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

<#
 # 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

Keybase proof

I hereby claim:

• I am aev-mambro2 on github.
• I am aveltstramambro (https://keybase.io/aveltstramambro) on keybase.
• I have a public key ASAaQpE3YDIgBdp1YgUMN2YgKm-V7HCAWHHESAaJV1JbSQo

To claim this, I am signing this object:

aev-mambro2

-- 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

/*
* 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

$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

$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,"");