Skip to content

Instantly share code, notes, and snippets.

View KirillPashkov's full-sized avatar

Kirill Pashkov KirillPashkov

  • Russia, Moscow
View GitHub Profile
@KirillPashkov
KirillPashkov / Create Windows Scheduled Task For Powershell Script
Created November 12, 2021 15:23
Create Windows Scheduled Task For Powershell Script
schtasks /create /tn Report_Task /tr "powershell -NoLogo -WindowStyle hidden -file Script.ps1" /sc Daily /st 18:00 /ru System
@KirillPashkov
KirillPashkov / Get SQL Procedure Results And Send CSV to SFTP
Created November 12, 2021 15:22
Get SQL Procedure Results And Send CSV to SFTP
# Variable Declaration Region
# WINSCP DLL Location
$WINCSPDLL = 'C:\Temp\Send-ReportToNYSFTP\WinSCPnet.dll'
# SFTP Credentials
$SFTPHostName = '***'
$SFTPHostLogin = '***'
# PPK Data
$PPKLocation = 'C:\Temp\Send-ReportToNYSFTP\Keys\***.ppk'
$PPKPassphrase = '***'
$lookup = '***'
Out-File -FilePath C:\temp\results.txt -InputObject $lookup -Encoding utf8 -Force -Append
gci -File 'C:\temp\1' -Recurse -Filter '*.*' -Exclude '*.zip'|
select -exp FullName | % {
$f = gc $_; $l=1
foreach($r in @($f.Split("`n"))){
#if ($r.Length -gt 0 -and $r -match $lookup){Write-Host $("File: {0}`tLine: {1}`tString: {2}" -f $_,$l,$r) -b Black -f Green}; $l++
if ($r.Length -gt 0 -and $r -match $lookup){Out-File -FilePath C:\temp\results.txt -InputObject $("[File: {0}][Line: {1}][String: {2}]" -f $_,$l,$r) -Encoding utf8 -Force -Append }; $l++
}
}
select t1.c1, t2.c1 from t1 left join t2 on t1.c1=t2.c1
union all
select t1.c1, t2.c1 from t1 right join t2 on t1.c1=t2.c1
where t1.c1 is null
order by 1, 2;
Function FindInArray(InputArray,LookupValue)
Dim Found: Found = False
For i = 0 To UBound(InputArray)
If InputArray(i) = LookupValue Then
Found = True
Exit For
End If
Next
FindInArray = Found
End Function
Function MatchRegex(RegexPattern,InputString)
If _
IsNull(RegexPattern) Or IsEmpty(RegexPattern) Or _
IsNull(InputString) Or IsEmpty(InputString) _
Then
MatchRegex = False
Else
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Global = True
objRegEx.Pattern = RegexPattern
DBCC USEROPTIONS; --for user options
@@OPTIONS; --for server options
SET NOCOUNT ON
DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
-- Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
set nocount on
go
set cursor_close_on_commit off
go
if object_id('tempdb..#t', 'U') is not null
drop table #t
create table #t (num int)
insert #t values (1),(2),(3),(4);
Function InvokePS(Command)
cmd = "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command " & Command
Set shell = CreateObject("WScript.Shell")
Set executor = shell.Exec(cmd)
executor.StdIn.Close
InvokePS = executor.StdOut.ReadAll
End Function
Output = REPLACE(InvokePS(Command),vbCrLf,"")
Function IfNull(Expression,Replacement)
if IsNull(Expression) then
IfNull = Replacement
Else
IfNull = Expression
End If
End Function