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:
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: |
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)), |
## 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 = @{ |
#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?"); |
<# | |
# 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 |
I hereby claim:
To claim this, I am signing this object:
-- Using common table expressions | |
with | |
-- Supply list A | |
a (i) as (select i from (values | |
('6GA00215S'), | |
('6GU00208S'), | |
('6PO00396I'), | |
('6PO00398I') | |
) A(i)), | |
-- Supply list B |
/* | |
* 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 |
$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) { |
$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,""); |