Archive for August 2011

Fun with the Get-Hotfix cmdlet

With the Get-Hotfix cmdlet you can query the list of hotfixes that have been applied to computers. ?View Code POWERSHELLGet-Hotfix | Format-Table -AutoSize; This will display the list of hotfixes installed on the local computer. Source Description HotFixID InstalledBy InstalledOn —— ———– ——– ———– ———– SO0590 Update 982861 NT AUTHORITY\SYSTEM 07/07/2011 00:00:00 SO0590 Update KB958830 […]

Fun with the Get-EventLog cmdlet

The Get-EventLog cmdlet is great for working with the Windows Event Logs on local and remote computers. It includes lots of parameters that make life much easier than using the Event Viewer GUI. To list the available logs on the local computer just execute; ?View Code POWERSHELLGet-EventLog -List | Format-Table -AutoSize; Max(K) Retain OverflowAction Entries […]

TRIGGER_NESTLEVEL TSQL Function

This function is used to determine the current nest level or number of triggers that fired the current one. This could be used to prevent triggers from firing when fired by others. Here’s an example that does that; we have two tables with triggers, that fire AFTER INSERT, and insert into the other table. The […]

The CONNECTIONPROPERTY TSQL Function

The CONNECTIONPROPERTY function can be used to obtain information about the current connection. The information available is similar to the sys.dm_exec_connections system view. ?View Code TSQLSELECT ‘Net Transport’ AS Property, CONNECTIONPROPERTY(’net_transport’) AS Value UNION ALL SELECT ‘Protocol type’, CONNECTIONPROPERTY(’protocol_type’) UNION ALL SELECT ‘Auth scheme’, CONNECTIONPROPERTY(’auth_scheme’) UNION ALL SELECT ‘Local net address’, CONNECTIONPROPERTY(’local_net_address’) UNION ALL SELECT […]

Encryption with TSQL

SQL Server has a bunch of encryption functionality at its disposal. The EncryptByPassphrase allows us to quickly encrypt data using a password. This function uses the Triple DES algorithm to protect data from prying eyes. To encrypt a section of text we supply a password and the text to the function; ?View Code TSQLSELECT ENCRYPTBYPASSPHRASE(’secret’, […]