I recommend this book to all database administrators who still have not jumped in and executed their first PowerShell command. I know of several DBAs who for one excuse reason or another, keep postponing their exposure to the next generation command line shell. This volume makes a good first PowerShell book for DBAs. It covers … Read more
PROBLEM: You manage many MS SQL servers and need an easy way to review jobs that have failed. SOLUTION: I will show you one way to accomplish this task with results displaying on the screen or to a file. To make your life easier, I recommend you download & install PowerGUI from Quest Software. This … Read more
PROBLEM: You need to find out which computers and servers in my network have an instance of MS SQL installed. SOLUTION: In this solution we’ll create an instance of the Windows Management Instrumentation (WMI) for each computer in a list. We’ll then query the Win32_Service class for the existence of a service which name starts … Read more
PROBLEM: I need an easy way to script out all the tables from a database in MS SQL 2000 and above. SOLUTION: Like anything in programming, there are a thousand ways to skin a cat. Here, I’ll show you the simplest way I found to meet this request. Go ahead and open a new session … Read more
USE: To convert the GMT data returned from error logs to a readable date time value. CLEAR $sHostName = “TUIRA” $aLogName = “Application” $FromDate = [datetime]::today $WMIQuery = “LogFile=’$aLogName’ and Type=’Error’ and TimeGenerated>=’$FromDate'” Get-WmiObject Win32_NTLogEvent -ComputerName $sHostName ` -Filter “$WMIQuery” ` -ErrorAction SilentlyContinue | ` SELECT SourceName, ` @{Name = “TimeGenerated”; EXPRESSION = {$_.ConvertToDateTime($_.TimeGenerated)}}, ` … Read more
This script was written by ed wilson at Use PowerShell to Data Mine Your Outlook Inbox CLEAR Function Get-OutlookInBox { Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null $olFolders = “Microsoft.Office.Interop.Outlook.olDefaultFolders” -as [type] $outlook = new-object -comobject outlook.application $namespace = $outlook.GetNameSpace(“MAPI”) $folder = ($namespace.getDefaultFolder($olFolders::olFolderInBox)).folders|WHERE {$_.Name -eq “DDUMP”} $folder.items | Select-Object -Property Subject, ReceivedTime, Importance, SenderName } #end function … Read more
USE: To generate T-SQL code for all indexes and PK constraints from a database. CLEAR $sSRCServer = “TPW520” $sSRCDatabase = “iSQLPS” $oSO = New-SqlScriptingOptions $oSO.ClusteredIndexes = $true $oSO.Indexes = $true $oSO.NonClusteredIndexes = $true $oSO.IncludeIfNotExists = $true $oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable foreach ($oTable in $oTables){ $oT = $oTable.Indexes foreach ($oIndex in $oT) { $DDL = … Read more