Author name: Tim van Kooten Niekerk

About me / Hey I’m Tim. I work as a systems integration specialist for a large educational institution in the Netherlands. A part of my free time I spend making and creating music.

MSSQL update value dependent on original

/ MSSQL Server /

Query to change the format of a postal code string for specific postal codes. This specific script changes all values in the postcode column from format  ‘1234AB’ to ‘1234 AB’.

UPDATE tablename
SET postcode = LEFT(LTRIM(postcode),4) + ' ' + RIGHT(RTRIM(postcode),2)
WHERE
  LEN(LTRIM(postcode)) = 6 AND
  LEN(REPLACE(postcode, ' ', '')) = 6 AND
  ((land = 'Nederland') OR (land = 'Nederland (NL)'))

Other example to replace values. This time replacing a value with a string from another table.

UPDATE   a
SET      ColumnX = b.ColumnY
FROM     Table1 a
JOIN     Table2 b ON b.ID=a.ID

PowerShell Check File LastWriteTime

/ PowerShell Scripting /

PowerShell script to start a specific routine when a file is older than two days (LastWriteTime). This specific example writes an error in the application event log.

# *****************************************************************************
# Scriptnaam: CHKMODTIME.PS1
# Geschreven_door: Tim van Kooten Niekerk
# Versie: 2010.10.29.01
# Info: PowerShell script t.b.v. uitvoeren actie als de wijzigingsdatum
# Info: van het opgegeven bestand ouder is dan ingestelde waarde.
# *****************************************************************************

function fGetFileModTime([string]$sFileName) {
  $vFileInfo = get-childitem $sFileName
  return $vFileInfo.lastwritetime 
}

# Opvragen van wijzigingsdatum bestand...
[datetime]$dFileModTime = fGetFileModTime("D:PadNaarBestand.bak")
# Voer een actie uit als de wijzigingsdatum van het bestand ouder is dan 2 dagen...
if ((Get-date).AddDays(-2) -gt $dFileModTime) {
  # Schrijf Error melding weg naar eventlog of voer een andere actie uit...  
  $oEventLog = new-object System.Diagnostics.EventLog('Application')
  $oEventLog.MachineName = "."
  $oEventLog.Source = "WSH"
  $oEventLog.WriteEntry("Back-up is to old...", "Error")
}

 

PowerShell Edit Multiple Files

/ PowerShell Scripting /

PowerShell example to edit multiple files. This specific example remove the first 15 chars from every XML file in a directory.

get-childitem D:TEMPMessages *.xml |
  % { 
         [string]$sFileContent = cat $_.fullname
         $sFileContent = $sFileContent.substring(15,$sFileContent.length-15) | Out-File -Filepath $_.fullname
         echo $_.fullname
  }

PowerShell BizTalk WMI

/ PowerShell Scripting /

Specific PowerShell script to shutdown a BizTalk receive location when a disk is more then 80% full. This application uses WMI to request disk info and shutdown the the receive location.

function fGetDiskFillPerc([string]$vComputer, [string]$vDiskID) {
  $vCommandLine1 = "Get-WMIObject -ComputerName '" + $vComputer + "' Win32_LogicalDisk -filter `"DeviceID = '" + $vDiskID + "'`""
  $vResult1 = Invoke-Expression $vCommandLine1
  foreach ( $vDisk in $vResult1 ) {
    if ($vDisk.DriveType -eq 3) { 
      [decimal]$dDiskFill = $vDisk.Size - $vDisk.FreeSpace
      return $dDiskFill / $vDisk.Size * 100
    }
  }
}
 [decimal]$dDiskFillPercResult = fGetDiskFillPerc "." "D:"
if ( $dDiskFillPercResult -gt 80 ) {
  $oRecLoc1 = get-wmiobject MSBTS_ReceiveLocation  -namespace 'rootMicrosoftBizTalkServer' -filter "Name='ReceivePortName'"
  [void]$oRecLoc1.Disable()
}

 

MSSQL DBFile SpaceUsed

/ MSSQL Server /

Script to return database file usage (GB) from a database.

USE [DATABASE]
GO
SELECT DBFiles.fileid as FileID,
       DBFiles.filename as FileName,
       DBFiles.name as Name,
       CONVERT(decimal(18,3),ROUND(((CAST(FILEPROPERTY(DBFiles.Name, 'SpaceUsed') AS decimal) * 8) /1024 /1024), 3)) AS SpaceUsedGB, 
       CONVERT(decimal(18,3),ROUND(((CAST(DBFiles.size AS decimal) * 8) /1024 /1024), 3)) AS FileSizeGB
FROM dbo.sysfiles DBFiles
GO

 

VBS Convert HTML Tables

/ VisualBasic Scripting /

Visual Basic script to convert HTML to CSV or TEXT using the Excel.Application object.

'****************************************************************************************
'bestandsnaam: ConvertTable2X.vbs
'geschreven_door: Tim van Kooten Niekerk
'versie: 17:38 15-4-2010
'Converteer html tabellen naar CSV of TEXT m.b.v. het Excel object
'****************************************************************************************

' Global variable and const declarations
option explicit
'on error resume next
dim oEXC, oArg
const xlCSV=6
const xlTXT=21

' Global objectdeclarations
set oArg = WScript.Arguments 
set oEXC = CreateObject("Excel.Application")

' Functie inlezen argumenten van de commandline (0-?)
function fReadArgument(vArgNumber)
  On Error Resume Next
  fReadArgument = oArg(vArgNumber)
end function

' Convert2X function...
function fTable2X(sHTMLFile, sCSVFile, sFileType)
  if (sHTMLFile ‹› "") and (sCSVFile ‹› "") then
    dim oWB
    set oWB =  oEXC.Workbooks.Open (sHTMLFile)
    'oEXC.Visible = True
    oEXC.Application.DisplayAlerts = False
    oEXC.ActiveWorkbook.SaveAs sCSVFile,  sFileType
    oEXC.ActiveWorkbook.Close False 
    oEXC.Application.DisplayAlerts = True

    ' Opschonen
    Set oWB =  Nothing
  end if
end function

' Start programma...
if fReadArgument("0") ‹› "" and fReadArgument("1") ‹› ""  and fReadArgument("2") ‹› "" then
  fTable2X fReadArgument("0"), fReadArgument("1"), fReadArgument("2")
else
  MsgBox ("Een of meerdere argumenten ontbreken..." & vbCrLf & vbCrLf & "Gebruik: convertTable2X   " & vbCrLf & "(filetypenum: 6=CSV, 21=TXT)")
end if

' Opschonen
set oArg = Nothing
set oEXC = Nothing

 

Powershell Fail-Back script

/ PowerShell Scripting /

Simple script using the cluster command to return a cluster group to a prefered node.

# ********************************************************************
# Scriptnaam: AUTOFAILBACK.PS1
# Geschreven_door: Tim van Kooten Niekerk
# Versie: 20100412
# Info: Failback to preferred node (TST en ACC omgeving) 
# ********************************************************************

# Constanten en andere string waarden...
[string]$sClusterGroupCmd = "cluster group"
[string]$sOutputLOGFile = "AutoFailBack.log"
[string]$sDateTime = Get-Date -f o
$sDateTime + ";START;>>> Start controle en AutoFail-Back procedure..." | Out-File -Filepath $sOutputLOGFile

function fAutoFailBack([string]$sClusterGroup1, [string]$sPreferredNodeCG1) {
  [string]$sDateTime = Get-Date -f o
  [string]$sClusterGroup1Clean = $sClusterGroup1.Replace("", "")
  $vResult1 = Invoke-Expression $sClusterGroupCmd
  $vResult2 = $vResult1 | select-string -Pattern $sClusterGroup1 | select-string -Pattern "Online" | select-string -Pattern $($sPreferredNodeCG1)
  if ($vResult2.LineNumber -gt 0) {
    $vResultFIN = $sDateTime + ";OK;" + $vResult2.Line
	
  } else {
    [string]$sClusterGroupCmdFO =  $sClusterGroupCmd  + " `"" + $($sClusterGroup1Clean) +  "`" `/moveto:" + $($sPreferredNodeCG1)
    $vResultB1 = Invoke-Expression $sClusterGroupCmdFO
    $vResultB2 = $vResultB1 | select-string -Pattern $sClusterGroup1 | select-string -Pattern "Online" | select-string -Pattern $($sPreferredNodeCG1)
    if ($vResultB2.LineNumber -gt 0) { 
      $vResultFIN = $sDateTime + ";FO-OK;" + $vResultB2.Line
    } else {
      $vResultC1 = Invoke-Expression $sClusterGroupCmd
      $vResultC2 = $vResultC1 | select-string -Pattern $sClusterGroup1
      $vResultFIN = $sDateTime + ";FO-FAIL;" + $vResultC2.Line
    }

  }
  # Schrijf resultaat weg naar Logbestand
  $vResultFIN | Out-File -Filepath $sOutputLOGFile -Append
}

fAutoFailBack "CLUSTERGROUPNAME" "NODENAME"
fAutoFailBack "CLUSTERGROUPNAME2" "NODENAME"

 

MSSQL Log space used percentage

/ MSSQL Server /

Met onderstaande T-SQL kun je de vulling in procent van het transaction log bestand van een specifieke database opvragen en monitoren. Dit is alleen handig in specifieke situaties. Bij een correct database onderhoudsplan zal dit niet nodig zijn.

CREATE TABLE #tTLSpace 
( 
DBName sysname, 
TLSize decimal(18,5), 
TLUsed decimal(18,5), 
status INT 
)
GO

INSERT INTO #tTLSpace 
       exec ('DBCC SQLPERF(logspace)') 
GO

DECLARE @dTLUsed decimal
SELECT @dTLUsed = TLUsed from #tTLSpace WHERE DBName = 'DATABASE'
IF ( @dTLUsed > 90)
BEGIN
  PRINT @dTLUsed
  PRINT 'TransactionLog bestand 90 procent vol...'
END
GO

DROP TABLE #tTLSpace 
GO

MSSQL check database-backup

/ MSSQL Server /

Script to check for databases not backupped in the last two days

SELECT sys.databases.name
FROM sys.databases
WHERE sys.databases.name not in
(SELECT DISTINCT sys.databases.name from sys.databases 
INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name
WHERE (msdb..backupset.type = 'D' AND backup_start_date > (GetDate() -2)))
AND sys.databases.state = 0 AND sys.databases.name ‹› 'tempdb'

 

GNU/Linux basic VI commands

/ GNU/Linux /

Text edit

a[text toevoegen na] i[text toevoegen voor] o[text onder toevoegen] O[text boven toevoegen] R[text overschrijven]

Text delete

d[getal+richting verwijderen] d$[verwijder text tot einde regel] dd[verwijder hele regel] x[verwijder huidig character] X[verwijder vorig character] J[koppel de huidige en de volgende regel aan elkaar]

Copy / Paste 

y[getal+richting naar buffer kopieren] yw[woord naar buffer kopieren] p[plaats buffer na] P[plaats buffer voor]

Other

u[ongedaan maken] ~[hoofdletters/kleine letters]

File management

:wq[bestand opslaan en afsluiten :w[bestand opslaan] :q[afsluiten] :q![geforceerd afsluiten zonder opslaan]