SQLQueryWindowMultipleQueriesSampleFormsGUI.ps1
|
<#PSScriptInfo .VERSION 1.1 .GUID c5d9c0d1-32de-4d43-81ce-80d3b6b0ffe7 .AUTHOR John Merager .COMPANYNAME .COPYRIGHT 12/21/2020 .TAGS SQL, TSQL, T-SQL, SQL Server, MS SQL Server, Microsoft SQL Server, Query, Queries, Multiple, Report, Reporting, Loop, Grid, UI, GUI, Forms .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES 1.0 This comes with multiple pre-written queries, including SAP related sample queries (Application specific). Purpose: Sample SQL Query Window UI. It can be modified with queries specific to your needs. Execute example: powershell.exe -ExecutionPolicy Bypass -file .\SQLQueryWindowMultipleQueriesSampleFormsGUI.ps1 1.1 - Fixed grid size so scroll bars show properly #> <# .DESCRIPTION SQL Query Window UI with Multiple Pre-written Queries Features: * Able to return multiple results * Tests port 1433 before connecting to SQL to avoid longer connection timeout * Click on buttons to run pre-written queries - Option to show query from button either with or without executing * Able to re-query previous query on a timer * Able to limit rows returned (Top rows). Avoid too many rows returned! * Filter options when executing queries * -File parameter allows a list of SQL Servers (and databases) which shows in a dropdown * Login to SQL either with Windows Authentication or SQL User * Get button to get list of databases of current SQL Server and shows in database dropdown * Execute queries from query window * Has Settings Tab for additional options * Has SAMPLE buttons which can be modified in the script with additional queries #> Param([String]$Server="",[String]$Database="",[String]$UserName="",[String]$Password="",[String]$File="",[Switch]$Loop,[Int]$LoopSec=30,[Switch]$ShowQuery,[Switch]$ShowQueryNoExec,[String]$Query="") <# .SYNOPSIS Microsoft SQL Server Query Window Sample UI with Multiple Pre-written Queries .DESCRIPTION SQL query window UI used to execute pre-written queries. This comes with multiple pre-written sample queries. Modify to execute commonly run queries in your environment or for your application. This can be used for managing, trouble shooting, or running reports in SQL Server. Avoid large amounts of data being returned. .PARAMETER Server Name of the SQLServer or Multiple SQL Servers (comma delimited, not spaces). Example: Server1,Server2 .PARAMETER Database Database(s) to connect to. Blank for default database. Comma delimited (no spaces) for matching with -Server when multiple are specificed .PARAMETER UserName SQL User Name to login to SQL. Default is Windows Authentication. .PARAMETER Password SQL User Password to login to SQL .PARAMETER File CSV List of servers. Columns: Name, Server, Database .PARAMETER Loop Enable re-query of previous query in specified number of seconds .PARAMETER LoopSec Number of seconds to re-query previous query .PARAMETER ShowQuery Enables to show query in query window and Execute when clicking on a button with a pre-written query .PARAMETER ShowQueryNoExec Enables to show query in query window, but no execute when clicking on a button with a pre-written query .PARAMETER Query Put query in Query Window .EXAMPLE powershell.exe -ExecutionPolicy Bypass -file .\SQLQueryWindowMultipleQueriesSampleFormsGUI.ps1 .EXAMPLE powershell.exe -ExecutionPolicy Bypass -file .\SQLQueryWindowMultipleQueriesSampleFormsGUI.ps1 -File c:\serverlist.csv .EXAMPLE powershell.exe -ExecutionPolicy Bypass -file .\SQLQueryWindowMultipleQueriesSampleFormsGUI.ps1 -UserName sa -Password myPassWord .NOTES #> ############################################################################### ### Written By: John Merager # ############################## ### 12/21/2020 - Created for running multiple pre-written queries in Microsoft SQL Server - Version 1.0 - John Merager ### 12/29/2020 - Version 1.1 - Fixed grid size so scroll bars show properly ### ############################################################################### ### Starting ############################################################################### write-host "Starting...Please Wait..." ############################################################################### ### Save Error Preference ############################################################################### #$ErrorPreference=$script:ErrorActionPreference ############################################################################### ### Variables ############################################################################### $CurrentComputer = ([String]$env:COMPUTERNAME).ToUpper() # Server Name $Interval = 1 # Refresh Interval (default 1 seconds) ############################################################################### ### Change Time Default Format ############################################################################### $currentThread = [System.Threading.Thread]::CurrentThread $culture = [CultureInfo]::InvariantCulture.Clone() $culture.DateTimeFormat.ShortDatePattern = 'MM/dd/yyyy' $culture.DateTimeFormat.ShortTimePattern = 'HH:mm:ss' $currentThread.CurrentCulture = $culture ############################################################################### ### Load the .net assembly and Variables ############################################################################### $global:SQLLastQuery="" $global:SQLCounter=30 $global:SQLServerList=@() $global:SQLResultTabs=@() $global:SQLResultGrids=@() $global:LastSQLServer="" $global:LastSQLServerChanged=$False [void][System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") [void][System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") $ToolTip = New-Object System.Windows.Forms.ToolTip ############################################################################### ### Functions: Status Bar ############################################################################### Function ClickChangeTab { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $StatusBar_P1.Text = $global:TabIndexList[$tab.SelectedIndex].Status $StatusBar_P2.Text = $global:TabIndexList[$tab.SelectedIndex].Details $StatusBar_P3.Text = $global:TabIndexList[$tab.SelectedIndex].Server } Function StatusAddToBar($Index) { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} if (("$($global:TabIndexList[$Index].Status)").length -lt 50) { $global:TabIndexList[$Index].Status += "$([char]0x2588)" if ($chkSQLDebug.Checked) {write-host -NoNewline "."} } else { $global:TabIndexList[$Index].Status = "$([char]0x2588)" if ($chkSQLDebug.Checked) {write-host "."} } $StatusBar_P1.Text=$global:TabIndexList[$Index].Status } Function StatusUpdate($Index,$Status1,$Status2,$Status3) { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} if ($Status1 -ne $null) {$global:TabIndexList[$Index].Status = "$Status1"} if ($Status2 -ne $null) {$global:TabIndexList[$Index].Details = "$Status2"} if ($Status3) {$global:TabIndexList[$Index].Server = "$Status3"} elseif ($Status3 -eq "") {$global:TabIndexList[$Index].Server = "$CurrentComputer"} if ($Index -eq $tab.SelectedIndex) { if ($Status1 -ne $null) {$StatusBar_P1.Text = "$Status1"} if ($Status2 -ne $null) {$StatusBar_P2.Text = "$Status2"} if ($Status3) {$StatusBar_P3.Text = "$Status3"} elseif ($Status3 -eq "") {$StatusBar_P3.Text = "$CurrentComputer"} } } ############################################################################### ### Functions: Mouse ############################################################################### Function ShowMouseHoverToolTip { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} Switch ($this.name) { "test1" {$tip = "Test1"} "test2" {$tip = "Test2"} "SyncAutoClear" {$tip = "Clear below table for each new execution"} default {$tip = ""} } $ToolTip.SetToolTip($this,$tip) } ############################################################################### ### Functions: Date Time ############################################################################### Function UTCtoLocalTime([DateTime]$UTCTime) { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} foreach ($UTC in [System.TimeZoneInfo]::GetSystemTimeZones()) { if ($UTC.Id -eq 'UTC') { return ($UTCTime).AddMinutes(([DateTime]::Now-[TimeZoneInfo]::ConvertTime([DateTime]::Now, $UTC)).TotalMinutes) } } } Function UTCtoLocalTimeMinutes([DateTime]$UTCTime) { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} foreach ($UTC in [System.TimeZoneInfo]::GetSystemTimeZones()) { if ($UTC.Id -eq 'UTC') { return ([DateTime]::Now-[TimeZoneInfo]::ConvertTime([DateTime]::Now, $UTC)).TotalMinutes } } } Function CurrentUTC { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} foreach ($UTC in [System.TimeZoneInfo]::GetSystemTimeZones()) { if ($UTC.Id -eq 'UTC') { return [TimeZoneInfo]::ConvertTime([DateTime]::Now, $UTC) } } } ############################################################################### ### Functions: Other ############################################################################### Function OpenTextBox($TextValue) { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $subform = new-object System.Windows.Forms.Form $subform.Size = new-object System.Drawing.Size 600,400 $OpenTextBox = new-object System.windows.forms.TextBox $OpenTextBox.Location = New-Object System.Drawing.Size(0,0) $OpenTextBox.Size = New-Object System.Drawing.Size(578,353) $OpenTextBox.Multiline=$true $TextValue=$TextValue.replace("><",">`r`n<") $TextValue=$TextValue.replace(">`n",">`r`n") $OpenTextBox.ScrollBars="Vertical" #$OpenTextBox.WordWrap=$False #$OpenTextBox.ScrollBars="Both" $OpenTextBox.Text = "$TextValue" $subform.Controls.Add($OpenTextBox) $subform.topmost = $true $subform.Add_SizeChanged({OpenTextBoxSizeChanged}) $subform.showdialog() } Function OpenTextBoxSizeChanged { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $OpenTextBox.Width=$subform.Width-22 $OpenTextBox.Height=$subform.Height-47 } ################################################################### #################### SQL Tab Functions Function ClickSQLSourceList { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $cbxSQLSource.Items.Clear() $cbxSQLSource.ResetText() foreach ($SQLInfoRow in $TblServersList.Rows) { [void] $cbxSQLSource.Items.Add($SQLInfoRow.Name) } } Function ChangedSelectSQLSource { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $cbxSQLDatabase.Items.Clear() [void] $cbxSQLDatabase.Items.Add("master") [void] $cbxSQLDatabase.Items.Add("msdb") [void] $cbxSQLDatabase.Items.Add("model") [void] $cbxSQLDatabase.Items.Add("tempdb") if ($global:SQLServerList -contains $cbxSQLSource.SelectedItem) { $tbxSQLServer.Text=$cbxSQLSource.SelectedItem $cbxSQLDatabase.Text="" } else { foreach ($SQLInfoRow in ($TblServersList.Rows |where {$_.Name -eq $cbxSQLSource.SelectedItem})) { $tbxSQLServer.Text=$SQLInfoRow.Server if ($chkSQLAddDropdownDB.Checked) { $cbxSQLDatabase.Text=$SQLInfoRow.Database } else { $cbxSQLDatabase.Text="" } } } } Function PingPort([string]$computer) { if ($chkSQLCheckPort.Checked -eq $True -And "$computer" -ne "") { $Port=[int]1433 $TCPtimeout=[int]4000 #Create object for connecting to port on computer If ($computer.Contains(":")) { $tcpobject = new-Object system.Net.Sockets.TcpClient([System.Net.Sockets.AddressFamily]::InterNetworkv6) } else { $tcpobject = new-Object system.Net.Sockets.TcpClient } #Connect to remote machine's port $connect = $tcpobject.BeginConnect($computer,$Port,$null,$null) #Configure a timeout before quitting $wait = $connect.AsyncWaitHandle.WaitOne($TCPtimeout,$false) If (!$wait) { #Close connection Try {$tcpobject.Close()} Catch {} return $False } Else { Try {[void]$tcpobject.EndConnect($connect)} Catch {Try {$tcpobject.Close()} Catch {}} Try {$tcpobject.Close()} Catch {} return $True } } else { return $True } } Function ExecuteSQLCommandFromButton([string] $server,[string] $db, [string] $Command) { if (($chkSQLSaveInQueryWindow.Checked) -Or ($chkSQLSaveInQueryNoExecute.Checked)) { $tbxSQLQueryWindow.Text=$Command } if ($chkSQLSaveInQueryNoExecute.Checked) { $tab3.SelectedIndex=0 } else { ExecuteSQLCommand $server $db $Command $btnSQLExecute.Enabled=$False } } Function SQLServersList_DClick { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $cbxSQLSource.SelectedItem = [String]($TblServersList.DefaultView[$dgServersList.CurrentCell.RowIndex][0]) ChangedSelectSQLSource } Function SQLResult_DClick($TC=0) { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $OnTopWas = $Form.topmost $Form.topmost = $false $CellValue = [String]($global:SQLResultGrids[$TC].Item($global:SQLResultGrids[$TC].CurrentCell.ColumnIndex,$global:SQLResultGrids[$TC].CurrentCell.RowIndex)).Value #if ($chkSQLDebug.Checked) {write-host $CellValue.replace("><",">`r`n<")} if ($chkSQLDebug.Checked) {write-host $CellValue} if ($False) { Start-Process -FilePath "C:\Program Files\Internet Explorer\iexplore.exe" -wait -ArgumentList "$CellValue" } elseif ("$CellValue" -like "*<ShowPlanXML *</ShowPlanXML>*") { $CellValue=$CellValue.replace("><",">`r`n<") OpenTextBox $CellValue } else { OpenTextBox $CellValue } $Form.topmost = $OnTopWas } Function ExecuteSQLCommand([string] $server,[string] $db, [string] $Command) { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $TCStart=0 $PingPortResult=PingPort $server if ("$server" -eq "") { $server=$CurrentComputer } for ($TC=0;$TC -lt $global:SQLResultTabs.Count; $TC++) { $global:SQLResultGrids[$TC].DataSource = $null } for ($TC=$tab3.controls.Count-2;$TC -gt 0; $TC--) { $tab3.controls.Remove($global:SQLResultTabs[$TC]) } if ($PingPortResult) { foreach ($ResultTab in $global:SQLResultGrids) { $ResultTab.DataSource = $null #$ResultTab.Visible=$False } if (("$server").Trim() -ne "" -And "$server" -ne "." -And $global:SQLServerList -notcontains "$server") { $global:SQLServerList+="$server" [void] $cbxSQLSource.Items.Add("$server") } $global:SQLLastQuery=$Command $global:SQLCounter=[int]$tbxSQLAutoRefreshEvery.Text $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $DataSet = New-Object System.Data.DataSet if ($chkSQLWindowsAuthentication.Checked -eq $True -Or "$($tbxSQLUserName.Text)" -eq "" -Or "$($tbxSQLPassword.Text)" -eq "") { $SqlConnection.ConnectionString = "Server=$server;Database=$db;Integrated Security=True;Connection Timeout=$($tbxSQLConnectionTimeout.Text)" } else { $SqlConnection.ConnectionString = "Server=$server;Database=$db;User ID=$($tbxSQLUserName.Text);Password=$($tbxSQLPassword.Text);Connection Timeout=$($tbxSQLConnectionTimeout.Text)" } Try { $SqlConnection.open() } Catch { $TblSQLTableResult = New-Object System.Data.DataTable $TblSQLTableResult.TableName = "SQLTableResult" [void]$TblSQLTableResult.Columns.Add("ErrorMessage",[string]) $addrow = $TblSQLTableResult.NewRow() $addrow.("ErrorMessage")="Failed to connect to SQL Server $($server): $($Error[0])" $TblSQLTableResult.Rows.Add($addrow) $global:SQLResultGrids[0].DataSource = $TblSQLTableResult Try { $global:SQLResultGrids[$TC].Refresh() } Catch {} $tab3.SelectedIndex=1 return } $SqlCmd.Connection = $SqlConnection $SqlCmd.CommandText = $Command #$SqlCmd.CommandTimeout=$tbxSQLQueryTimeout.Text $SqlAdapter.SelectCommand = $SqlCmd StatusUpdate $tab.SelectedIndex "Executing Query on $server at $(get-date)" $null $null Try { $SqlAdapter.Fill($DataSet) | Out-Null } Catch { $TCStart=1 $TblSQLTableResult = New-Object System.Data.DataTable $TblSQLTableResult.TableName = "SQLTableResult" [void]$TblSQLTableResult.Columns.Add("ErrorMessage",[string]) $addrow = $TblSQLTableResult.NewRow() $addrow.("ErrorMessage")="Failed to run query on SQL Server $($server): $($Error[0])" $TblSQLTableResult.Rows.Add($addrow) $global:SQLResultGrids[0].DataSource = $TblSQLTableResult $global:SQLResultGrids[0].Refresh() Try {$SqlConnection.Close()} Catch {} $tab3.SelectedIndex=1 } $SqlConnection.Close() $FilterOn=("$($tbxSQLFilter.Text)").Trim() $Exclude=("$($tbxSQLExclude.Text)").Trim() $FilteredColumns=@() if (("$($tbxSQLColumns.Text)").Trim() -ne "") { $DoFilterColumns=$True Foreach ($Col in ($tbxSQLColumns.Text).split(',')) { $FilteredColumns+=$Col } } else { $DoFilterColumns=$False } if ("$FilterOn" -ne "" -Or "$Exclude" -ne "") { $Filtering=$True } else { $DoFilterColumns=$False $Filtering=$False } StatusUpdate $tab.SelectedIndex "Finished at $(get-date)" $null $null for ($TC=$TCStart;$TC -lt $DataSet.Tables.Count+$TCStart; $TC++) { if ($global:SQLResultTabs.count -le $TC) { $global:SQLResultTabs+= new-object System.Windows.Forms.tabpage $global:SQLResultTabs[$TC].Text = "Result$($TC+1)" $global:SQLResultTabs[$TC].Size = New-object System.Drawing.Size(950, 440)#950 $global:SQLResultTabs[$TC].TabIndex = $tab3.TabCount $tab3.controls.add($global:SQLResultTabs[$TC]) if ($global:SQLResultGrids.count -gt $TC) { $global:SQLResultTabs[$TC].Controls.Add($global:SQLResultGrids[$TC]) } } elseif ($tab3.controls.count-1 -le $TC) { $tab3.controls.add($global:SQLResultTabs[$TC]) } if ($global:SQLResultGrids.count -le $TC) { $global:SQLResultGrids+=new-object System.windows.forms.DataGridView $global:SQLResultGrids[$TC].Location = new-object System.Drawing.Size(0,0) $global:SQLResultGrids[$TC].size = new-object System.Drawing.Size(($tab.Width-16),($tab.Height-$tab3.Top-52-$global:SQLResultGrids[$TC].Top))#974,415 $global:SQLResultGrids[$TC].DataBindings.DefaultDataSourceUpdateMode = 0 $global:SQLResultGrids[$TC].AutoSizeColumnsMode = "AllCells" $global:SQLResultGrids[$TC].Add_CellDoubleClick({SQLResult_DClick $TC}) $global:SQLResultGrids[$TC].AutoGenerateColumns = $True $global:SQLResultGrids[$TC].ReadOnly = $True $global:SQLResultGrids[$TC].AllowUserToDeleteRows = $False $global:SQLResultGrids[$TC].AllowUserToAddRows = $False $global:SQLResultGrids[$TC].AllowUserToOrderColumns = $True $global:SQLResultTabs[$TC].Controls.Add($global:SQLResultGrids[$TC]) } if ($TC -eq 0) { StatusUpdate $tab.SelectedIndex "($($DataSet.Tables[$TC-$TCStart].Rows.count) rows affected) ($(get-date)) ($server,$db)" $null $null } else { } if (("$($tbxSQLFilter.Text)").Trim() -eq "" -And ("$($tbxSQLExclude.Text)").Trim() -eq "" -And !($DataSet.Tables[$TC-$TCStart].Columns |Where {$_.DataType.Name -eq "Byte[]"}) -And !("$($DataSet.Tables[$TC-$TCStart].get_rows())" -ne "" -And ([int]$tbxSQLTop.Text) -gt 0)) { $global:SQLResultGrids[$TC].DataSource = $DataSet.Tables[$TC-$TCStart] #.get_rows() } else { $TblSQLTableResult = New-Object System.Data.DataTable $TblSQLTableResult.TableName = "SQLTableResult" $FoundAColumn=$False foreach ($Col in $DataSet.Tables[$TC-$TCStart].Columns) { if ($DoFilterColumns) { if ($FilteredColumns -contains $Col.ColumnName) { $FoundAColumn=$True } } if ($Col.DataType.Name -eq "Byte[]") { [void]$TblSQLTableResult.Columns.Add($Col.ColumnName,[string])#,([string]) } else { [void]$TblSQLTableResult.Columns.Add($Col.ColumnName,$Col.DataType)#,([string]) } } $DoFilterColumns=$FoundAColumn if ("$($DataSet.Tables[$TC-$TCStart].get_rows())" -ne "" -And ([int]$tbxSQLTop.Text) -gt 0) { $DataSetList=$DataSet.Tables[$TC-$TCStart].get_rows() |select -First ([int]$tbxSQLTop.Text) } else { $DataSetList=$DataSet.Tables[$TC-$TCStart].get_rows() } if ($chkSQLExactMatch.Checked) { $WildCard="" } else { $WildCard="*" } foreach ($row in $DataSetList) { $addrow = $TblSQLTableResult.NewRow() $DoAddRow=$True if ($Filtering) { $FoundFilter=$False if ("$FilterOn" -eq "") { $FoundFilter=$True } } foreach ($Col in $DataSet.Tables[$TC-$TCStart].Columns) { if ($Col.DataType.Name -eq "Byte[]") { if ("$($row.($Col.ColumnName))" -eq "") { $addrow.($Col.ColumnName)=$null } else { $addrow.($Col.ColumnName)="0x$(($row.($Col.ColumnName) |ForEach-Object ToString X2) -join '')" } } else { $addrow.($Col.ColumnName)=$row.($Col.ColumnName) } if ($Filtering) { if ($chkSQLCaseSensitive.Checked) { if ((!($DoFilterColumns) -Or $FilteredColumns -contains $Col.ColumnName) -And !($FoundFilter) -And "$($addrow.($Col.ColumnName))" -clike "$WildCard$($FilterOn)$WildCard") { $FoundFilter=$True } if (($DoAddRow) -And (!($DoFilterColumns) -Or $FilteredColumns -contains $Col.ColumnName) -And "$Exclude" -ne "" -And "$($addrow.($Col.ColumnName))" -clike "$WildCard$($Exclude)$WildCard") { $DoAddRow=$False } } else { if ((!($DoFilterColumns) -Or $FilteredColumns -contains $Col.ColumnName) -And !($FoundFilter) -And "$($addrow.($Col.ColumnName))" -like "$WildCard$($FilterOn)$WildCard") { $FoundFilter=$True } if (($DoAddRow) -And (!($DoFilterColumns) -Or $FilteredColumns -contains $Col.ColumnName) -And "$Exclude" -ne "" -And "$($addrow.($Col.ColumnName))" -like "$WildCard$($Exclude)$WildCard") { $DoAddRow=$False } } } } if ($Filtering) { if (!($FoundFilter)) { $DoAddRow=$False } } if ($DoAddRow) { $TblSQLTableResult.Rows.Add($addrow) } } $global:SQLResultGrids[$TC].DataSource = $TblSQLTableResult } $global:SQLResultGrids[$TC].Refresh() $tab3.SelectedIndex=($TC+1) #Needed for initial row count Try { $global:SQLResultGrids[$TC].rows.HeaderCell |foreach {$_.Value="$($_.RowIndex+1)";if ("$($_.Value)" -eq "") {$_.Value="$($_.RowIndex+1)"}} } Catch {} } } else { $TblSQLTableResult = New-Object System.Data.DataTable $TblSQLTableResult.TableName = "SQLTableResult" [void]$TblSQLTableResult.Columns.Add("ErrorMessage",[string]) $addrow = $TblSQLTableResult.NewRow() $addrow.("ErrorMessage")="Failed to connect to Port 1433" $TblSQLTableResult.Rows.Add($addrow) $global:SQLResultGrids[0].DataSource = $TblSQLTableResult $global:SQLResultGrids[0].Refresh() } $tab3.SelectedIndex=1 } Function ExecuteSQLCommandSimple([string] $server,[string] $db, [string] $Command) { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $PingPortResult=PingPort $server if ("$server" -eq "") { $server=$CurrentComputer } if ($PingPortResult) { if (("$server").Trim() -ne "" -And "$server" -ne "." -And $global:SQLServerList -notcontains "$server") { $global:SQLServerList+="$server" [void] $cbxSQLSource.Items.Add("$server") } $global:SQLLastQuery=$Command $global:SQLCounter=[int]$tbxSQLAutoRefreshEvery.Text $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $DataSet = New-Object System.Data.DataSet if ($chkSQLWindowsAuthentication.Checked -eq $True -Or "$($tbxSQLUserName.Text)" -eq "" -Or "$($tbxSQLPassword.Text)" -eq "") { $SqlConnection.ConnectionString = "Server=$server;Database=$db;Integrated Security=True;Connection Timeout=$($tbxSQLConnectionTimeout.Text)" } else { $SqlConnection.ConnectionString = "Server=$server;Database=$db;User ID=$($tbxSQLUserName.Text);Password=$($tbxSQLPassword.Text);Connection Timeout=$($tbxSQLConnectionTimeout.Text)" } Try { $SqlConnection.open() } Catch { $TblSQLTableResult = New-Object System.Data.DataTable $TblSQLTableResult.TableName = "SQLTableResult" [void]$TblSQLTableResult.Columns.Add("ErrorMessage",[string]) $addrow = $TblSQLTableResult.NewRow() $addrow.("ErrorMessage")="Failed to connect to SQL Server $($server): $($Error[0])" $TblSQLTableResult.Rows.Add($addrow) $global:SQLResultGrids[0].DataSource = $TblSQLTableResult $global:SQLResultGrids[0].Refresh() $tab3.SelectedIndex=1 return $null } $SqlCmd.Connection = $SqlConnection $SqlCmd.CommandText = $Command $SqlAdapter.SelectCommand = $SqlCmd StatusUpdate $tab.SelectedIndex "Executing Query on $server at $(get-date)" $null $null Try { $SqlAdapter.Fill($DataSet) | Out-Null } Catch { $TblSQLTableResult = New-Object System.Data.DataTable $TblSQLTableResult.TableName = "SQLTableResult" [void]$TblSQLTableResult.Columns.Add("ErrorMessage",[string]) $addrow = $TblSQLTableResult.NewRow() $addrow.("ErrorMessage")="Failed to run query on SQL Server $($server): $($Error[0])" $TblSQLTableResult.Rows.Add($addrow) $global:SQLResultGrids[0].DataSource = $TblSQLTableResult $global:SQLResultGrids[0].Refresh() Try {$SqlConnection.Close()} Catch {} $tab3.SelectedIndex=1 return $null } $SqlConnection.Close() StatusUpdate $tab.SelectedIndex "Finished at $(get-date)" $null $null if($DataSet.Tables[0]) { return $DataSet.Tables[0].get_rows() } } else { $TblSQLTableResult = New-Object System.Data.DataTable $TblSQLTableResult.TableName = "SQLTableResult" [void]$TblSQLTableResult.Columns.Add("ErrorMessage",[string]) $addrow = $TblSQLTableResult.NewRow() $addrow.("ErrorMessage")="Failed to connect to Port 1433" $TblSQLTableResult.Rows.Add($addrow) $global:SQLResultGrids[0].DataSource = $TblSQLTableResult $global:SQLResultGrids[0].Refresh() return $null } } Function ButtonShowDropdownList { if ($btnSQLShowServerList.Text -eq "Show List") { $tab3.controls.Add($tabSQLDropdownList) $btnSQLShowServerList.Text="Remove List" $tab3.SelectedIndex=$tab3.controls.Count-1 } else { $tab3.controls.Remove($tabSQLDropdownList) $btnSQLShowServerList.Text="Show List" $tab3.SelectedIndex=0 } } Function ClickAlwaysOnTop { if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} $form.topmost=$chkSQLAlwaysOnTop.Checked if ($chkSQLAlwaysOnTop.Checked) { StatusUpdate $tab.SelectedIndex $null "Checked Always On Top" $null } else { StatusUpdate $tab.SelectedIndex $null "Unchecked Always On Top" $null } } #################### SQL Tab Functions ################################################################### Function FormSizeChanged { # if ($chkSQLDebug.Checked) {write-host "Function $($MyInvocation.Line)"} if ($Form.Width -gt 10) { $tab.Width=$Form.Width-20 } if ($Form.Height -gt 100) { $tab.Height=$Form.Height-65 if ($tab.Height -gt (61+$tab3.Top)) { $tab3.Height=($tab.Height-$tab3.Top-26) } } if ($Form.Width -gt 360) { $tab31.Width=$tab.Width-358 } if ($Form.Width -gt 10) { if ($tab.Width -gt 20)# -And $tab3.Width -ne $tab.Width-8 { $tab3.Width=$tab.Width-8 } $dgServersList.Width=$Form.Width-36 $tbxSQLQueryWindow.Width=$Form.Width-36 for ($TC=0;$TC -lt $global:SQLResultGrids.Count; $TC++) { $global:SQLResultGrids[$TC].Width=$Form.Width-36 } } if ($Form.Height -gt 40) { if ($Form.Height -gt 100) { $dgServersList.Height=$Form.Height-95-$dgServersList.Top $tbxSQLQueryWindow.Height=$Form.Height-95-$tbxSQLQueryWindow.Top for ($TC=0;$TC -lt $global:SQLResultGrids.Count; $TC++) { $global:SQLResultGrids[$TC].Height=$Form.Height-95-$global:SQLResultGrids[$TC].Top } if ($Form.Height -gt ($tab3.Top+16)) { $tbxSQLQueryWindow.Height=$Form.Height-$tab3.Top-117-$tbxSQLQueryWindow.Top for ($TC=0;$TC -lt $global:SQLResultGrids.Count; $TC++) { $global:SQLResultGrids[$TC].Height=$Form.Height-$tab3.Top-117-($global:SQLResultGrids[$TC].Top) } } } } } ############################################################################### ### ### End Functions ### ### ############################################################################### ############################################################################################################################################################## ############################################################################################################################################################## ############################################################################################################################################################## ############################################################################### ### ### Create GUI ### ### ############################################################################### $form = new-object System.Windows.Forms.form ##################################################################### ### Create Tabs ##################################################################### $tab = new-object System.Windows.Forms.tabcontrol $tab.Location = New-object System.Drawing.Point(1, 1) $tab.Size = New-object System.Drawing.Size(990, 570) $tab.SelectedIndex = 0 $tab.TabIndex = 0 $tab.Add_SelectedIndexChanged({ClickChangeTab}) $tab3 = new-object System.Windows.Forms.tabcontrol $tab3.Location = New-object System.Drawing.Point(0, 119)#119 $tab3.Size = New-object System.Drawing.Size(($tab.Width-8), ($tab.Height-$tab3.Top-26)) $tab3.SelectedIndex = 0 $tab3.TabIndex = 0 #$tab3.Add_SelectedIndexChanged({ClickChangeTab}) $tab31 = new-object System.Windows.Forms.tabcontrol $tab31.Location = New-object System.Drawing.Point(350, 0)#119 $tab31.Size = New-object System.Drawing.Size(($tab.Width-358), (120)) $tab31.SelectedIndex = 0 $tab31.TabIndex = 0 #$tab31.Add_SelectedIndexChanged({ClickChangeTab}) ##################################################################### ### Tab Drawing: SQL ##################################################################### ##### ##### Tabs: ##### $tabSQL = new-object System.Windows.Forms.tabpage ##### Add Tab: $tabSQL.Text = "SQL" $tabSQL.Size = New-object System.Drawing.Size(950, 450) $tabSQL.TabIndex = $tab.TabCount $tab.controls.add($tabSQL) $tabSQLQuery = new-object System.Windows.Forms.tabpage $tabSQLQuery.Text = "Query" $tabSQLQuery.Size = New-object System.Drawing.Size(950, 440)#950 $tabSQLQuery.TabIndex = $tab3.TabCount $tab3.controls.add($tabSQLQuery) $tab3.Add_SelectedIndexChanged({if ($tab3.SelectedIndex -eq 0) {$btnSQLExecute.Enabled=$True}}) $tabSQLDropdownList = new-object System.Windows.Forms.tabpage $tabSQLDropdownList.Text = "DropdownList" $tabSQLDropdownList.Size = New-object System.Drawing.Size(950, 440)#950 $tabSQLDropdownList.TabIndex = $tab3.TabCount #Add Later: #$tab3.controls.add($tabSQLDropdownList) $global:SQLResultTabs+= new-object System.Windows.Forms.tabpage $global:SQLResultTabs[0].Text = "Result" $global:SQLResultTabs[0].Size = New-object System.Drawing.Size(950, 440)#950 $global:SQLResultTabs[0].TabIndex = $tab3.TabCount $tab3.controls.add($global:SQLResultTabs[0]) $tabSQLSettings = new-object System.Windows.Forms.tabpage $tabSQLSettings.Text = "Settings" $tabSQLSettings.Size = New-object System.Drawing.Size(950, 440)#950 $tabSQLSettings.TabIndex = $tab31.TabCount $tab31.controls.add($tabSQLSettings) $tabSQLCommon = new-object System.Windows.Forms.tabpage $tabSQLCommon.Text = "Common" $tabSQLCommon.Size = New-object System.Drawing.Size(950, 440)#950 $tabSQLCommon.TabIndex = $tab31.TabCount $tab31.controls.add($tabSQLCommon) $tab31.SelectedIndex=1 $tabSQLSAP = new-object System.Windows.Forms.tabpage $tabSQLSAP.Text = "SAP" $tabSQLSAP.Size = New-object System.Drawing.Size(950, 440)#950 $tabSQLSAP.TabIndex = $tab31.TabCount $tab31.controls.add($tabSQLSAP) $tabSQLSamples = new-object System.Windows.Forms.tabpage $tabSQLSamples.Text = "Samples" $tabSQLSamples.Size = New-object System.Drawing.Size(950, 440)#950 $tabSQLSamples.TabIndex = $tab31.TabCount $tab31.controls.add($tabSQLSamples) $tabSQLSecurity = new-object System.Windows.Forms.tabpage $tabSQLSecurity.Text = "Security" $tabSQLSecurity.Size = New-object System.Drawing.Size(950, 440)#950 $tabSQLSecurity.TabIndex = $tab31.TabCount $tab31.controls.add($tabSQLSecurity) $tabSQLPartition = new-object System.Windows.Forms.tabpage $tabSQLPartition.Text = "Partition" $tabSQLPartition.Size = New-object System.Drawing.Size(950, 440)#950 $tabSQLPartition.TabIndex = $tab31.TabCount $tab31.controls.add($tabSQLPartition) $tbxSQLQueryWindow = new-object System.windows.forms.TextBox $tbxSQLQueryWindow.Location = New-Object System.Drawing.Size(0,0) $tbxSQLQueryWindow.Size = New-Object System.Drawing.Size($tab.Width-16),($tab.Height-$tab3.Top-52-$tbxSQLQueryWindow.Top) #(578,353) $tbxSQLQueryWindow.Multiline=$true #$tbxSQLQueryWindow.ScrollBars="Vertical" $tbxSQLQueryWindow.WordWrap=$False $tbxSQLQueryWindow.ScrollBars="Both" $tbxSQLQueryWindow.Text = $Query $tabSQLQuery.Controls.Add($tbxSQLQueryWindow) $tabSQL.Controls.Add($tab3) $tabSQL.Controls.Add($tab31) ##### ##### Main Tab: ##### $lblSQLServer = New-Object System.Windows.Forms.Label $lblSQLServer.Location = New-Object System.Drawing.Size(15,02) $lblSQLServer.Size = New-Object System.Drawing.Size(65,15) $lblSQLServer.Text = "SQLServer:" $tabSQL.Controls.Add($lblSQLServer) $tbxSQLServer = New-Object System.Windows.Forms.TextBox $tbxSQLServer.Location = New-Object System.Drawing.Size(82,0) $tbxSQLServer.Size = New-Object System.Drawing.Size(150,10) $tbxSQLServer.Height = 30 $tbxSQLServer.Text="$Server" $tbxSQLServer.Add_TextChanged({ if ($global:LastSQLServer -ne $tbxSQLServer.Text -And !($global:LastSQLServerChanged)) { $cbxSQLDatabase.Items.Clear() [void] $cbxSQLDatabase.Items.Add("master") [void] $cbxSQLDatabase.Items.Add("msdb") [void] $cbxSQLDatabase.Items.Add("model") [void] $cbxSQLDatabase.Items.Add("tempdb") $global:LastSQLServerChanged=$True } }) $tbxSQLServer.Add_MouseHover({$ToolTip.SetToolTip($this,"SQLServer(s). Comma delimited. Ex: Server1,Server2,Server3")}) $tabSQL.Controls.Add($tbxSQLServer) $cbxSQLSource = New-Object System.Windows.Forms.ComboBox $cbxSQLSource.Location = New-Object System.Drawing.Size(235,0) $cbxSQLSource.Size = New-Object System.Drawing.Size(115,10) $cbxSQLSource.Height = 30 $cbxSQLSource.Sorted = $True $cbxSQLSource.Enabled=$True $cbxSQLSource.Add_SelectedValueChanged({ChangedSelectSQLSource}) $cbxSQLSource.Add_MouseHover({$ToolTip.SetToolTip($this,"Server List. Use -File to load servers")}) $tabSQL.Controls.Add($cbxSQLSource) $lblSQLDatabase = New-Object System.Windows.Forms.Label $lblSQLDatabase.Location = New-Object System.Drawing.Size(15,22) $lblSQLDatabase.Size = New-Object System.Drawing.Size(65,15) $lblSQLDatabase.Text = "Database:" $tabSQL.Controls.Add($lblSQLDatabase) $cbxSQLDatabase = New-Object System.Windows.Forms.ComboBox $cbxSQLDatabase.Location = New-Object System.Drawing.Size(82,20) $cbxSQLDatabase.Size = New-Object System.Drawing.Size(150,10) $cbxSQLDatabase.Height = 30 $cbxSQLDatabase.Sorted = $True $cbxSQLDatabase.Text = "" #$cbxSQLDatabase.DropDownStyle="DropDownList" $cbxSQLDatabase.Enabled=$True $cbxSQLDatabase.Add_SelectedValueChanged({ $cbxSQLDatabase.Text = $cbxSQLDatabase.SelectedItem }) $cbxSQLDatabase.Add_MouseHover({$ToolTip.SetToolTip($this,"SQL Database(s). Comma delimited. Ex: master,msdb")}) $tabSQL.Controls.Add($cbxSQLDatabase) [void] $cbxSQLDatabase.Items.Add("master") [void] $cbxSQLDatabase.Items.Add("msdb") [void] $cbxSQLDatabase.Items.Add("model") [void] $cbxSQLDatabase.Items.Add("tempdb") $lblSQLTop = New-Object System.Windows.Forms.Label $lblSQLTop.Location = New-Object System.Drawing.Size(275,24)#235,24 $lblSQLTop.Size = New-Object System.Drawing.Size(25,15) $lblSQLTop.Text = "Top:" $tabSQL.Controls.Add($lblSQLTop) $btnSQLGetDatabase = new-object System.Windows.Forms.Button $btnSQLGetDatabase.Location = new-object System.Drawing.Size(235,21) $btnSQLGetDatabase.Size = new-object System.Drawing.Size(40,18) $btnSQLGetDatabase.Text = "Get" $btnSQLGetDatabase.Enabled = $True $btnSQLGetDatabase.Add_Click({ $global:LastSQLServer=$tbxSQLServer.Text $global:LastSQLServerChanged=$False $GetDatabaseResult=ExecuteSQLCommandSimple $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted select name from master.dbo.sysdatabases where databasepropertyex(name,'Status')='ONLINE' and databasepropertyex(name,'Updateability')='READ_WRITE'" if ($GetDatabaseResult) { $cbxSQLDatabase.Items.Clear() foreach ($dbname in $GetDatabaseResult) { [void] $cbxSQLDatabase.Items.Add($dbname.name) } } }) $btnSQLGetDatabase.Add_MouseHover({$ToolTip.SetToolTip($this,"Connect to current SQL Server to load databases into list")}) $tabSQL.Controls.Add($btnSQLGetDatabase) $tbxSQLTop = New-Object System.Windows.Forms.TextBox $tbxSQLTop.Location = New-Object System.Drawing.Size(300,20)#260,20 $tbxSQLTop.Size = New-Object System.Drawing.Size(50,10) $tbxSQLTop.Height = 30 $tbxSQLTop.Text="" #$tbxSQLTop.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLTop.Add_LostFocus({ChangedFocusDeployFunctional}) $tbxSQLTop.Add_MouseHover({$ToolTip.SetToolTip($this,"Top number of rows. Ex: 10000")}) $tabSQL.Controls.Add($tbxSQLTop) $chkSQLAutoRefresh = New-Object System.Windows.Forms.CheckBox $chkSQLAutoRefresh.Location = New-Object System.Drawing.Size(10,40) $chkSQLAutoRefresh.size = new-object System.Drawing.Size(125,20) $chkSQLAutoRefresh.Text = "Auto-Query Every" $chkSQLAutoRefresh.Checked = $False $chkSQLAutoRefresh.Add_Click({if ($chkSQLAutoRefresh.Checked) {$global:SQLCounter=[int]$tbxSQLAutoRefreshEvery.Text} else {$lblSQLAutoRefreshCountDown.Text="()"}}) $chkSQLAutoRefresh.Add_MouseHover({$ToolTip.SetToolTip($this,"Re-query last query every # number of seconds")}) $tabSQL.Controls.Add($chkSQLAutoRefresh) $chkSQLAutoRefresh.Checked = $Loop $tbxSQLAutoRefreshEvery = New-Object System.Windows.Forms.TextBox $tbxSQLAutoRefreshEvery.Location = New-Object System.Drawing.Size(10,60) $tbxSQLAutoRefreshEvery.Size = New-Object System.Drawing.Size(50,10) $tbxSQLAutoRefreshEvery.Height = 30 $tbxSQLAutoRefreshEvery.Text="$LoopSec" #$tbxSQLAutoRefreshEvery.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLAutoRefreshEvery.Add_LostFocus({ChangedFocusDeployFunctional}) $tbxSQLAutoRefreshEvery.Add_MouseHover({$ToolTip.SetToolTip($this,"Re-query these number of seconds")}) $tabSQL.Controls.Add($tbxSQLAutoRefreshEvery) $lblSQLAutoRefreshSec = New-Object System.Windows.Forms.Label $lblSQLAutoRefreshSec.Location = New-Object System.Drawing.Size(60,63) $lblSQLAutoRefreshSec.Size = New-Object System.Drawing.Size(25,15) $lblSQLAutoRefreshSec.Text = "Sec" $tabSQL.Controls.Add($lblSQLAutoRefreshSec) $lblSQLAutoRefreshCountDown = New-Object System.Windows.Forms.Label $lblSQLAutoRefreshCountDown.Location = New-Object System.Drawing.Size(85,63) $lblSQLAutoRefreshCountDown.Size = New-Object System.Drawing.Size(60,15) $lblSQLAutoRefreshCountDown.Text = "()" $tabSQL.Controls.Add($lblSQLAutoRefreshCountDown) $btnSQLExecute = new-object System.Windows.Forms.Button $btnSQLExecute.Location = new-object System.Drawing.Size(20,85) $btnSQLExecute.Size = new-object System.Drawing.Size(120,23) $btnSQLExecute.Text = "Execute" $btnSQLExecute.Enabled = $True $btnSQLExecute.Add_Click({ExecuteSQLCommand $tbxSQLServer.Text $cbxSQLDatabase.Text $tbxSQLQueryWindow.Text}) $btnSQLExecute.Add_MouseHover({$ToolTip.SetToolTip($this,"Execute what is in the Query Window")}) $tabSQL.Controls.Add($btnSQLExecute) $lblSQLFilter = New-Object System.Windows.Forms.Label $lblSQLFilter.Location = New-Object System.Drawing.Size(200,43) $lblSQLFilter.Size = New-Object System.Drawing.Size(50,15) $lblSQLFilter.Text = "FilterOn:" $tabSQL.Controls.Add($lblSQLFilter) $tbxSQLFilter = New-Object System.Windows.Forms.TextBox $tbxSQLFilter.Location = New-Object System.Drawing.Size(250,40) $tbxSQLFilter.Size = New-Object System.Drawing.Size(100,10) $tbxSQLFilter.Height = 30 $tbxSQLFilter.Text="" $tbxSQLFilter.Add_MouseHover({$ToolTip.SetToolTip($this,"Only show rows with this when executing query")}) $tabSQL.Controls.Add($tbxSQLFilter) $lblSQLExclude = New-Object System.Windows.Forms.Label $lblSQLExclude.Location = New-Object System.Drawing.Size(200,63) $lblSQLExclude.Size = New-Object System.Drawing.Size(50,15) $lblSQLExclude.Text = "Exclude:" $tabSQL.Controls.Add($lblSQLExclude) $tbxSQLExclude = New-Object System.Windows.Forms.TextBox $tbxSQLExclude.Location = New-Object System.Drawing.Size(250,60) $tbxSQLExclude.Size = New-Object System.Drawing.Size(100,10) $tbxSQLExclude.Height = 30 $tbxSQLExclude.Text="" $tbxSQLExclude.Add_MouseHover({$ToolTip.SetToolTip($this,"Do not show rows with this when executing query")}) $tabSQL.Controls.Add($tbxSQLExclude) $lblSQLColumns = New-Object System.Windows.Forms.Label $lblSQLColumns.Location = New-Object System.Drawing.Size(200,83) $lblSQLColumns.Size = New-Object System.Drawing.Size(50,15) $lblSQLColumns.Text = "Columns:" $tabSQL.Controls.Add($lblSQLColumns) $tbxSQLColumns = New-Object System.Windows.Forms.TextBox $tbxSQLColumns.Location = New-Object System.Drawing.Size(250,80) $tbxSQLColumns.Size = New-Object System.Drawing.Size(100,10) $tbxSQLColumns.Height = 30 $tbxSQLColumns.Text="" $tbxSQLColumns.Add_MouseHover({$ToolTip.SetToolTip($this,"Only apply filters to columns. Comma delimited. Ex: Column1,Column2")}) $tabSQL.Controls.Add($tbxSQLColumns) $chkSQLCaseSensitive = New-Object System.Windows.Forms.CheckBox $chkSQLCaseSensitive.Location = New-Object System.Drawing.Size(270,100) $chkSQLCaseSensitive.size = new-object System.Drawing.Size(80,20) $chkSQLCaseSensitive.Text = "Case-Sens" $chkSQLCaseSensitive.Checked = $False $chkSQLCaseSensitive.Add_MouseHover({$ToolTip.SetToolTip($this,"Case sensitive when filtering")}) $tabSQL.Controls.Add($chkSQLCaseSensitive) $chkSQLExactMatch = New-Object System.Windows.Forms.CheckBox $chkSQLExactMatch.Location = New-Object System.Drawing.Size(200,100) $chkSQLExactMatch.size = new-object System.Drawing.Size(70,20) $chkSQLExactMatch.Text = "Exact" $chkSQLExactMatch.Checked = $False $chkSQLExactMatch.Add_MouseHover({$ToolTip.SetToolTip($this,"Only exact match of whole cell (no partial) when filtering")}) $tabSQL.Controls.Add($chkSQLExactMatch) ##### ##### Tab: Settings ##### $chkSQLSaveInQueryWindow = New-Object System.Windows.Forms.CheckBox $chkSQLSaveInQueryWindow.Location = New-Object System.Drawing.Size(0,0)#170,40 $chkSQLSaveInQueryWindow.size = new-object System.Drawing.Size(150,20) $chkSQLSaveInQueryWindow.Text = "Btn->Query + Exec" $chkSQLSaveInQueryWindow.Checked = $ShowQuery $chkSQLSaveInQueryWindow.Add_Click({$chkSQLSaveInQueryNoExecute.Checked=$False}) $chkSQLSaveInQueryWindow.Add_MouseHover({$ToolTip.SetToolTip($this,"Show query from button in Query Window and Execute query")}) $tabSQLSettings.Controls.Add($chkSQLSaveInQueryWindow) $chkSQLSaveInQueryNoExecute = New-Object System.Windows.Forms.CheckBox $chkSQLSaveInQueryNoExecute.Location = New-Object System.Drawing.Size(0,20)#170,40 $chkSQLSaveInQueryNoExecute.size = new-object System.Drawing.Size(150,20) $chkSQLSaveInQueryNoExecute.Text = "Btn->Query No Exec" $chkSQLSaveInQueryNoExecute.Checked = $False $chkSQLSaveInQueryNoExecute.Add_Click({$chkSQLSaveInQueryWindow.Checked=$False}) $chkSQLSaveInQueryNoExecute.Add_MouseHover({$ToolTip.SetToolTip($this,"Show query from button in Query Window, but DO NOT Execute query")}) $tabSQLSettings.Controls.Add($chkSQLSaveInQueryNoExecute) $chkSQLSaveInQueryNoExecute.Checked = $ShowQueryNoExec $chkSQLShowDropDown = New-Object System.Windows.Forms.CheckBox $chkSQLShowDropDown.Location = New-Object System.Drawing.Size(0,40) $chkSQLShowDropDown.size = new-object System.Drawing.Size(150,20) $chkSQLShowDropDown.Text = "Show Servers List Drop" $chkSQLShowDropDown.Checked = $True $chkSQLShowDropDown.Add_MouseHover({$ToolTip.SetToolTip($this,"When there is a server list, show drop down")}) $tabSQLSettings.Controls.Add($chkSQLShowDropDown) $chkSQLAddDropdownDB = New-Object System.Windows.Forms.CheckBox $chkSQLAddDropdownDB.Location = New-Object System.Drawing.Size(0,60) $chkSQLAddDropdownDB.size = new-object System.Drawing.Size(120,20) $chkSQLAddDropdownDB.Text = "Dropdown Fill DB" $chkSQLAddDropdownDB.Checked = $True $chkSQLAddDropdownDB.Add_MouseHover({$ToolTip.SetToolTip($this,"Use Database when picking server from server list dropdown")}) $tabSQLSettings.Controls.Add($chkSQLAddDropdownDB) $btnSQLShowServerList = new-object System.Windows.Forms.Button $btnSQLShowServerList.Location = new-object System.Drawing.Size(125,65) $btnSQLShowServerList.Size = new-object System.Drawing.Size(80,23) $btnSQLShowServerList.Text = "Show List" $btnSQLShowServerList.Enabled = $True $btnSQLShowServerList.Add_Click({ButtonShowDropdownList}) $btnSQLShowServerList.Add_MouseHover({$ToolTip.SetToolTip($this,"From -File param. Show server list details used in drop down. Ex: Name, Server, Database")}) $tabSQLSettings.Controls.Add($btnSQLShowServerList) $lblSQLConnectionTimeout = New-Object System.Windows.Forms.Label $lblSQLConnectionTimeout.Location = New-Object System.Drawing.Size(480,02)#750,62 $lblSQLConnectionTimeout.Size = New-Object System.Drawing.Size(80,15) $lblSQLConnectionTimeout.Text = "ConTimeout:" $tabSQLSettings.Controls.Add($lblSQLConnectionTimeout) $tbxSQLConnectionTimeout = New-Object System.Windows.Forms.TextBox $tbxSQLConnectionTimeout.Location = New-Object System.Drawing.Size(560,0)#850,60 $tbxSQLConnectionTimeout.Size = New-Object System.Drawing.Size(50,10) $tbxSQLConnectionTimeout.Height = 30 $tbxSQLConnectionTimeout.Text="30" $tbxSQLConnectionTimeout.Add_MouseHover({$ToolTip.SetToolTip($this,"Connection time-out in seconds")}) $tabSQLSettings.Controls.Add($tbxSQLConnectionTimeout) $lblSQLQueryTimeout = New-Object System.Windows.Forms.Label $lblSQLQueryTimeout.Location = New-Object System.Drawing.Size(480,22)#20,42 $lblSQLQueryTimeout.Size = New-Object System.Drawing.Size(80,15) $lblSQLQueryTimeout.Text = "QryTimeout:" $tabSQLSettings.Controls.Add($lblSQLQueryTimeout) $tbxSQLQueryTimeout = New-Object System.Windows.Forms.TextBox $tbxSQLQueryTimeout.Location = New-Object System.Drawing.Size(560,20)#120,40 $tbxSQLQueryTimeout.Size = New-Object System.Drawing.Size(50,10) $tbxSQLQueryTimeout.Height = 30 $tbxSQLQueryTimeout.Text="0" $tbxSQLQueryTimeout.Add_MouseHover({$ToolTip.SetToolTip($this,"Query time-out in seconds. Zero means no timeout")}) $tabSQLSettings.Controls.Add($tbxSQLQueryTimeout) $chkSQLWindowsAuthentication = New-Object System.Windows.Forms.CheckBox $chkSQLWindowsAuthentication.Location = New-Object System.Drawing.Size(150,0) $chkSQLWindowsAuthentication.size = new-object System.Drawing.Size(145,20) $chkSQLWindowsAuthentication.Text = "Windows Authentication" $chkSQLWindowsAuthentication.Checked = $True $chkSQLWindowsAuthentication.Add_Click({$tbxSQLUserName.Enabled=!($chkSQLWindowsAuthentication.Checked);$tbxSQLPassword.Enabled=!($chkSQLWindowsAuthentication.Checked)}) $chkSQLWindowsAuthentication.Add_MouseHover({$ToolTip.SetToolTip($this,"Current Windows User to Authenticate with SQL")}) $tabSQLSettings.Controls.Add($chkSQLWindowsAuthentication) $lblSQLUserName = New-Object System.Windows.Forms.Label $lblSQLUserName.Location = New-Object System.Drawing.Size(150,22) $lblSQLUserName.Size = New-Object System.Drawing.Size(62,15) $lblSQLUserName.Text = "UserName:" $tabSQLSettings.Controls.Add($lblSQLUserName) $lblSQLPassword = New-Object System.Windows.Forms.Label $lblSQLPassword.Location = New-Object System.Drawing.Size(150,42) $lblSQLPassword.Size = New-Object System.Drawing.Size(62,15) $lblSQLPassword.Text = "Password:" $tabSQLSettings.Controls.Add($lblSQLPassword) $tbxSQLUserName = New-Object System.Windows.Forms.TextBox $tbxSQLUserName.Location = New-Object System.Drawing.Size(215,20) $tbxSQLUserName.Size = New-Object System.Drawing.Size(135,10) $tbxSQLUserName.Height = 30 $tbxSQLUserName.Text="$UserName" $tbxSQLUserName.Enabled=$False $tbxSQLUserName.Add_MouseHover({$ToolTip.SetToolTip($this,"SQL Standard User to Authenticate with SQL")}) $tabSQLSettings.Controls.Add($tbxSQLUserName) $tbxSQLPassword = New-Object System.Windows.Forms.MaskedTextBox $tbxSQLPassword.Location = New-Object System.Drawing.Size(215,40) $tbxSQLPassword.Size = New-Object System.Drawing.Size(135,10) $tbxSQLPassword.PasswordChar="*" $tbxSQLPassword.Height = 30 $tbxSQLPassword.Text="$Password" $tbxSQLPassword.Enabled=$False $tbxSQLPassword.Add_MouseHover({$ToolTip.SetToolTip($this,"Password of SQL Standard User to Authenticate with SQL")}) $tabSQLSettings.Controls.Add($tbxSQLPassword) if ("$UserName" -ne "") { $chkSQLWindowsAuthentication.Checked = $False $tbxSQLUserName.Enabled=!($chkSQLWindowsAuthentication.Checked) $tbxSQLPassword.Enabled=!($chkSQLWindowsAuthentication.Checked) } $chkSQLCheckPort = New-Object System.Windows.Forms.CheckBox $chkSQLCheckPort.Location = New-Object System.Drawing.Size(0,80) $chkSQLCheckPort.size = new-object System.Drawing.Size(100,20) $chkSQLCheckPort.Text = "Test Port 1433" $chkSQLCheckPort.Checked = $True $chkSQLCheckPort.Add_MouseHover({$ToolTip.SetToolTip($this,"Test port 1433 before trying to connect to SQL. Avoids waiting on connection timeout")}) $tabSQLSettings.Controls.Add($chkSQLCheckPort) $chkSQLDebug = New-Object System.Windows.Forms.CheckBox $chkSQLDebug.Location = New-Object System.Drawing.Size(500,40) $chkSQLDebug.size = new-object System.Drawing.Size(175,20) $chkSQLDebug.Text = "Debug" $chkSQLDebug.Checked = $False $chkSQLDebug.Add_MouseHover({$ToolTip.SetToolTip($this,"Shows functions and more information at command line")}) $tabSQLSettings.Controls.Add($chkSQLDebug) $chkSQLAlwaysOnTop = New-Object System.Windows.Forms.CheckBox $chkSQLAlwaysOnTop.Location = New-Object System.Drawing.Size(500,60) $chkSQLAlwaysOnTop.size = new-object System.Drawing.Size(175,20) $chkSQLAlwaysOnTop.Text = "Always On Top" $chkSQLAlwaysOnTop.Checked = $form.topmost $chkSQLAlwaysOnTop.Add_Click({ClickAlwaysOnTop}) $chkSQLAlwaysOnTop.Add_MouseHover({$ToolTip.SetToolTip($this,"Always on top of all applications")}) $tabSQLSettings.Controls.Add($chkSQLAlwaysOnTop) ##### ##### Tab: Common ##### $btnSQLPercentDone = new-object System.Windows.Forms.Button $btnSQLPercentDone.Location = new-object System.Drawing.Size(0,20) $btnSQLPercentDone.Size = new-object System.Drawing.Size(120,20) $btnSQLPercentDone.Text = "Back/Restore %done" $btnSQLPercentDone.Enabled = $True $btnSQLPercentDone.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SELECT p.spid ,p.blocked ,'#min'=datediff(minute,p.last_batch,getdate()) , '##%'=r.percent_complete , 'EstMin'=datediff(minute,p.last_batch,getdate())*100/nullif(r.percent_complete,0) , 'EstimatedCompletion'=dateadd(minute,datediff(minute,p.last_batch,getdate())*100/nullif(r.percent_complete,0),p.last_batch) ,p.cmd ,p.cpu ,'io'=p.physical_io ,p.memusage ,'dbname'=db_name(p.dbid) ,loginame=rtrim(p.loginame) , p.status FROM master.dbo.sysprocesses p left outer join sys.dm_exec_requests r on p.spid=r.session_id where r.percent_complete > 0"}) #(p.cmd like '%BACKUP%' or p.cmd like '%RESTORE%' or upper(p.cmd) like '%DBCC%' or upper(p.cmd) like '%ROLL%') or $tabSQLCommon.Controls.Add($btnSQLPercentDone) $btnSQLInputbuffer = new-object System.Windows.Forms.Button $btnSQLInputbuffer.Location = new-object System.Drawing.Size(0,40) $btnSQLInputbuffer.Size = new-object System.Drawing.Size(120,20) $btnSQLInputbuffer.Text = "Inputbuffer" $btnSQLInputbuffer.Enabled = $True $btnSQLInputbuffer.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "declare @sh varbinary(64), @id smallint, @string nvarchar(256) set nocount on select top 0 session_id, sql_handle, 'text'=convert(ntext,null) into #tempdmexecsqltext from sys.dm_exec_requests DECLARE CR_sid CURSOR FOR select session_id, sql_handle from master.sys.dm_exec_requests where session_id<>@@spid and sql_handle is not null OPEN CR_sid FETCH NEXT FROM CR_sid INTO @id, @sh WHILE @@FETCH_STATUS = 0 BEGIN insert into #tempdmexecsqltext select @id, @sh, text from master.sys.dm_exec_sql_text(@sh) FETCH NEXT FROM CR_sid INTO @id, @sh END CLOSE CR_sid DEALLOCATE CR_sid set nocount off select session_id, text, sql_handle from #tempdmexecsqltext drop table #tempdmexecsqltext"}) $tabSQLCommon.Controls.Add($btnSQLInputbuffer) $btnSQLBlocking = new-object System.Windows.Forms.Button $btnSQLBlocking.Location = new-object System.Drawing.Size(0,60) $btnSQLBlocking.Size = new-object System.Drawing.Size(120,20) $btnSQLBlocking.Text = "Blocking" $btnSQLBlocking.Enabled = $True $btnSQLBlocking.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager select spid, blocked, #min=datediff(minute,last_batch,getdate()), status=rtrim(convert(nvarchar(64),status)), loginame=rtrim(convert(nvarchar(256),loginame)), hostname=rtrim(convert(nvarchar(256),hostname)), hostprocess=rtrim(convert(nvarchar(32),hostprocess)), lastwaittype=rtrim(convert(nvarchar(64),lastwaittype)), cmd=rtrim(convert(nvarchar(256),cmd)), program_name=rtrim(convert(nvarchar(256),program_name)), cpu, physical_io, memusage, dbname=db_name(dbid), last_batch, login_time, open_tran from master.dbo.sysprocesses where (blocked <> 0 or spid in (select blocked from master.dbo.sysprocesses where blocked <> 0)) order by spid"}) $tabSQLCommon.Controls.Add($btnSQLBlocking) $btnSQLSpids = new-object System.Windows.Forms.Button $btnSQLSpids.Location = new-object System.Drawing.Size(0,0) $btnSQLSpids.Size = new-object System.Drawing.Size(120,20) $btnSQLSpids.Text = "spids/sysprocesses" $btnSQLSpids.Enabled = $True $btnSQLSpids.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager select spid, blocked, dbname=db_name(dbid), #min=datediff(minute,last_batch,getdate()), loginame=rtrim(convert(nvarchar(256),loginame)), hostname=rtrim(convert(nvarchar(256),hostname)), hostprocess=rtrim(convert(nvarchar(32),hostprocess)), lastwaittype=rtrim(convert(nvarchar(64),lastwaittype)), cmd=rtrim(convert(nvarchar(256),cmd)), program_name=rtrim(convert(nvarchar(256),program_name)), status=rtrim(convert(nvarchar(64),status)), cpu, physical_io, memusage, last_batch, login_time, open_tran from master.dbo.sysprocesses order by spid "}) $tabSQLCommon.Controls.Add($btnSQLSpids) $btnSQLDBs = new-object System.Windows.Forms.Button $btnSQLDBs.Location = new-object System.Drawing.Size(120,0) $btnSQLDBs.Size = new-object System.Drawing.Size(120,20) $btnSQLDBs.Text = "Databases" $btnSQLDBs.Enabled = $True $btnSQLDBs.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text " set transaction isolation level read uncommitted --Written by: John Merager select name=d.name, 'status'=isnull(nullif(databasepropertyex(d.name,'Updateability '),'READ_WRITE'),databasepropertyex(d.name,'Status')), 'Recovery'=databasepropertyex(d.name,'Recovery'), dbid, 'owner'=suser_sname(d.sid), d.crdate, d.filename, f.[Last Full Backup], t.[Last TLog Backup] from master.dbo.sysdatabases d left outer join (select 'Last Full Backup'=max(backup_finish_date), database_name from msdb.dbo.backupset where type = 'D' group by database_name) f on f.database_name=d.name left outer join (select 'Last TLog Backup'=max(backup_finish_date), database_name from msdb.dbo.backupset where type = 'L' group by database_name) t on t.database_name=d.name "}) $tabSQLCommon.Controls.Add($btnSQLDBs) $btnSQLDBFiles = new-object System.Windows.Forms.Button $btnSQLDBFiles.Location = new-object System.Drawing.Size(120,40) $btnSQLDBFiles.Size = new-object System.Drawing.Size(120,20) $btnSQLDBFiles.Text = "DB Files" $btnSQLDBFiles.Enabled = $True $btnSQLDBFiles.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager SET NOCOUNT ON declare @dbname nvarchar(256), @tempstring nvarchar(2000) select top 0 'dbname'=@dbname, name, size, 'spaceused'=FILEPROPERTY (name, 'spaceused'), filename into #tempsysfiles from sysfiles DECLARE check_DB_Cr CURSOR FOR select name from master.dbo.sysdatabases where db_name() in ('master','msdb') or name=db_name() OPEN check_DB_Cr FETCH NEXT FROM check_DB_Cr INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN If databasepropertyex(@dbname,'Status')='ONLINE' begin set @tempstring = 'use ['+@dbname+'] select ''dbname''='''+@dbname+''', name, size, ''spaceused''=FILEPROPERTY (name, ''spaceused''), filename from dbo.sysfiles ' insert into #tempsysfiles exec(@tempstring) end FETCH NEXT FROM check_DB_Cr INTO @dbname END CLOSE check_DB_Cr DEALLOCATE check_DB_Cr SET NOCOUNT OFF select dbname, name, 'size(MB)'=size/128, 'Used(MB)'=spaceused/128, filename, 'UsedPercentBarGraph'=replace(space(convert(int,((spaceused/128))*20/nullif((size/128),0))),' ','X')+replace(space(20-convert(int,((spaceused/128))*20/nullif((size/128),0))),' ','.') from #tempsysfiles --where dbname like '%%' order by dbname, name, filename drop table #tempsysfiles"}) $tabSQLCommon.Controls.Add($btnSQLDBFiles) $btnSQLDBSizes = new-object System.Windows.Forms.Button $btnSQLDBSizes.Location = new-object System.Drawing.Size(120,20) $btnSQLDBSizes.Size = new-object System.Drawing.Size(120,20) $btnSQLDBSizes.Text = "DB Sizes" $btnSQLDBSizes.Enabled = $True $btnSQLDBSizes.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager SET NOCOUNT ON declare @dbname nvarchar(256), @tempstring nvarchar(2000) SELECT top 0 'dbname'=@dbname , 'FileType'=case FileType when 0 then 'Data' when 1 then 'Log' end , sum(size) as 'Size' , sum(Used) as 'Used' , sum(size)-sum(Used) as 'Free' into #tempsysfiles from (Select FILEPROPERTY(name, 'islogfile') as FileType, size, FILEPROPERTY (name, 'spaceused') as Used from dbo.sysfiles) a group by FileType DECLARE check_DB_Cr CURSOR FOR select name from master.dbo.sysdatabases OPEN check_DB_Cr FETCH NEXT FROM check_DB_Cr INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN If databasepropertyex(@dbname,'Status')='ONLINE' begin set @tempstring = 'use ['+@dbname+'] SELECT ''dbname''='''+@dbname+''' , ''FileType''=case FileType when 0 then ''Data'' when 1 then ''Log'' end , sum(size) as ''Size'' , sum(Used) as ''Used'' , sum(size)-sum(Used) as ''Free'' from (Select FILEPROPERTY(name, ''islogfile'') as FileType, size, FILEPROPERTY (name, ''spaceused'') as Used from dbo.sysfiles) a group by FileType' insert into #tempsysfiles exec(@tempstring) end FETCH NEXT FROM check_DB_Cr INTO @dbname END CLOSE check_DB_Cr DEALLOCATE check_DB_Cr SET NOCOUNT OFF select dbname, FileType, 'size(MB)'=Size/128, 'Used(MB)'=Used/128, 'Free(MB)'=Free/128, 'UsedPercentBarGraph'=replace(space(convert(int,((Used/128))*20/nullif((Size/128),0))),' ','X')+replace(space(20-convert(int,((Used/128))*20/nullif((Size/128),0))),' ','.') from #tempsysfiles --where dbname like '%%' order by dbname, FileType drop table #tempsysfiles"}) $tabSQLCommon.Controls.Add($btnSQLDBSizes) $btnSQLAlwaysOn = new-object System.Windows.Forms.Button $btnSQLAlwaysOn.Location = new-object System.Drawing.Size(240,0) $btnSQLAlwaysOn.Size = new-object System.Drawing.Size(120,20) $btnSQLAlwaysOn.Text = "AlwaysOn" $btnSQLAlwaysOn.Enabled = $True $btnSQLAlwaysOn.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select c.dns_name, d.database_name, a.replica_server_name, synchronization_state=isnull(isnull(replace(e.primary_recovery_health_desc,e.primary_recovery_health_desc,'PRIMARY '+isnull(e.primary_recovery_health_desc,'')), isnull(b.synchronization_state_desc,replace(e.primary_replica,e.primary_replica,'PRIMARY '+isnull(e.secondary_recovery_health_desc,'')))),f.join_state_desc), b.last_commit_time, a.availability_mode_desc, a.failover_mode_desc from sys.availability_replicas a inner join sys.availability_group_listeners c on c.group_id=a.group_id inner join sys.dm_hadr_availability_replica_cluster_states f on a.replica_id=f.replica_id and f.group_id=a.group_id left outer join sys.dm_hadr_database_replica_cluster_states d on d.replica_id=a.replica_id left outer join sys.dm_hadr_database_replica_states b on a.replica_id=b.replica_id and d.group_database_id=b.group_database_id left outer join sys.dm_hadr_availability_group_states e on e.group_id=a.group_id and e.primary_replica=a.replica_server_name"}) $tabSQLCommon.Controls.Add($btnSQLAlwaysOn) $btnSQLLocks = new-object System.Windows.Forms.Button $btnSQLLocks.Location = new-object System.Drawing.Size(360,0) $btnSQLLocks.Size = new-object System.Drawing.Size(120,20) $btnSQLLocks.Text = "SQL Locks" $btnSQLLocks.Enabled = $True $btnSQLLocks.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted set nocount on select p.spid , p.blocked , DB = db_name(l.rsc_dbid) , dbid = l.rsc_dbid , Object = object_name(l.rsc_objid) , Objid = l.rsc_objid , Status = isnull(nullif(isnull(nullif(isnull(nullif(convert(nvarchar(10),l.req_status),'1'),'Granted'),'2'),'Converting'),'3'),'Waiting') , ObjType = isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(convert(nvarchar(6),l.rsc_type),'1'),'NULL'),'2'),'DB'),'3'),'File'),'4'),'Index'),'5'),'Table'),'6'),'Page'),'7'),'Key'),'8'),'Extent'),'9'),'RowID') , FuncType = isnull(nullif(isnull(nullif(isnull(nullif(convert(nvarchar(11),l.req_ownertype),'1'),'Transaction'),'2'),'Session'),'3'),'Cursor') , Mode = convert(varchar(20),ltrim(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(convert(nvarchar(20),substring(convert(varchar(20),l.req_mode),1,1)),'1'),''),'0'),'No access'),'2'),'Sch-M No reference'),'3'),'IS Intent Shared'),'4'),'SIU Share Intent Upd'),'5'),'IS-S Intent Sharedx2'),'6'),'IX Intent Exclusive'),'7'),'SIX Shar Intent Excl'),'8'),'S Shared'),'9'),'U Update')+' '+isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(convert(nvarchar(20),l.req_mode),'1'),'Sch-S No drops'),'10'),'IIn-Nul Intent Ins'),'11'),'IS-X Intent Shar-Ex'),'12'),'IU Intent Update'),'13'),'IS-U Intent Shar-Upd'),'14'),'X Exclusive'),'15'),'BU bulk operations'))) , p.lastwaittype , p.waittime , p.cpu , p.physical_io , p.memusage , p.login_time , p.last_batch , p.open_tran , p.status , p.hostname , p.program_name , p.hostprocess , p.cmd , p.loginame into #temptable1 from master.dbo.syslockinfo l inner join master.dbo.sysprocesses p on p.spid=l.req_spid where l.rsc_objid<>0 and p.spid<>@@SPID --and (p.blocked<>0 or p.spid in (select blocked from master.dbo.sysprocesses where blocked <> 0)) --order by p.spid select top 0 DB=db_name(), Objid=id, Object=name into #temptable2 from master.dbo.sysobjects declare @db nvarchar(2048), @objectid bigint, @exec nvarchar(4000) DECLARE CR_Get_Objects CURSOR FOR select distinct DB, Objid from #temptable1 where Object is null OPEN CR_Get_Objects FETCH NEXT FROM CR_Get_Objects INTO @db, @objectid WHILE @@FETCH_STATUS = 0 BEGIN set @exec = 'select DB='''+@db+''', Objid=id, Object=name from ['+@db+'].dbo.sysobjects where id='+convert(nvarchar(256),@objectid) insert into #temptable2 exec(@exec) FETCH NEXT FROM CR_Get_Objects INTO @db, @objectid END CLOSE CR_Get_Objects DEALLOCATE CR_Get_Objects update #temptable1 set Object=b.Object from #temptable1 a inner join #temptable2 b on a.DB=b.DB and a.Objid=b.Objid where a.Object is null drop table #temptable2 set nocount off select distinct * from #temptable1 order by spid drop table #temptable1 "}) $tabSQLCommon.Controls.Add($btnSQLLocks) $btnSQLTableSizes = new-object System.Windows.Forms.Button $btnSQLTableSizes.Location = new-object System.Drawing.Size(120,60) $btnSQLTableSizes.Size = new-object System.Drawing.Size(120,20) $btnSQLTableSizes.Text = "Table Sizes" $btnSQLTableSizes.Enabled = $True $btnSQLTableSizes.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET LOCK_TIMEOUT 10000 DECLARE @PageSize int SET @PageSize = 8 SELECT --top 1000 tbl.object_id, CASE WHEN SUBSTRING(OBJECT_NAME(tbl.object_id),1,1) = '#' THEN '' ELSE OBJECT_SCHEMA_NAME(tbl.object_id) + '.' END + OBJECT_NAME(tbl.object_id) AS TableName, IsView, ISNULL(IndexCount, 0) AS [Indexes], CASE WHEN spaceUsed.[HasClusteredIndex] = 1 THEN 'Y' ELSE 'N' END AS [Clust], CONVERT(decimal (20, 3), spaceUsed.[SpaceReserved]/1024.) AS [Reserved MB], CONVERT(decimal (20, 3), spaceUsed.[DataSpaceUsed]/1024.) AS [DataUsed MB], CONVERT(decimal (20, 3), spaceUsed.[IndexSpaceUsed]/1024.) AS [IndexUsed MB], CONVERT(decimal (20, 3), spaceUsed.[SpaceUnused]/1024.) AS [Unused MB], spaceUsed.[RowCount], spaceUsed.[DS Type] AS [DS Type], spaceUsed.[DS Name] + ISNULL(' (' + PS.part_column + ')', '') AS [DS Name], ISNULL(lob_ds.name + ISNULL(' (' + PS.part_column + ')', ''), '') AS [LOB FG Name], create_date, modify_date FROM ( SELECT object_id, lob_data_space_id, CAST(0 AS BIT) AS IsView, create_date, modify_date FROM sys.tables UNION ALL SELECT v.object_id, NULL as lob_data_space_id, CAST(1 AS BIT) AS IsView, create_date, modify_date FROM sys.views v INNER JOIN sys.indexes i ON v.object_id = i.object_id ) tbl LEFT JOIN (SELECT object_id, COUNT(*) IndexCount FROM sys.indexes WHERE index_id > 0 GROUP BY object_id) idx ON idx.object_id = tbl.object_id LEFT JOIN (SELECT i.object_id, MAX(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END) AS [HasClusteredIndex], ISNULL((@PageSize * SUM(a.total_pages)) ,0.0) AS [SpaceReserved], ISNULL((@PageSize * SUM(a.total_pages-a.used_pages)) ,0.0) AS [SpaceUnused], ISNULL((@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) ,0.0) AS [DataSpaceUsed], ISNULL((@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)),0.0) AS [IndexSpaceUsed], ISNULL(SUM(CASE WHEN p.index_id < 2 AND a.type = 1 THEN p.rows ELSE 0 END), 0) AS [RowCount], MAX(CASE WHEN ds.type IN ('FG', 'PS') AND i.index_id < 2 THEN ds.type ELSE '' END) AS [DS Type], MAX(CASE WHEN ds.type IN ('FG', 'PS') AND i.index_id < 2 THEN ds.name ELSE '' END) AS [DS Name] FROM sys.indexes as i JOIN sys.partitions as p ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.allocation_units as a ON a.container_id = p.partition_id LEFT JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id GROUP BY i.object_id) spaceUsed ON spaceUsed.object_id = tbl.object_id LEFT JOIN (select ic.object_id, c.name as part_column from sys.index_columns ic inner join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id where ic.index_id = 1 AND ic.partition_ordinal > 0) PS on PS.object_id = tbl.object_id LEFT JOIN sys.data_spaces lob_ds ON lob_ds.data_space_id = tbl.lob_data_space_id AND lob_ds.type = 'FG' ORDER BY 6 desc--TableName "}) $tabSQLCommon.Controls.Add($btnSQLTableSizes) $btnSQLIndexStats = new-object System.Windows.Forms.Button $btnSQLIndexStats.Location = new-object System.Drawing.Size(360,60) $btnSQLIndexStats.Size = new-object System.Drawing.Size(120,20) $btnSQLIndexStats.Text = "Index Stats Date" $btnSQLIndexStats.Enabled = $True $btnSQLIndexStats.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager SELECT 'Table'=o.name, 'Index Name' = i.name, 'Statistics Date' = STATS_DATE(i.id, i.indid) FROM sysobjects o inner join sysindexes i on o.id = i.id --WHERE o.name like '%' order by o.name, i.name "}) $tabSQLCommon.Controls.Add($btnSQLIndexStats) $btnSQLMirroring = new-object System.Windows.Forms.Button $btnSQLMirroring.Location = new-object System.Drawing.Size(240,20) $btnSQLMirroring.Size = new-object System.Drawing.Size(120,20) $btnSQLMirroring.Text = "Mirroring" $btnSQLMirroring.Enabled = $True $btnSQLMirroring.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager declare @dbname nvarchar(1024) if exists (select * from master.dbo.sysobjects where name='database_mirroring') BEGIN set @dbname = db_name() if not exists (select * from master.sys.database_mirroring where database_id=db_id(@dbname) and mirroring_failover_lsn is not null) if exists (select * from master.sys.database_mirroring where mirroring_failover_lsn is not null) set @dbname = (select top 1 db_name(database_id) from master.sys.database_mirroring where mirroring_failover_lsn is not null) if exists (select * from master.sys.database_mirroring where database_id=db_id(@dbname) and mirroring_failover_lsn is not null and mirroring_failover_lsn > 0) begin select top 1 * into #temptable1 from master.sys.database_mirroring where database_id=db_id(@dbname) select top 2 * into #temptable2 from msdb.dbo.backupset where type='L' and database_name = @dbname and server_name = @@servername order by backup_set_id desc select top 1 * into #temptablemin from #temptable2 order by backup_set_id desc select top 1 * into #temptablemax from #temptable2 order by backup_set_id if exists (select * from #temptable1 where upper(mirroring_role_desc) = 'MIRROR') select 'SQLServer'=@@servername , 'Partner'=a.mirroring_partner_instance , 'database_name'=@dbname , 'Role'=a.mirroring_role_desc , 'State'=a.mirroring_state_desc , 'Sec_Threshold'=a.mirroring_connection_timeout*60 , 'Safty'=a.mirroring_safety_level_desc , 'Witness_State'=a.mirroring_witness_state_desc , a.mirroring_witness_name , a.mirroring_failover_lsn from #temptable1 a else if exists (select * from #temptable1 a, #temptablemin b where a.mirroring_failover_lsn > b.last_lsn) select 'SQLServer'=@@servername , 'Partner'=a.mirroring_partner_instance , 'database_name'=@dbname , 'Role'=a.mirroring_role_desc , 'State'=a.mirroring_state_desc , 'DelayMinSec'=0 --Delay between THIS , 'DelayMaxSec'=datediff(second,b.backup_start_date,getdate()) --AND THIS , 'Sec_Threshold'=a.mirroring_connection_timeout*60 , 'Safty'=a.mirroring_safety_level_desc , 'Witness_State'=a.mirroring_witness_state_desc , a.mirroring_witness_name , a.mirroring_failover_lsn, 'TLog_last_lsn'=b.last_lsn from #temptable1 a left outer join #temptablemin b on a.database_id=db_id(b.database_name) else select 'SQLServer'=@@servername , 'Partner'=a.mirroring_partner_instance , 'database_name'=@dbname , 'Role'=a.mirroring_role_desc , 'State'=a.mirroring_state_desc , 'DelayMinSec'=datediff(second,b.backup_start_date,getdate()) , 'DelayMaxSec'=datediff(second,c.backup_start_date,getdate()) , 'Sec_Threshold'=a.mirroring_connection_timeout*60 , 'Safty'=a.mirroring_safety_level_desc , 'Witness_State'=a.mirroring_witness_state_desc , a.mirroring_witness_name , a.mirroring_failover_lsn, 'TLog_last_lsn'=b.last_lsn from #temptable1 a left outer join #temptablemin b on a.database_id=db_id(b.database_name) left outer join #temptablemax c on a.database_id=db_id(c.database_name) drop table #temptable1 drop table #temptable2 drop table #temptablemin drop table #temptablemax end else begin select 'STATUS'='No Active Mirroring' end END ELSE BEGIN select 'STATUS'='No Mirroring' END "}) $tabSQLCommon.Controls.Add($btnSQLMirroring) $btnSQLLogShipping = new-object System.Windows.Forms.Button $btnSQLLogShipping.Location = new-object System.Drawing.Size(240,40) $btnSQLLogShipping.Size = new-object System.Drawing.Size(120,20) $btnSQLLogShipping.Text = "LogShipping" $btnSQLLogShipping.Enabled = $True $btnSQLLogShipping.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager /* select 'type'='backup', last_filename=last_backup_filename, servertype='primary', server_name=primary_server_name, 'database'=primary_database_name, last_updated=last_updated, 'upt_min'=datediff(n,last_updated,getdate()), 'TLog_Diff_Min'=convert(bigint,0) from msdb.dbo.log_shipping_primaries union select 'type'='copied', last_filename=last_copied_filename, servertype='secondary', server_name=secondary_server_name, 'database'=secondary_database_name, last_updated=last_copied_last_updated, 'upt_min'=datediff(n,last_copied_last_updated,getdate()), 'TLog_Diff_Min'=convert(bigint,0) from msdb.dbo.log_shipping_secondaries union select 'type'='loaded', last_filename=last_loaded_filename, servertype='secondary', server_name=secondary_server_name, 'database'=secondary_database_name, last_updated=last_loaded_last_updated, 'upt_min'=datediff(n,last_loaded_last_updated,getdate()), 'TLog_Diff_Min'=convert(bigint,0) from msdb.dbo.log_shipping_secondaries select 'type'='primarymonitor', 'server'=primary_server, 'database'=primary_database from msdb.dbo.log_shipping_monitor_primary union select 'type'='primarysecondary', 'server'=primary_server, 'database'=primary_database from msdb.dbo.log_shipping_secondary union select 'type'='monitorprimary', 'server'=monitor_server, 'database'=primary_database from msdb.dbo.log_shipping_primary_databases union select 'type'='secondaryprimary', 'server'=secondary_server, 'database'=secondary_database from msdb.dbo.log_shipping_primary_secondaries */ select 'type'='backup', last_filename=last_backup_filename, primary_server=primary_server_name, secondary_server='', 'database'=primary_database_name, last_updated=last_updated, 'upt_min'=datediff(n,last_updated,getdate()), 'TLog_Diff_Min'=datediff(minute,convert(datetime,STUFF(STUFF(STUFF(left(right(last_backup_filename,18),14),13,0,':'),11,0,':'),9,0,' ')),getutcdate()) from msdb.dbo.log_shipping_primaries where last_backup_filename like '%.trn' union select 'type'='copied', last_filename=last_copied_filename, primary_server='', secondary_server=secondary_server_name, 'database'=secondary_database_name, last_updated=last_copied_last_updated, 'upt_min'=datediff(n,last_copied_last_updated,getdate()), 'TLog_Diff_Min'=datediff(minute,convert(datetime,STUFF(STUFF(STUFF(left(right(last_copied_filename,18),14),13,0,':'),11,0,':'),9,0,' ')),getutcdate()) from msdb.dbo.log_shipping_secondaries where last_copied_filename like '%.trn' union select 'type'='loaded', last_filename=last_loaded_filename, primary_server='', secondary_server=secondary_server_name, 'database'=secondary_database_name, last_updated=last_loaded_last_updated, 'upt_min'=datediff(n,last_loaded_last_updated,getdate()), 'TLog_Diff_Min'=datediff(minute,convert(datetime,STUFF(STUFF(STUFF(left(right(last_loaded_filename,18),14),13,0,':'),11,0,':'),9,0,' ')),getutcdate()) from msdb.dbo.log_shipping_secondaries where last_loaded_filename like '%.trn' union select 'type'='backup', last_file=replace(right('\'+a.last_backup_file,charindex('\',reverse('\'+a.last_backup_file))-1),'**** This node is part of an availability group and not its preferred backup replica. ****',''), a.primary_server, secondary_server=isnull(b.secondary_server,''), 'database'=a.primary_database, last_date=a.last_backup_date, 'upt_min'=datediff(n,a.last_backup_date,getdate()), 'TLog_Diff_Min'=datediff(minute,convert(datetime,STUFF(STUFF(STUFF(left(right(replace(right('\'+a.last_backup_file,charindex('\',reverse('\'+a.last_backup_file))-1),'**** This node is part of an availability group and not its preferred backup replica. ****',''),18),14),13,0,':'),11,0,':'),9,0,' ')),getutcdate()) from msdb.dbo.log_shipping_monitor_primary a left outer join msdb.dbo.log_shipping_primary_secondaries b on a.primary_id=b.primary_id where a.last_backup_file like '%.trn' --a.last_backup_file<>'**** The primary database is in either NORECOVERY mode or STANDBY mode. ****' union select 'type'='copied', last_file=right(a.last_copied_file,charindex('\',reverse(a.last_copied_file))-1), a.primary_server, secondary_server=isnull(b.secondary_server,''), 'database'=isnull(b.secondary_database,''), last_date=a.last_copied_date, 'upt_min'=datediff(n,a.last_copied_date,getdate()), 'TLog_Diff_Min'=datediff(minute,convert(datetime,STUFF(STUFF(STUFF(left(right(right('\'+a.last_copied_file,charindex('\',reverse('\'+a.last_copied_file))-1),18),14),13,0,':'),11,0,':'),9,0,' ')),getutcdate()) from msdb.dbo.log_shipping_secondary a left outer join msdb.dbo.log_shipping_monitor_secondary b on a.secondary_id=b.secondary_id where a.last_copied_file like '%.trn' and a.last_copied_date is not null --a.last_copied_file is not null and --union select 'type'='copied', last_file=right(last_copied_file,charindex('\',reverse(last_copied_file))-1), 'servertype'='secondary', 'server'=@@SERVERNAME, 'database'='', last_date=last_copied_date, 'upt_min'=datediff(n,last_copied_date,getdate()), 'TLog_Diff_Min'=getutcdate() from msdb.dbo.log_shipping_secondary where last_copied_file is not null and last_copied_date is not null union select 'type'='restored', last_file=right(a.last_restored_file,charindex('\',reverse(a.last_restored_file))-1), b.primary_server, secondary_server=isnull(b.secondary_server,''), 'database'=a.secondary_database, last_date=a.last_restored_date, 'upt_min'=datediff(n,a.last_restored_date,getdate()), 'TLog_Diff_Min'=datediff(minute,convert(datetime,STUFF(STUFF(STUFF(left(right(right('\'+a.last_restored_file,charindex('\',reverse('\'+a.last_restored_file))-1),18),14),13,0,':'),11,0,':'),9,0,' ')),getutcdate()) from msdb.dbo.log_shipping_secondary_databases a left outer join msdb.dbo.log_shipping_monitor_secondary b on a.secondary_id=b.secondary_id where a.last_restored_file like '%.trn' and a.last_restored_date is not null --a.last_restored_file is not null and --union select 'type'='restored', last_file=right(last_restored_file,charindex('\',reverse(last_restored_file))-1), 'servertype'='secondary', 'server'=@@SERVERNAME, 'database'=secondary_database, last_date=last_restored_date, 'upt_min'=datediff(n,last_restored_date,getdate()), 'TLog_Diff_Min'=getutcdate() from msdb.dbo.log_shipping_secondary_databases where last_restored_file is not null and last_restored_date is not null "}) $tabSQLCommon.Controls.Add($btnSQLLogShipping) $btnSQLLastBackup = new-object System.Windows.Forms.Button $btnSQLLastBackup.Location = new-object System.Drawing.Size(240,60) $btnSQLLastBackup.Size = new-object System.Drawing.Size(120,20) $btnSQLLastBackup.Text = "LastBackup" $btnSQLLastBackup.Enabled = $True $btnSQLLastBackup.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager select a.server_name, a.database_name, type=isnull(nullif(isnull(nullif(isnull(nullif(convert(nvarchar(4),a.type),'L'),'TLog'),'I'),'Diff'),'D'),'Full'), 'sizeGB'=convert(bigint,a.backup_size/1024/1024/1024), 'compressGB'=convert(bigint,a.compressed_backup_size/1024/1024/1024), 'started'=a.backup_start_date, 'finished'=a.backup_finish_date, '#min'=datediff(minute,a.backup_start_date,a.backup_finish_date), a.user_name, a.backup_set_id, a.media_set_id, a.collation_name, a.compatibility_level, a.recovery_model, a.first_lsn, a.last_lsn, a.checkpoint_lsn, a.database_backup_lsn from msdb.dbo.backupset a inner join ( select backup_finish_date=max(backup_finish_date), database_name, type from msdb.dbo.backupset group by database_name, type ) b on a.database_name=b.database_name and a.type=b.type and a.backup_finish_date=b.backup_finish_date "}) $tabSQLCommon.Controls.Add($btnSQLLastBackup) $btnSQLspconfigure = new-object System.Windows.Forms.Button $btnSQLspconfigure.Location = new-object System.Drawing.Size(360,20) $btnSQLspconfigure.Size = new-object System.Drawing.Size(120,20) $btnSQLspconfigure.Text = "sp_configure" $btnSQLspconfigure.Enabled = $True $btnSQLspconfigure.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "exec sp_configure"}) $tabSQLCommon.Controls.Add($btnSQLspconfigure) $btnSQLQueryPlan = new-object System.Windows.Forms.Button $btnSQLQueryPlan.Location = new-object System.Drawing.Size(360,40) $btnSQLQueryPlan.Size = new-object System.Drawing.Size(120,20) $btnSQLQueryPlan.Text = "QueryPlan" $btnSQLQueryPlan.Enabled = $True $btnSQLQueryPlan.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager --Warning: This might show a lot of data which can make IE (HTML) run slow. ----------Best to specify the spid with session_id=??? where ??? is the spid number declare @spid bigint, @planhandle varbinary(64) if exists (select * from master.dbo.sysobjects where name='dm_exec_requests') begin set nocount on select top 0 'spid'=convert(bigint,null), query_plan, 'plan_handle'=convert(varbinary(64),null) into #temptable from master.sys.dm_exec_query_plan(null) DECLARE CR_Get_Plan_Handle CURSOR FOR Select session_id, plan_handle from master.sys.dm_exec_requests where plan_handle is not null and session_id<>@@spid --and session_id=??? OPEN CR_Get_Plan_Handle FETCH NEXT FROM CR_Get_Plan_Handle INTO @spid, @planhandle WHILE @@FETCH_STATUS = 0 BEGIN insert into #temptable select 'spid'=@spid, query_plan, 'plan_handle'=@planhandle from master.sys.dm_exec_query_plan(@planhandle) FETCH NEXT FROM CR_Get_Plan_Handle INTO @spid, @planhandle END CLOSE CR_Get_Plan_Handle DEALLOCATE CR_Get_Plan_Handle set nocount off select * from #temptable where query_plan is not null order by spid drop table #temptable end "}) $tabSQLCommon.Controls.Add($btnSQLQueryPlan) $chkSQLERRORLOGDesc = New-Object System.Windows.Forms.CheckBox $chkSQLERRORLOGDesc.Location = New-Object System.Drawing.Size(600,0) $chkSQLERRORLOGDesc.size = new-object System.Drawing.Size(50,20) $chkSQLERRORLOGDesc.Text = "Desc" $chkSQLERRORLOGDesc.Checked = $True #$chkSQLERRORLOGDesc.Add_Click({ClickDeployLoadSourceList}) $tabSQLCommon.Controls.Add($chkSQLERRORLOGDesc) $lblSQLERRORLOGStr1 = New-Object System.Windows.Forms.Label $lblSQLERRORLOGStr1.Location = New-Object System.Drawing.Size(690,3) $lblSQLERRORLOGStr1.Size = New-Object System.Drawing.Size(30,15) $lblSQLERRORLOGStr1.Text = "Str1" $tabSQLCommon.Controls.Add($lblSQLERRORLOGStr1) $tbxSQLERRORLOGStr1 = New-Object System.Windows.Forms.TextBox $tbxSQLERRORLOGStr1.Location = New-Object System.Drawing.Size(720,0) $tbxSQLERRORLOGStr1.Size = New-Object System.Drawing.Size(50,10) $tbxSQLERRORLOGStr1.Height = 30 $tbxSQLERRORLOGStr1.Text="" #$tbxSQLERRORLOGStr1.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLERRORLOGStr1.Add_LostFocus({ChangedFocusDeployFunctional}) $tabSQLCommon.Controls.Add($tbxSQLERRORLOGStr1) $lblSQLERRORLOGStr2 = New-Object System.Windows.Forms.Label $lblSQLERRORLOGStr2.Location = New-Object System.Drawing.Size(770,3) $lblSQLERRORLOGStr2.Size = New-Object System.Drawing.Size(30,15) $lblSQLERRORLOGStr2.Text = "Str2" $tabSQLCommon.Controls.Add($lblSQLERRORLOGStr2) $tbxSQLERRORLOGStr2 = New-Object System.Windows.Forms.TextBox $tbxSQLERRORLOGStr2.Location = New-Object System.Drawing.Size(800,0) $tbxSQLERRORLOGStr2.Size = New-Object System.Drawing.Size(50,10) $tbxSQLERRORLOGStr2.Height = 30 $tbxSQLERRORLOGStr2.Text="" #$tbxSQLERRORLOGStr2.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLERRORLOGStr2.Add_LostFocus({ChangedFocusDeployFunctional}) $tabSQLCommon.Controls.Add($tbxSQLERRORLOGStr2) $chkSQLERRORLOGEntireLog = New-Object System.Windows.Forms.CheckBox $chkSQLERRORLOGEntireLog.Location = New-Object System.Drawing.Size(855,0) $chkSQLERRORLOGEntireLog.size = new-object System.Drawing.Size(75,20) $chkSQLERRORLOGEntireLog.Text = "EntireLog" $chkSQLERRORLOGEntireLog.Checked = $True #$chkSQLERRORLOGEntireLog.Enabled = $False $chkSQLERRORLOGEntireLog.Add_Click({ $tbxSQLERRORLOGFrom.Enabled=!($chkSQLERRORLOGEntireLog.Checked) $tbxSQLERRORLOGTo.Enabled=!($chkSQLERRORLOGEntireLog.Checked) }) $tabSQLCommon.Controls.Add($chkSQLERRORLOGEntireLog) $lblSQLERRORLOGFrom = New-Object System.Windows.Forms.Label $lblSQLERRORLOGFrom.Location = New-Object System.Drawing.Size(930,3) $lblSQLERRORLOGFrom.Size = New-Object System.Drawing.Size(35,15) $lblSQLERRORLOGFrom.Text = "From" $tabSQLCommon.Controls.Add($lblSQLERRORLOGFrom) $tbxSQLERRORLOGFrom = New-Object System.Windows.Forms.TextBox $tbxSQLERRORLOGFrom.Location = New-Object System.Drawing.Size(965,0) $tbxSQLERRORLOGFrom.Size = New-Object System.Drawing.Size(50,10) $tbxSQLERRORLOGFrom.Height = 30 $tbxSQLERRORLOGFrom.Text="$((get-date).AddDays(-1).Year)-$((get-date).AddDays(-1).Month)-$((get-date).AddDays(-1).Day)" $tbxSQLERRORLOGFrom.Enabled=$False #$tbxSQLERRORLOGFrom.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLERRORLOGFrom.Add_LostFocus({ChangedFocusDeployFunctional}) $tabSQLCommon.Controls.Add($tbxSQLERRORLOGFrom) $lblSQLERRORLOGTO = New-Object System.Windows.Forms.Label $lblSQLERRORLOGTO.Location = New-Object System.Drawing.Size(1015,3) $lblSQLERRORLOGTO.Size = New-Object System.Drawing.Size(20,15) $lblSQLERRORLOGTO.Text = "To" $tabSQLCommon.Controls.Add($lblSQLERRORLOGTO) $tbxSQLERRORLOGTO = New-Object System.Windows.Forms.TextBox $tbxSQLERRORLOGTO.Location = New-Object System.Drawing.Size(1040,0) $tbxSQLERRORLOGTO.Size = New-Object System.Drawing.Size(50,10) $tbxSQLERRORLOGTO.Height = 30 $tbxSQLERRORLOGTO.Text="$((get-date).Year)-$((get-date).Month)-$((get-date).Day)" $tbxSQLERRORLOGTO.Enabled=$False #$tbxSQLERRORLOGTO.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLERRORLOGTO.Add_LostFocus({ChangedFocusDeployFunctional}) $tabSQLCommon.Controls.Add($tbxSQLERRORLOGTO) $cbxSQLERRORLOG = New-Object System.Windows.Forms.ComboBox $cbxSQLERRORLOG.Location = New-Object System.Drawing.Size(650,00) $cbxSQLERRORLOG.Size = New-Object System.Drawing.Size(40,10) $cbxSQLERRORLOG.Height = 30 $cbxSQLERRORLOG.Sorted = $True $cbxSQLERRORLOG.DropDownStyle="DropDownList" $cbxSQLERRORLOG.Enabled=$True #$cbxSQLERRORLOG.Add_SelectedValueChanged({ChangedSelectDeploySource}) $tabSQLCommon.Controls.Add($cbxSQLERRORLOG) [void] $cbxSQLERRORLOG.Items.Add("0") $cbxSQLERRORLOG.SelectedItem="0" [void] $cbxSQLERRORLOG.Items.Add("1") [void] $cbxSQLERRORLOG.Items.Add("2") [void] $cbxSQLERRORLOG.Items.Add("3") [void] $cbxSQLERRORLOG.Items.Add("4") [void] $cbxSQLERRORLOG.Items.Add("5") [void] $cbxSQLERRORLOG.Items.Add("6") [void] $cbxSQLERRORLOG.Items.Add("7") [void] $cbxSQLERRORLOG.Items.Add("8") [void] $cbxSQLERRORLOG.Items.Add("9") $btnSQLERRORLOG = new-object System.Windows.Forms.Button $btnSQLERRORLOG.Location = new-object System.Drawing.Size(480,0) $btnSQLERRORLOG.Size = new-object System.Drawing.Size(120,20) $btnSQLERRORLOG.Text = "ERRORLOG" $btnSQLERRORLOG.Enabled = $True $btnSQLERRORLOG.Add_Click({ if ("$($tbxSQLERRORLOGStr1.Text)" -eq "") { $LogStr1="NULL" } else { $LogStr1="N'$($tbxSQLERRORLOGStr1.Text)'" } if ("$($tbxSQLERRORLOGStr2.Text)" -eq "") { $LogStr2="NULL" } else { $LogStr2="N'$($tbxSQLERRORLOGStr2.Text)'" } if ("$($tbxSQLERRORLOGFrom.Text)" -eq "" -Or ($chkSQLERRORLOGEntireLog.Checked)) { $LogFrom="NULL" } else { $LogFrom="N'$($tbxSQLERRORLOGFrom.Text)'" } if ("$($tbxSQLERRORLOGTo.Text)" -eq "" -Or ($chkSQLERRORLOGEntireLog.Checked)) { $LogTo="NULL" } else { $LogTo="N'$($tbxSQLERRORLOGTo.Text)'" } if ($chkSQLERRORLOGDesc.Checked) { $LogDesc="Desc" } else { $LogDesc="Asc" } ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "EXEC master.dbo.xp_readerrorlog $($cbxSQLERRORLOG.SelectedItem), 1, $LogStr1, $LogStr2, $LogFrom, $LogTo, N'$LogDesc'" }) $tabSQLCommon.Controls.Add($btnSQLERRORLOG) $chkSQLAgentLogDesc = New-Object System.Windows.Forms.CheckBox $chkSQLAgentLogDesc.Location = New-Object System.Drawing.Size(600,20) $chkSQLAgentLogDesc.size = new-object System.Drawing.Size(50,20) $chkSQLAgentLogDesc.Text = "Desc" $chkSQLAgentLogDesc.Checked = $True #$chkSQLAgentLogDesc.Add_Click({ClickDeployLoadSourceList}) $tabSQLCommon.Controls.Add($chkSQLAgentLogDesc) $lblSQLAgentLogStr1 = New-Object System.Windows.Forms.Label $lblSQLAgentLogStr1.Location = New-Object System.Drawing.Size(690,23) $lblSQLAgentLogStr1.Size = New-Object System.Drawing.Size(30,15) $lblSQLAgentLogStr1.Text = "Str1" $tabSQLCommon.Controls.Add($lblSQLAgentLogStr1) $tbxSQLAgentLogStr1 = New-Object System.Windows.Forms.TextBox $tbxSQLAgentLogStr1.Location = New-Object System.Drawing.Size(720,20) $tbxSQLAgentLogStr1.Size = New-Object System.Drawing.Size(50,10) $tbxSQLAgentLogStr1.Height = 30 $tbxSQLAgentLogStr1.Text="" #$tbxSQLAgentLogStr1.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLAgentLogStr1.Add_LostFocus({ChangedFocusDeployFunctional}) $tabSQLCommon.Controls.Add($tbxSQLAgentLogStr1) $lblSQLAgentLogStr2 = New-Object System.Windows.Forms.Label $lblSQLAgentLogStr2.Location = New-Object System.Drawing.Size(770,23) $lblSQLAgentLogStr2.Size = New-Object System.Drawing.Size(30,15) $lblSQLAgentLogStr2.Text = "Str2" $tabSQLCommon.Controls.Add($lblSQLAgentLogStr2) $tbxSQLAgentLogStr2 = New-Object System.Windows.Forms.TextBox $tbxSQLAgentLogStr2.Location = New-Object System.Drawing.Size(800,20) $tbxSQLAgentLogStr2.Size = New-Object System.Drawing.Size(50,10) $tbxSQLAgentLogStr2.Height = 30 $tbxSQLAgentLogStr2.Text="" #$tbxSQLAgentLogStr2.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLAgentLogStr2.Add_LostFocus({ChangedFocusDeployFunctional}) $tabSQLCommon.Controls.Add($tbxSQLAgentLogStr2) $chkSQLAgentLogEntireLog = New-Object System.Windows.Forms.CheckBox $chkSQLAgentLogEntireLog.Location = New-Object System.Drawing.Size(855,20) $chkSQLAgentLogEntireLog.size = new-object System.Drawing.Size(75,20) $chkSQLAgentLogEntireLog.Text = "EntireLog" $chkSQLAgentLogEntireLog.Checked = $True #$chkSQLAgentLogEntireLog.Enabled = $False $chkSQLAgentLogEntireLog.Add_Click({ $tbxSQLAgentLogFrom.Enabled=!($chkSQLAgentLogEntireLog.Checked) $tbxSQLAgentLogTo.Enabled=!($chkSQLAgentLogEntireLog.Checked) }) $tabSQLCommon.Controls.Add($chkSQLAgentLogEntireLog) $lblSQLAgentLogFrom = New-Object System.Windows.Forms.Label $lblSQLAgentLogFrom.Location = New-Object System.Drawing.Size(930,23) $lblSQLAgentLogFrom.Size = New-Object System.Drawing.Size(35,15) $lblSQLAgentLogFrom.Text = "From" $tabSQLCommon.Controls.Add($lblSQLAgentLogFrom) $tbxSQLAgentLogFrom = New-Object System.Windows.Forms.TextBox $tbxSQLAgentLogFrom.Location = New-Object System.Drawing.Size(965,20) $tbxSQLAgentLogFrom.Size = New-Object System.Drawing.Size(50,10) $tbxSQLAgentLogFrom.Height = 30 $tbxSQLAgentLogFrom.Text="$((get-date).AddDays(-1).Year)-$((get-date).AddDays(-1).Month)-$((get-date).AddDays(-1).Day)" $tbxSQLAgentLogFrom.Enabled=$False #$tbxSQLAgentLogFrom.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLAgentLogFrom.Add_LostFocus({ChangedFocusDeployFunctional}) $tabSQLCommon.Controls.Add($tbxSQLAgentLogFrom) $lblSQLAgentLogTO = New-Object System.Windows.Forms.Label $lblSQLAgentLogTO.Location = New-Object System.Drawing.Size(1015,23) $lblSQLAgentLogTO.Size = New-Object System.Drawing.Size(20,15) $lblSQLAgentLogTO.Text = "To" $tabSQLCommon.Controls.Add($lblSQLAgentLogTO) $tbxSQLAgentLogTO = New-Object System.Windows.Forms.TextBox $tbxSQLAgentLogTO.Location = New-Object System.Drawing.Size(1040,20) $tbxSQLAgentLogTO.Size = New-Object System.Drawing.Size(50,10) $tbxSQLAgentLogTO.Height = 30 $tbxSQLAgentLogTO.Text="$((get-date).Year)-$((get-date).Month)-$((get-date).Day)" $tbxSQLAgentLogTO.Enabled=$False #$tbxSQLAgentLogTO.Add_SelectedValueChanged({ChangedSelectDeployFunctional}) #$tbxSQLAgentLogTO.Add_LostFocus({ChangedFocusDeployFunctional}) $tabSQLCommon.Controls.Add($tbxSQLAgentLogTO) $cbxSQLAgentLog = New-Object System.Windows.Forms.ComboBox $cbxSQLAgentLog.Location = New-Object System.Drawing.Size(650,20) $cbxSQLAgentLog.Size = New-Object System.Drawing.Size(40,10) $cbxSQLAgentLog.Height = 30 $cbxSQLAgentLog.Sorted = $True $cbxSQLAgentLog.DropDownStyle="DropDownList" $cbxSQLAgentLog.Enabled=$True #$cbxSQLAgentLog.Add_SelectedValueChanged({ChangedSelectDeploySource}) $tabSQLCommon.Controls.Add($cbxSQLAgentLog) [void] $cbxSQLAgentLog.Items.Add("0") $cbxSQLAgentLog.SelectedItem="0" [void] $cbxSQLAgentLog.Items.Add("1") [void] $cbxSQLAgentLog.Items.Add("2") [void] $cbxSQLAgentLog.Items.Add("3") [void] $cbxSQLAgentLog.Items.Add("4") [void] $cbxSQLAgentLog.Items.Add("5") [void] $cbxSQLAgentLog.Items.Add("6") [void] $cbxSQLAgentLog.Items.Add("7") [void] $cbxSQLAgentLog.Items.Add("8") [void] $cbxSQLAgentLog.Items.Add("9") $btnSQLAgentLog = new-object System.Windows.Forms.Button $btnSQLAgentLog.Location = new-object System.Drawing.Size(480,20) $btnSQLAgentLog.Size = new-object System.Drawing.Size(120,20) $btnSQLAgentLog.Text = "Agent Log" $btnSQLAgentLog.Enabled = $True $btnSQLAgentLog.Add_Click({ if ("$($tbxSQLAgentLogStr1.Text)" -eq "") { $LogStr1="NULL" } else { $LogStr1="N'$($tbxSQLAgentLogStr1.Text)'" } if ("$($tbxSQLAgentLogStr2.Text)" -eq "") { $LogStr2="NULL" } else { $LogStr2="N'$($tbxSQLAgentLogStr2.Text)'" } if ("$($tbxSQLAgentLogFrom.Text)" -eq "" -Or ($chkSQLAgentLogEntireLog.Checked)) { $LogFrom="NULL" } else { $LogFrom="N'$($tbxSQLAgentLogFrom.Text)'" } if ("$($tbxSQLAgentLogTo.Text)" -eq "" -Or ($chkSQLAgentLogEntireLog.Checked)) { $LogTo="NULL" } else { $LogTo="N'$($tbxSQLAgentLogTo.Text)'" } if ($chkSQLAgentLogDesc.Checked) { $LogDesc="Desc" } else { $LogDesc="Asc" } ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "EXEC master.dbo.xp_readerrorlog $($cbxSQLAgentLog.SelectedItem), 2, $LogStr1, $LogStr2, $LogFrom, $LogTo, N'$LogDesc'" }) #$btnSQLAgentLog.Add_MouseUP( { # if ($_.Button -eq [System.Windows.Forms.MouseButtons]::Right ) { # write-host "RClick- RightClick - Right mouse up"} # }) $tabSQLCommon.Controls.Add($btnSQLAgentLog) $cbxSQLJob = New-Object System.Windows.Forms.ComboBox $cbxSQLJob.Location = New-Object System.Drawing.Size(600,40) $cbxSQLJob.Size = New-Object System.Drawing.Size(100,10) $cbxSQLJob.Height = 30 $cbxSQLJob.Sorted = $True $cbxSQLJob.DropDownStyle="DropDownList" $cbxSQLJob.Enabled=$True #$cbxSQLJob.Add_SelectedValueChanged({ChangedSelectDeploySource}) $tabSQLCommon.Controls.Add($cbxSQLJob) [void] $cbxSQLJob.Items.Add("ALL") $cbxSQLJob.SelectedItem="ALL" [void] $cbxSQLJob.Items.Add("Running") [void] $cbxSQLJob.Items.Add("Failed") [void] $cbxSQLJob.Items.Add("Scheduled") [void] $cbxSQLJob.Items.Add("Disabled") [void] $cbxSQLJob.Items.Add("Enabled") [void] $cbxSQLJob.Items.Add("LastRunHist") [void] $cbxSQLJob.Items.Add("JobSteps") $btnSQLJobs = new-object System.Windows.Forms.Button $btnSQLJobs.Location = new-object System.Drawing.Size(480,40) $btnSQLJobs.Size = new-object System.Drawing.Size(120,20) $btnSQLJobs.Text = "SQL Jobs" $btnSQLJobs.Enabled = $True $btnSQLJobs.Add_Click({ $SQLAddJoin="" $SQLAddColumns="" if ($cbxSQLJob.SelectedItem -eq "ALL") { $SQLWhere="" } elseif ($cbxSQLJob.SelectedItem -eq "Running") { $SQLWhere="where r.running=1" } elseif ($cbxSQLJob.SelectedItem -eq "Failed") { $SQLWhere="where h.run_status=0" } elseif ($cbxSQLJob.SelectedItem -eq "Scheduled") { $SQLWhere="where r.next_run_date<>0" } elseif ($cbxSQLJob.SelectedItem -eq "Disabled") { $SQLWhere="where j.enabled=0" } elseif ($cbxSQLJob.SelectedItem -eq "Enabled") { $SQLWhere="where j.enabled=1" } elseif ($cbxSQLJob.SelectedItem -eq "JobSteps") { $SQLAddJoin=" inner join msdb.dbo.sysjobsteps s on s.job_id=j.job_id " $SQLAddColumns=" , s.step_id, s.step_name, s.database_name, s.output_file_name, s.subsystem, s.on_success_step_id, s.on_fail_step_id, s.cmdexec_success_code, s.command " } elseif ($cbxSQLJob.SelectedItem -eq "LastRunHist") { $SQLAddJoin=" left outer join (select a.* from msdb.dbo.sysjobhistory a inner join (select job_id, step_id, run_date_time=max(convert(nvarchar(8),run_date)+right('000000'+convert(nvarchar(6),run_time),6)) from msdb.dbo.sysjobhistory group by job_id, step_id ) b on a.job_id=b.job_id and a.step_id=b.step_id and convert(nvarchar(8),a.run_date)+right('000000'+convert(nvarchar(6),a.run_time),6)=b.run_date_time) jh on jh.job_id=j.job_id left outer join msdb.dbo.sysjobsteps s on s.job_id=jh.job_id and s.step_id=jh.step_id " $SQLAddColumns=" , jh.step_id, jh.step_name, isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(convert(nvarchar(6),jh.run_status),'-'),'0'),'Failed'),'1'),'Succes'),'2'),'Retry'),'3'),'Canceld'),'4'),'InProg') StepStatus , convert(datetime,substring(convert(nvarchar(10),nullif(jh.run_date,0)),5,2)+'/'+substring(convert(nvarchar(8),nullif(jh.run_date,0)),7,2)+'/'+substring(convert(varchar(8),nullif(jh.run_date,0)),1,4)+' '+substring(reverse(substring(reverse('000000'+convert(varchar(6),nullif(jh.run_time,0))),1,6)),1,2)+':'+substring(reverse(substring(reverse('000000'+convert(varchar(6),nullif(jh.run_time,0))),1,6)),3,2)+':'+substring(reverse(substring(reverse('000000'+convert(varchar(6),nullif(jh.run_time,0))),1,6)),5,2)) StartDateTime , ltrim(left(right(' '+convert(nvarchar(20),jh.run_duration),20),15)+left(right('00000'+convert(nvarchar(20),jh.run_duration),5),1)+':'+left(right('0000'+convert(nvarchar(20),jh.run_duration),4),2)+':'+right('0'+convert(nvarchar(20),jh.run_duration),2)) 'StepDuration' , jh.sql_message_id, jh.sql_severity, s.database_name, s.output_file_name, jh.message, s.subsystem, s.command " } ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager SET NOCOUNT ON CREATE TABLE #list_running_SQL_jobs (job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id sysname NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) declare @username varchar(128) ,@running bit ,@SearchStr varchar(255) ,@String varchar(255) INSERT INTO #list_running_SQL_jobs EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa' select instance_id=max(instance_id) into #sysjobhistorymax from msdb.dbo.sysjobhistory group by job_id select * into #sysjobhistory from msdb.dbo.sysjobhistory where instance_id in (select instance_id from #sysjobhistorymax) drop table #sysjobhistorymax select instance_id=max(instance_id) into #sysjobhistorymaxzero from msdb.dbo.sysjobhistory where job_id in (select job_id from #sysjobhistory where step_id<>0) and step_id=0 group by job_id select * into #sysjobhistoryzero from #sysjobhistory where step_id=0 union select * from msdb.dbo.sysjobhistory where instance_id in (select instance_id from #sysjobhistorymaxzero) drop table #sysjobhistorymaxzero drop table #sysjobhistory select j.job_id, p.spid , datediff(minute,p.last_batch,getdate()) '#Min' into #jobspids from msdb.dbo.sysjobs j inner join master.dbo.sysprocesses p on j.job_id=substring(substring(program_name,charindex('0x',program_name)+2,32),7,2)+substring(substring(program_name,charindex('0x',program_name)+2,32),5,2)+substring(substring(program_name,charindex('0x',program_name)+2,32),3,2)+substring(substring(program_name,charindex('0x',program_name)+2,32),1,2)+'-'+substring(substring(program_name,charindex('0x',program_name)+2,32),11,2)+substring(substring(program_name,charindex('0x',program_name)+2,32),9,2)+'-'+substring(substring(program_name,charindex('0x',program_name)+2,32),15,2)+substring(substring(program_name,charindex('0x',program_name)+2,32),13,2)+'-'+substring(substring(program_name,charindex('0x',program_name)+2,32),17,4)+'-'+substring(substring(program_name,charindex('0x',program_name)+2,32),21,12) and program_name like '%0x%' union select j.job_id, p.spid , datediff(minute,p.last_batch,getdate()) '#Min' from msdb.dbo.sysjobs j inner join master.dbo.sysprocesses p on j.name=program_name select top 0 job_id, 'start_execution_date'=convert(datetime,null) into #list_running_start from msdb.dbo.sysjobs if exists (select * from msdb.dbo.sysobjects where name='sysjobactivity') insert into #list_running_start select job_id, 'start_execution_date'=max(start_execution_date) from msdb.dbo.sysjobactivity where start_execution_date is not null and job_id in (select job_id from #list_running_SQL_jobs where running=1) group by job_id set nocount off select distinct 'Job Name'=j.name --j.name 'Job Name' --, SUSER_SNAME(j.owner_sid) 'User' , isnull(nullif(isnull(nullif(Convert(nvarchar(3),j.enabled),'1'),'Yes'),'0'),'No') 'Enbl' $SQLAddColumns , substring(convert(nvarchar(10),nullif(r.next_run_date,0)),5,2)+'/'+substring(convert(nvarchar(8),nullif(r.next_run_date,0)),7,2)+'/'+substring(convert(varchar(8),nullif(r.next_run_date,0)),1,4)+' '+substring(reverse(substring(reverse('000000'+convert(varchar(6),nullif(r.next_run_time,0))),1,6)),1,2)+':'+substring(reverse(substring(reverse('000000'+convert(varchar(6),nullif(r.next_run_time,0))),1,6)),3,2) Next_Run_Date , convert(datetime,substring(convert(nvarchar(10),nullif(r.last_run_date,0)),5,2)+'/'+substring(convert(nvarchar(8),nullif(r.last_run_date,0)),7,2)+'/'+substring(convert(varchar(8),nullif(r.last_run_date,0)),1,4)+' '+substring(reverse(substring(reverse('000000'+convert(varchar(6),nullif(r.last_run_time,0))),1,6)),1,2)+':'+substring(reverse(substring(reverse('000000'+convert(varchar(6),nullif(r.last_run_time,0))),1,6)),3,2)+':'+substring(reverse(substring(reverse('000000'+convert(varchar(6),nullif(r.last_run_time,0))),1,6)),5,2)) Last_Run_Date , isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(convert(nvarchar(6),h.run_status),'-'),'0'),'Failed'),'1'),'Succes'),'2'),'Retry'),'3'),'Canceld'),'4'),'InProg') LastStatus , ltrim(left(right(' '+convert(nvarchar(20),h.run_duration),20),15)+left(right('00000'+convert(nvarchar(20),h.run_duration),5),1)+':'+left(right('0000'+convert(nvarchar(20),h.run_duration),4),2)+':'+right('0'+convert(nvarchar(20),h.run_duration),2)) 'LastDuration' , r.current_step OnStep--, st.run_duration/60 'LastStepMin' , isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(convert(varchar(9),r.job_state),'4'),'Idle'),'1'),'Executing'),'2'),'Wait'),'3'),'BtwnRetry'),'5'),'Suspended'),'6'),'Obsolete'),'7'),'Finishing') State , p.spid , isnull(datediff(minute,rs.start_execution_date,getdate()),p.[#Min]) 'CurMin' , 'Owner'=isnull(suser_sname(j.owner_sid),'S-'+convert(nvarchar(12),convert(bigint,UNICODE(left(convert(nvarchar(256),j.owner_sid),1)))-convert(bigint,256)*convert(bigint,UNICODE(left(convert(nvarchar(256),j.owner_sid),1))/256)) +'-'+convert(nvarchar(12),UNICODE(right(left(convert(nvarchar(256),j.owner_sid),4),1))/256+convert(bigint,nullif(UNICODE(left(convert(nvarchar(256),j.owner_sid),1))/256,0))-convert(bigint,UNICODE(left(convert(nvarchar(256),j.owner_sid),1))/256)) +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),5),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),6),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),j.owner_sid))-6),-1))*0),'') +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),7),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),8),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),j.owner_sid))-8),-1))*0),'') +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),9),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),10),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),j.owner_sid))-10),-1))*0),'') +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),11),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),12),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),j.owner_sid))-12),-1))*0),'') +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),13),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),j.owner_sid),14),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),j.owner_sid))-14),-1))*0),'') ) --NT SID from msdb.dbo.sysjobs j $SQLAddJoin left outer join #list_running_SQL_jobs r on j.job_id=r.job_id left outer join #sysjobhistoryzero h on j.job_id=h.job_id left outer join #jobspids p on p.job_id=j.job_id left outer join #list_running_start rs on rs.job_id=j.job_id --left outer join #sysjobhistorystep st on st.job_id=j.job_id --For Last step runtime $SQLWhere ORDER BY 1, 3 --where r.running=1 --Running jobs --where h.run_status=0 --Last run failed --Where r.next_run_date<>0 -- scheduled jobs --Where j.enabled=1 -- enabled jobs --Where j.name like '%jobname%' drop table #list_running_SQL_jobs drop table #sysjobhistoryzero drop table #jobspids drop table #list_running_start --drop table #sysjobhistorystep "}) $tabSQLCommon.Controls.Add($btnSQLJobs) ##### ##### Tab: SAP ##### $btnSQLSAPWSSNC0 = new-object System.Windows.Forms.Button $btnSQLSAPWSSNC0.Location = new-object System.Drawing.Size(360,0) $btnSQLSAPWSSNC0.Size = new-object System.Drawing.Size(120,20) $btnSQLSAPWSSNC0.Text = "WS SNC0" $btnSQLSAPWSSNC0.Enabled = $True $btnSQLSAPWSSNC0.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint if exists (select * from sysobjects where name='SNCSYSACL') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='SNCSYSACL' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''SNCSYSACL'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''SNCSYSACL'' and schema_name(uid)='''+@schema+''') select ''DB''=db_name(), TYP, R_SYSID, PNAME, MNAME, MDATE, ''TCode''=''SNC0'', ''Desc''=''Webservice App Pool User'' from SNCSYSACL where TYP=''E'' else select ''DB''=db_name(), TYP, R_SYSID, PNAME, MNAME, MDATE, ''TCode''=''SNC0'', ''Desc''=''Webservice App Pool User'' from ['+@schema+'].SNCSYSACL where TYP=''E''')"}) $tabSQLSAP.Controls.Add($btnSQLSAPWSSNC0) $btnSQLSAPWSMapping = new-object System.Windows.Forms.Button $btnSQLSAPWSMapping.Location = new-object System.Drawing.Size(360,20) $btnSQLSAPWSMapping.Size = new-object System.Drawing.Size(120,20) $btnSQLSAPWSMapping.Text = "WS Mapping" $btnSQLSAPWSMapping.Enabled = $True $btnSQLSAPWSMapping.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint if exists (select * from sysobjects where name='USREXTID') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='USREXTID' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''USREXTID'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''USREXTID'' and schema_name(uid)='''+@schema+''') select ''DB''=db_name(), EXTID, BNAME, STATUS, CNAME, CDATE, CTIME, TYPE, ''TCode Desc''=''SM30-VUSREXTID-ExtTyp NT'' from USREXTID where TYPE=''NT'' else select ''DB''=db_name(), EXTID, BNAME, STATUS, CNAME, CDATE, CTIME, TYPE, ''TCode Desc''=''SM30-VUSREXTID-ExtTyp NT'' from ['+@schema+'].USREXTID where TYPE=''NT''')"}) $tabSQLSAP.Controls.Add($btnSQLSAPWSMapping) $btnSQLSAPJobsRunning = new-object System.Windows.Forms.Button $btnSQLSAPJobsRunning.Location = new-object System.Drawing.Size(240,0) $btnSQLSAPJobsRunning.Size = new-object System.Drawing.Size(120,20) $btnSQLSAPJobsRunning.Text = "Jobs Running" $btnSQLSAPJobsRunning.Enabled = $True $btnSQLSAPJobsRunning.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint if exists (select * from sysobjects where name='TBTCO') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='TBTCO' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''TBTCO'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''TBTCO'' and schema_name(uid)='''+@schema+''') select NTPID=substring(convert(char(6),o.WPPROCID),1,6),SYSTEM=substring(o.BTCSYSREAX,1,12),USERNAME=substring(o.SDLUNAME,1,10),o.JOBNAME,STARTED=substring(convert(char(8), STRTDATE),5,2)+''-''+substring(convert(char(8), STRTDATE),7,2)+'' ''+substring(convert(char(6),STRTTIME),1,2)+'':''+substring(convert(char(6),STRTTIME),3,2),''Averg%''=isnull(convert(char(6),right(space(6)+convert(varchar(14),100*datediff(minute,convert(datetime,substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),5,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),7,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),1,4)+'' ''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),1,2)+'':''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),3,2)),getdate())/nullif(a.[AVG],0))+''%'',6)),'' --''),''AvMin''=isnull(convert(char(5),right(space(5)+convert(varchar(10),a.[AVG]),5)),'' --''),''#MIN''=convert(varchar(6),right(space(6)+convert(varchar(10),datediff(minute,convert(datetime,substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),5,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),7,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),1,4)+'' ''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),1,2)+'':''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),3,2)),getdate())),6)) FROM TBTCO o left outer join (select ''AVG''=avg(datediff(minute,convert(datetime,substring(convert(char(8),f.STRTDATE),5,2)+''-''+substring(convert(char(8), f.STRTDATE),7,2)+''-''+substring(convert(char(8), f.STRTDATE),1,4)+'' ''+substring(convert(char(6),f.STRTTIME),1,2)+'':''+substring(convert(char(6),f.STRTTIME),3,2)),convert(datetime,substring(convert(char(8),f.ENDDATE),5,2)+''-''+substring(convert(char(8), f.ENDDATE),7,2)+''-''+substring(convert(char(8), f.ENDDATE),1,4)+'' ''+substring(convert(char(6),f.ENDTIME),1,2)+'':''+substring(convert(char(6),f.ENDTIME),3,2)))), f.JOBNAME, ''#MIN''=datediff(minute,convert(datetime,substring(convert(char(8),r.STRTDATE),5,2)+''-''+substring(convert(char(8), r.STRTDATE),7,2)+''-''+substring(convert(char(8), r.STRTDATE),1,4)+'' ''+substring(convert(char(6),r.STRTTIME),1,2)+'':''+substring(convert(char(6),r.STRTTIME),3,2)),getdate()) FROM TBTCO f inner join TBTCO r on r.STATUS=''R'' and r.JOBNAME=f.JOBNAME and f.STATUS=''F'' and nullif(f.STRTDATE,'''') is not null and nullif(f.STRTTIME,'''') is not null and nullif(f.ENDDATE,'''') is not null and nullif(f.ENDTIME,'''') is not null group by f.JOBNAME, datediff(minute,convert(datetime,substring(convert(char(8),r.STRTDATE),5,2)+''-''+substring(convert(char(8), r.STRTDATE),7,2)+''-''+substring(convert(char(8), r.STRTDATE),1,4)+'' ''+substring(convert(char(6),r.STRTTIME),1,2)+'':''+substring(convert(char(6),r.STRTTIME),3,2)),getdate())) a on a.JOBNAME=o.JOBNAME and a.[#MIN] is not null and convert(char(5),datediff(minute,convert(datetime,substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),5,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),7,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),1,4)+'' ''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),1,2)+'':''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),3,2)),getdate())) is not null and a.[#MIN]=convert(varchar(6),datediff(minute,convert(datetime,substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),5,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),7,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),1,4)+'' ''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),1,2)+'':''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),3,2)),getdate())) WHERE o.STATUS=''R'' order by o.STRTDATE, o.STRTTIME else select NTPID=substring(convert(char(6),o.WPPROCID),1,6), SYSTEM=substring(o.BTCSYSREAX,1,12), USERNAME=substring(o.SDLUNAME,1,10), o.JOBNAME,STARTED=substring(convert(char(8), STRTDATE),5,2)+''-''+substring(convert(char(8), STRTDATE),7,2)+'' ''+substring(convert(char(6),STRTTIME),1,2)+'':''+substring(convert(char(6),STRTTIME),3,2),''Averg%''=isnull(convert(char(6),right(space(6)+convert(varchar(14),100*datediff(minute,convert(datetime,substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),5,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),7,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),1,4)+'' ''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),1,2)+'':''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),3,2)),getdate())/nullif(a.[AVG],0))+''%'',6)),'' --''),''AvMin''=isnull(convert(char(5),right(space(5)+convert(varchar(10),a.[AVG]),5)),'' --''),''#MIN''=convert(varchar(6),right(space(6)+convert(varchar(10),datediff(minute,convert(datetime,substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),5,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),7,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),1,4)+'' ''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),1,2)+'':''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),3,2)),getdate())),6)) FROM '+@schema+'.TBTCO o left outer join (select ''AVG''=avg(datediff(minute,convert(datetime,substring(convert(char(8),f.STRTDATE),5,2)+''-''+substring(convert(char(8), f.STRTDATE),7,2)+''-''+substring(convert(char(8), f.STRTDATE),1,4)+'' ''+substring(convert(char(6),f.STRTTIME),1,2)+'':''+substring(convert(char(6),f.STRTTIME),3,2)),convert(datetime,substring(convert(char(8),f.ENDDATE),5,2)+''-''+substring(convert(char(8), f.ENDDATE),7,2)+''-''+substring(convert(char(8), f.ENDDATE),1,4)+'' ''+substring(convert(char(6),f.ENDTIME),1,2)+'':''+substring(convert(char(6),f.ENDTIME),3,2)))), f.JOBNAME, ''#MIN''=datediff(minute,convert(datetime,substring(convert(char(8),r.STRTDATE),5,2)+''-''+substring(convert(char(8), r.STRTDATE),7,2)+''-''+substring(convert(char(8), r.STRTDATE),1,4)+'' ''+substring(convert(char(6),r.STRTTIME),1,2)+'':''+substring(convert(char(6),r.STRTTIME),3,2)),getdate()) FROM '+@schema+'.TBTCO f inner join '+@schema+'.TBTCO r on r.STATUS=''R'' and r.JOBNAME=f.JOBNAME and f.STATUS=''F'' and nullif(f.STRTDATE,'''') is not null and nullif(f.STRTTIME,'''') is not null and nullif(f.ENDDATE,'''') is not null and nullif(f.ENDTIME,'''') is not null group by f.JOBNAME, datediff(minute,convert(datetime,substring(convert(char(8),r.STRTDATE),5,2)+''-''+substring(convert(char(8), r.STRTDATE),7,2)+''-''+substring(convert(char(8), r.STRTDATE),1,4)+'' ''+substring(convert(char(6),r.STRTTIME),1,2)+'':''+substring(convert(char(6),r.STRTTIME),3,2)),getdate())) a on a.JOBNAME=o.JOBNAME and a.[#MIN] is not null and convert(char(5),datediff(minute,convert(datetime,substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),5,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),7,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),1,4)+'' ''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),1,2)+'':''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),3,2)),getdate())) is not null and a.[#MIN]=convert(varchar(6),datediff(minute,convert(datetime,substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),5,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),7,2)+''-''+substring(convert(char(8),nullif(rtrim(o.STRTDATE),'''')),1,4)+'' ''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),1,2)+'':''+substring(convert(char(6),nullif(rtrim(o.STRTTIME),'''')),3,2)),getdate())) WHERE o.STATUS=''R'' order by o.STRTDATE, o.STRTTIME') "}) $tabSQLSAP.Controls.Add($btnSQLSAPJobsRunning) $btnSQLSAPJobsFailed = new-object System.Windows.Forms.Button $btnSQLSAPJobsFailed.Location = new-object System.Drawing.Size(240,20) $btnSQLSAPJobsFailed.Size = new-object System.Drawing.Size(120,20) $btnSQLSAPJobsFailed.Text = "Jobs Failed" $btnSQLSAPJobsFailed.Enabled = $True $btnSQLSAPJobsFailed.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @pastminstring nvarchar(20) set @pastminstring='1440' if exists (select * from sysobjects where name='TBTCO') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='TBTCO' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''TBTCO'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''TBTCO'' and schema_name(uid)='''+@schema+''') select SYSTEM=substring(BTCSYSREAX,1,12),USERNAME=substring(SDLUNAME,1,10),JOBNAME,START=substring(convert(char(8), STRTDATE),5,2)+''-''+substring(convert(char(8), STRTDATE),7,2)+'' ''+substring(convert(char(6),STRTTIME),1,2)+'':''+substring(convert(char(6),STRTTIME),3,2),DONE=substring(convert(char(8), ENDDATE),5,2)+''-''+substring(convert(char(8), ENDDATE),7,2)+'' ''+substring(convert(char(6),ENDTIME),1,2)+'':''+substring(convert(char(6),ENDTIME),3,2) FROM TBTCO WHERE STATUS=''A'' and datediff(minute,substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),5,2)+''-''+substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),7,2)+''-''+substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),1,4)+'' ''+substring(convert(char(6),isnull(nullif(isnull(nullif(ENDTIME,''''),STRTTIME),''''),SDLSTRTTM)),1,2)+'':''+substring(convert(char(6),isnull(nullif(isnull(nullif(ENDTIME,''''),STRTTIME),''''),SDLSTRTTM)),3,2),getdate()) < '+@pastminstring+' order by ENDDATE, ENDTIME else select SYSTEM=substring(BTCSYSREAX,1,12),USERNAME=substring(SDLUNAME,1,10),JOBNAME,START=substring(convert(char(8), STRTDATE),5,2)+''-''+substring(convert(char(8), STRTDATE),7,2)+'' ''+substring(convert(char(6),STRTTIME),1,2)+'':''+substring(convert(char(6),STRTTIME),3,2),DONE=substring(convert(char(8), ENDDATE),5,2)+''-''+substring(convert(char(8), ENDDATE),7,2)+'' ''+substring(convert(char(6),ENDTIME),1,2)+'':''+substring(convert(char(6),ENDTIME),3,2) FROM ['+@schema+'].TBTCO WHERE STATUS=''A'' and datediff(minute,substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),5,2)+''-''+substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),7,2)+''-''+substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),1,4)+'' ''+substring(convert(char(6),isnull(nullif(isnull(nullif(ENDTIME,''''),STRTTIME),''''),SDLSTRTTM)),1,2)+'':''+substring(convert(char(6),isnull(nullif(isnull(nullif(ENDTIME,''''),STRTTIME),''''),SDLSTRTTM)),3,2),getdate()) < '+@pastminstring+' order by ENDDATE, ENDTIME')"}) $tabSQLSAP.Controls.Add($btnSQLSAPJobsFailed) $btnSQLSAPJobsComplete = new-object System.Windows.Forms.Button $btnSQLSAPJobsComplete.Location = new-object System.Drawing.Size(240,40) $btnSQLSAPJobsComplete.Size = new-object System.Drawing.Size(120,20) $btnSQLSAPJobsComplete.Text = "Jobs Complete" $btnSQLSAPJobsComplete.Enabled = $True $btnSQLSAPJobsComplete.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @pastminstring nvarchar(20) set @pastminstring='1440' if exists (select * from sysobjects where name='TBTCO') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='TBTCO' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''TBTCO'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''TBTCO'' and schema_name(uid)='''+@schema+''') select SYSTEM=substring(BTCSYSREAX,1,12),USERNAME=substring(SDLUNAME,1,10),JOBNAME,START=substring(convert(char(8), STRTDATE),5,2)+''-''+substring(convert(char(8), STRTDATE),7,2)+'' ''+substring(convert(char(6),STRTTIME),1,2)+'':''+substring(convert(char(6),STRTTIME),3,2),DONE=substring(convert(char(8), ENDDATE),5,2)+''-''+substring(convert(char(8), ENDDATE),7,2)+'' ''+substring(convert(char(6),ENDTIME),1,2)+'':''+substring(convert(char(6),ENDTIME),3,2) FROM TBTCO WHERE STATUS=''F'' and JOBNAME<>''RDDIMPDP'' and JOBNAME not like ''INVOICE_00%'' and JOBNAME not in (select JOBNAME from TBTCS where PERIODIC=''X'' and (PRDMINS<>''00'' or PRDHOURS<>''00'')) and datediff(minute,substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),5,2)+''-''+substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),7,2)+''-''+substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),1,4)+'' ''+substring(convert(char(6),isnull(nullif(isnull(nullif(ENDTIME,''''),STRTTIME),''''),SDLSTRTTM)),1,2)+'':''+substring(convert(char(6),isnull(nullif(isnull(nullif(ENDTIME,''''),STRTTIME),''''),SDLSTRTTM)),3,2),getdate()) < '+@pastminstring+' order by ENDDATE, ENDTIME else select SYSTEM=substring(BTCSYSREAX,1,12),USERNAME=substring(SDLUNAME,1,10),JOBNAME,START=substring(convert(char(8), STRTDATE),5,2)+''-''+substring(convert(char(8), STRTDATE),7,2)+'' ''+substring(convert(char(6),STRTTIME),1,2)+'':''+substring(convert(char(6),STRTTIME),3,2),DONE=substring(convert(char(8), ENDDATE),5,2)+''-''+substring(convert(char(8), ENDDATE),7,2)+'' ''+substring(convert(char(6),ENDTIME),1,2)+'':''+substring(convert(char(6),ENDTIME),3,2) FROM ['+@schema+'].TBTCO WHERE STATUS=''F'' and JOBNAME<>''RDDIMPDP'' and JOBNAME not like ''INVOICE_00%'' and JOBNAME not in (select JOBNAME from ['+@schema+'].TBTCS where PERIODIC=''X'' and (PRDMINS<>''00'' or PRDHOURS<>''00'')) and datediff(minute,substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),5,2)+''-''+substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),7,2)+''-''+substring(convert(char(8), isnull(nullif(isnull(nullif(ENDDATE,''''),STRTDATE),''''),SDLSTRTDT)),1,4)+'' ''+substring(convert(char(6),isnull(nullif(isnull(nullif(ENDTIME,''''),STRTTIME),''''),SDLSTRTTM)),1,2)+'':''+substring(convert(char(6),isnull(nullif(isnull(nullif(ENDTIME,''''),STRTTIME),''''),SDLSTRTTM)),3,2),getdate()) < '+@pastminstring+' order by ENDDATE, ENDTIME')"}) $tabSQLSAP.Controls.Add($btnSQLSAPJobsComplete) $btnSQLSAPAppServers = new-object System.Windows.Forms.Button $btnSQLSAPAppServers.Location = new-object System.Drawing.Size(0,0) $btnSQLSAPAppServers.Size = new-object System.Drawing.Size(120,20) $btnSQLSAPAppServers.Text = "App Servers" $btnSQLSAPAppServers.Enabled = $True $btnSQLSAPAppServers.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'SQLServer'=@@servername, db_name(dbid) DB, rtrim(convert(nvarchar(64),hostname)) AppServer, 'Login_Time'=min(login_time), spidcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid), hostname order by @@servername, db_name(dbid), hostname"}) $tabSQLSAP.Controls.Add($btnSQLSAPAppServers) $btnSQLSAPWPList = new-object System.Windows.Forms.Button $btnSQLSAPWPList.Location = new-object System.Drawing.Size(0,20) $btnSQLSAPWPList.Size = new-object System.Drawing.Size(120,20) $btnSQLSAPWPList.Text = "WP List" $btnSQLSAPWPList.Enabled = $True $btnSQLSAPWPList.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select db_name(dbid) DB , rtrim(convert(nvarchar(64),hostname)) AppServer , substring(program_name,4,2) 'WP' , isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(convert(char(3),substring(program_name,3,1)),'D'),'DIA'),'B'),'BTC'),'S'),'SPO'),'E'),'ENQ'),'U'),'UPD'),'2'),'UP2') 'Type' , convert(nvarchar(32),hostprocess) NTPID , count(*) [spidcount] , 'Login_Time'=min(login_time) , @@SERVERNAME 'SQLServer' from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid), substring(program_name,4,2) ,isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(convert(char(3),substring(program_name,3,1)),'D'),'DIA'),'B'),'BTC'),'S'),'SPO'),'E'),'ENQ'),'U'),'UPD'),'2'),'UP2') ,convert(nvarchar(64),hostname) ,convert(nvarchar(32),hostprocess) order by db_name(dbid), convert(nvarchar(64),hostname), substring(program_name,4,2), convert(nvarchar(32),hostprocess)"}) $tabSQLSAP.Controls.Add($btnSQLSAPWPList) $btnSQLSAPShortDumps = new-object System.Windows.Forms.Button $btnSQLSAPShortDumps.Location = new-object System.Drawing.Size(120,0) $btnSQLSAPShortDumps.Size = new-object System.Drawing.Size(120,20) $btnSQLSAPShortDumps.Text = "ST22 ShortDumps" $btnSQLSAPShortDumps.Enabled = $True $btnSQLSAPShortDumps.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint if exists (select * from sysobjects where name='SNAP') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='SNAP' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) declare @i bigint, @dt char(8), @tm char(6) set @i=-2880 set @dt=convert(char(4),datepart(year,dateadd(minute,@i,getdate())))+right('0'+convert(varchar(2),datepart(month,dateadd(minute,@i,getdate()))),2)+right('0'+convert(varchar(2),datepart(day,dateadd(minute,@i,getdate()))),2) set @tm=right('0'+convert(varchar(2),datepart(hour,dateadd(minute,@i,getdate()))),2)+right('0'+convert(varchar(2),datepart(minute,dateadd(minute,@i,getdate()))),2)+right('0'+convert(varchar(2),datepart(second,dateadd(minute,@i,getdate()))),2) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''SNAP'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''SNAP'' and schema_name(uid)='''+@schema+''') select convert(datetime,substring(DATUM,5,2)+''/''+substring(DATUM,7,2)+''/''+substring(DATUM,1,4)+'' ''+substring(UZEIT,1,2)+'':''+substring(UZEIT,3,2)+'':''+substring(UZEIT,5,2)) [DATE], AHOST Server ,MANDT Cli,UNAME [User],convert(char(30),substring(FLIST+FLIST02,6,convert(int,(substring(FLIST+FLIST02,3,3))))) ShortDump from dbo.SNAP where SEQNO=''000'' and (DATUM > '''+@dt+''' or DATUM = '''+@dt+''' and UZEIT >= '''+@tm+''') order by DATUM desc, UZEIT desc else select convert(datetime,substring(DATUM,5,2)+''/''+substring(DATUM,7,2)+''/''+substring(DATUM,1,4)+'' ''+substring(UZEIT,1,2)+'':''+substring(UZEIT,3,2)+'':''+substring(UZEIT,5,2)) [DATE], AHOST Server ,MANDT Cli,UNAME [User],convert(char(30),substring(FLIST+FLIST02,6,convert(int,(substring(FLIST+FLIST02,3,3))))) ShortDump from ['+@schema+'].SNAP where SEQNO=''000'' and (DATUM > '''+@dt+''' or DATUM = '''+@dt+''' and UZEIT >= '''+@tm+''') order by DATUM desc,UZEIT desc')"}) $tabSQLSAP.Controls.Add($btnSQLSAPShortDumps) $btnSQLSAPUpdateFailures = new-object System.Windows.Forms.Button $btnSQLSAPUpdateFailures.Location = new-object System.Drawing.Size(120,20) $btnSQLSAPUpdateFailures.Size = new-object System.Drawing.Size(120,20) $btnSQLSAPUpdateFailures.Text = "Update Failures" $btnSQLSAPUpdateFailures.Enabled = $True $btnSQLSAPUpdateFailures.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint if exists (select * from sysobjects where name='VBHDR') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='VBHDR' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''VBHDR'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''VBHDR'' and schema_name(uid)='''+@schema+''') select * from (select ''DateTime''=substring(h.VBDATE,5,2)+''/''+substring(h.VBDATE,7,2)+''/''+left(h.VBDATE,4)+'' ''+substring(h.VBDATE,9,2)+'':''+substring(h.VBDATE,11,2)+'':''+right(h.VBDATE,2),''Server''=convert(varchar(16),isnull(nullif(h.VBNAME,''''),h.VBCLINAME)),''Clnt''=h.VBMANDT,''User''=h.VBUSR,''TCode''=h.VBTCODE,''State''=''Canceled: ''+isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(convert(varchar(16),h.VBRC),''24''),''Error''),''25''),''Error''),''9''),''Error''),''123''),''Error''),''12''),''Stopped(NoRetry)''),''21''),''Enque deleted''),''200''),''Failure V2 Part'') from VBHDR h left outer join VBERROR e on h.VBKEY=e.VBKEY where h.VBRC between 2 and 201 and h.VBSTATE between 1 and 255 union select ''DateTime''=substring(VBDATE,5,2)+''/''+substring(VBDATE,7,2)+''/''+left(VBDATE,4)+'' ''+substring(VBDATE,9,2)+'':''+substring(VBDATE,11,2)+'':''+right(VBDATE,2),''Server''=convert(varchar(16),isnull(nullif(VBNAME,''''),VBCLINAME)),''Clnt''=VBMANDT,''User''=VBUSR,''TCode''=VBTCODE,''State''=convert(varchar(11),isnull(nullif(''ToBeUpdated''+convert(varchar(3),VBRC)+convert(varchar(3),VBSTATE),''ToBeUpdated255255''),''Initial'')) from VBHDR where VBRC between 253 and 255 and VBSTATE between 254 and 255 union select ''DateTime''=substring(VBDATE,5,2)+''/''+substring(VBDATE,7,2)+''/''+left(VBDATE,4)+'' ''+substring(VBDATE,9,2)+'':''+substring(VBDATE,11,2)+'':''+right(VBDATE,2),''Server''=convert(varchar(16),isnull(nullif(VBNAME,''''),VBCLINAME)),''Clnt''=VBMANDT,''User''=VBUSR,''TCode''=VBTCODE,''State''=isnull(isnull(left(''V''+convert(varchar(1),VBSTATE)+'' processed''+nullif(convert(varchar(3),VBRC),''249'')+nullif(convert(varchar(3),VBRC),''252'')+nullif(convert(varchar(3),VBRC),''247''),12),left(''Started''+nullif(convert(varchar(3),VBRC),''247''),7)),''Processed'') from VBHDR where VBRC not between 2 and 201 and VBSTATE between 1 and 2) a order by convert(datetime,[DateTime]) desc else select * from (select ''DateTime''=substring(h.VBDATE,5,2)+''/''+substring(h.VBDATE,7,2)+''/''+left(h.VBDATE,4)+'' ''+substring(h.VBDATE,9,2)+'':''+substring(h.VBDATE,11,2)+'':''+right(h.VBDATE,2),''Server''=convert(varchar(16),isnull(nullif(h.VBNAME,''''),h.VBCLINAME)),''Clnt''=h.VBMANDT,''User''=h.VBUSR,''TCode''=h.VBTCODE,''State''=''Canceled: ''+isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(convert(varchar(16),h.VBRC),''24''),''Error''),''25''),''Error''),''9''),''Error''),''123''),''Error''),''12''),''Stopped(NoRetry)''),''21''),''Enque deleted''),''200''),''Failure V2 Part'') from ['+@schema+'].VBHDR h left outer join ['+@schema+'].VBERROR e on h.VBKEY=e.VBKEY where h.VBRC between 2 and 201 and h.VBSTATE between 1 and 255 union select ''DateTime''=substring(VBDATE,5,2)+''/''+substring(VBDATE,7,2)+''/''+left(VBDATE,4)+'' ''+substring(VBDATE,9,2)+'':''+substring(VBDATE,11,2)+'':''+right(VBDATE,2),''Server''=convert(varchar(16),isnull(nullif(VBNAME,''''),VBCLINAME)),''Clnt''=VBMANDT,''User''=VBUSR,''TCode''=VBTCODE,''State''=convert(varchar(11),isnull(nullif(''ToBeUpdated''+convert(varchar(3),VBRC)+convert(varchar(3),VBSTATE),''ToBeUpdated255255''),''Initial'')) from ['+@schema+'].VBHDR where VBRC between 253 and 255 and VBSTATE between 254 and 255 union select ''DateTime''=substring(VBDATE,5,2)+''/''+substring(VBDATE,7,2)+''/''+left(VBDATE,4)+'' ''+substring(VBDATE,9,2)+'':''+substring(VBDATE,11,2)+'':''+right(VBDATE,2),''Server''=convert(varchar(16),isnull(nullif(VBNAME,''''),VBCLINAME)),''Clnt''=VBMANDT,''User''=VBUSR,''TCode''=VBTCODE,''State''=isnull(isnull(left(''V''+convert(varchar(1),VBSTATE)+'' processed''+nullif(convert(varchar(3),VBRC),''249'')+nullif(convert(varchar(3),VBRC),''252'')+nullif(convert(varchar(3),VBRC),''247''),12),left(''Started''+nullif(convert(varchar(3),VBRC),''247''),7)),''Processed'') from ['+@schema+'].VBHDR where VBRC not between 2 and 201 and VBSTATE between 1 and 2) a order by convert(datetime,[DateTime]) desc')"}) $tabSQLSAP.Controls.Add($btnSQLSAPUpdateFailures) $btnSQLListDrainGroups = new-object System.Windows.Forms.Button $btnSQLListDrainGroups.Location = new-object System.Drawing.Size(0,60) $btnSQLListDrainGroups.Size = new-object System.Drawing.Size(120,20) $btnSQLListDrainGroups.Text = "List Drain Groups" $btnSQLListDrainGroups.Enabled = $True $btnSQLListDrainGroups.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint if exists (select * from sysobjects where name='RZLLITAB') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='RZLLITAB' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''RZLLITAB'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''RZLLITAB'' and schema_name(uid)='''+@schema+''') select ''TCode''=''SMLG'', ''Group''=CLASSNAME, ''Server''=APPLSERVER from RZLLITAB where GROUPTYPE <> ''S'' union select ''TCode''=''RZ12'', ''Group''=CLASSNAME, ''Server''=APPLSERVER from RZLLITAB where GROUPTYPE = ''S'' union select ''TCode''=''SM14'', ''Group''=SERVERGRP, ''Server''=SERVERNAME from APSRV union select ''TCode''=''SM61'', ''Group''=a.GRPNAME, ''Server''=b.APPSRVNAME from TSRVGRP a inner join TSRVLST b on a.GUID=b.GUID union select ''TCode''=''SPAD'', ''Group''=SERVER, ''Server''=MAPPEDTO from TSPSV where MAPPEDTO<>'''' union select ''TCode''=''SPAD'', ''Group''=SERVER, ''Server''=ALTSRV from TSPSV where ALTSRV<>'''' order by ''TCode'', ''Group'',''Server'' else select ''TCode''=''SMLG'', ''Group''=CLASSNAME, ''Server''=APPLSERVER from ['+@schema+'].RZLLITAB where GROUPTYPE <> ''S'' union select ''TCode''=''RZ12'', ''Group''=CLASSNAME, ''Server''=APPLSERVER from ['+@schema+'].RZLLITAB where GROUPTYPE = ''S'' union select ''TCode''=''SM14'', ''Group''=SERVERGRP, ''Server''=SERVERNAME from ['+@schema+'].APSRV union select ''TCode''=''SM61'', ''Group''=a.GRPNAME, ''Server''=b.APPSRVNAME from ['+@schema+'].TSRVGRP a inner join ['+@schema+'].TSRVLST b on a.GUID=b.GUID union select ''TCode''=''SPAD'', ''Group''=SERVER, ''Server''=MAPPEDTO from ['+@schema+'].TSPSV where MAPPEDTO<>'''' union select ''TCode''=''SPAD'', ''Group''=SERVER, ''Server''=ALTSRV from ['+@schema+'].TSPSV where ALTSRV<>'''' order by ''TCode'', ''Group'',''Server''')"}) $tabSQLSAP.Controls.Add($btnSQLListDrainGroups) $btnSQLSM59 = new-object System.Windows.Forms.Button $btnSQLSM59.Location = new-object System.Drawing.Size(480,0) $btnSQLSM59.Size = new-object System.Drawing.Size(120,20) $btnSQLSM59.Text = "SM59" $btnSQLSM59.Enabled = $True $btnSQLSM59.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @exec nvarchar(MAX) if exists (select * from sysobjects where name='RFCDES') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='RFCDES' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''RFCDES'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''RFCDES'' and schema_name(uid)='''+@schema+''') select RFCDEST, RFCTYPE,''Server''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,H=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,H=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''Gateway''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,G=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,G=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''Service''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,g=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,g=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''Client''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,M=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,M=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''User''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,U=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,U=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''Grp/Prog''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,N=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,N=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''DestText''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,d=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,d=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''DB''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,I=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,I=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''SysNum''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,S=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,S=%'','',''+RFCOPTIONS),0)-1)+'','')-1) from RFCDES order by RFCTYPE, RFCDEST else select RFCDEST, RFCTYPE,''Server''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,H=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,H=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''Gateway''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,G=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,G=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''Service''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,g=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,g=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''Client''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,M=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,M=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''User''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,U=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,U=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''Grp/Prog''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,N=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,N=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''DestText''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,d=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,d=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''DB''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,I=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,I=%'','',''+RFCOPTIONS),0)-1)+'','')-1),''SysNum''=left(right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,S=%'','',''+RFCOPTIONS),0)-1),charindex('','',right(RFCOPTIONS,len(RFCOPTIONS)-nullif(Patindex(''%,S=%'','',''+RFCOPTIONS),0)-1)+'','')-1) from ['+@schema+'].RFCDES order by RFCTYPE, RFCDEST') "}) $tabSQLSAP.Controls.Add($btnSQLSM59) $btnSQLSTRUST = new-object System.Windows.Forms.Button $btnSQLSTRUST.Location = new-object System.Drawing.Size(480,20) $btnSQLSTRUST.Size = new-object System.Drawing.Size(120,20) $btnSQLSTRUST.Text = "STRUST" $btnSQLSTRUST.Enabled = $True $btnSQLSTRUST.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @exec nvarchar(MAX) if exists (select * from sysobjects where name='SSF_PSE_H') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='SSF_PSE_H' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''SSF_PSE_H'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''SSF_PSE_H'' and schema_name(uid)='''+@schema+''') select NAME,HOST,NR=INSTANCEID,FILENAME,CDATE,CTIME,MDATE,MTIME,ID,SIGNED,DATALEN from SSF_PSE_H where upper(FILENAME) = upper(''SAPSSLS.pse'') else select NAME,HOST,NR=INSTANCEID,FILENAME,CDATE,CTIME,MDATE,MTIME,ID,SIGNED,DATALEN from ['+@schema+'].SSF_PSE_H where upper(FILENAME) = upper(''SAPSSLS.pse'') ')"}) $tabSQLSAP.Controls.Add($btnSQLSTRUST) $btnSQLLockedUsers = new-object System.Windows.Forms.Button $btnSQLLockedUsers.Location = new-object System.Drawing.Size(360,40) $btnSQLLockedUsers.Size = new-object System.Drawing.Size(120,20) $btnSQLLockedUsers.Text = "LockedUsers" $btnSQLLockedUsers.Enabled = $True $btnSQLLockedUsers.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @exec nvarchar(MAX) if exists (select * from sysobjects where name='USR02') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='USR02' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''USR02'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''USR02'' and schema_name(uid)='''+@schema+''') select BNAME, MANDT, UFLAG, LOCNT, ACCNT, GLTGV, GLTGB, ANAME, ERDAT, TRDAT, LTIME, BCDA1 from USR02 where UFLAG<>0 else select BNAME, MANDT, UFLAG, LOCNT, ACCNT, GLTGV, GLTGB, ANAME, ERDAT, TRDAT, LTIME, BCDA1 from ['+@schema+'].USR02 where UFLAG<>0 ') --update ???.USR02 set UFLAG=0 where BNAME='??????'"}) $tabSQLSAP.Controls.Add($btnSQLLockedUsers) $btnSQLUSRQRUNLOCK = new-object System.Windows.Forms.Button $btnSQLUSRQRUNLOCK.Location = new-object System.Drawing.Size(360,60) $btnSQLUSRQRUNLOCK.Size = new-object System.Drawing.Size(120,20) $btnSQLUSRQRUNLOCK.Text = "Usr QR Unlock" $btnSQLUSRQRUNLOCK.Enabled = $True $btnSQLUSRQRUNLOCK.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @exec nvarchar(MAX) if exists (select * from sysobjects where name='ZEAS_USRQRUNLOCK') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='ZEAS_USRQRUNLOCK' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''ZEAS_USRQRUNLOCK'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''ZEAS_USRQRUNLOCK'' and schema_name(uid)='''+@schema+''') select * from ZEAS_USRQRUNLOCK else select * from ['+@schema+'].ZEAS_USRQRUNLOCK ') --update ???.USR02 set UFLAG=0 where BNAME='??????'"}) $tabSQLSAP.Controls.Add($btnSQLUSRQRUNLOCK) $btnSQLSupportPacks = new-object System.Windows.Forms.Button $btnSQLSupportPacks.Location = new-object System.Drawing.Size(0,40) $btnSQLSupportPacks.Size = new-object System.Drawing.Size(120,20) $btnSQLSupportPacks.Text = "SupportPacks" $btnSQLSupportPacks.Enabled = $True $btnSQLSupportPacks.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @exec nvarchar(MAX) if exists (select * from sysobjects where name='PAT03') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='PAT03' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''PAT03'') select ''ErrorMessage''=''SAP database not found'' else --if not exists (select * from sysobjects where name=''PAT03'' and schema_name(uid)='''+@schema+''') begin set nocount on select a.COMPONENT, a.COMP_REL, a.TO_REL, a.PATCH, a.SHORT_TEXT, a.STATUS, a.CONFIRMED, a.RESPONSIBL, a.IMPLE_DATE, a.IMPLE_TIME into #tempPATCH from ['+@schema+'].PAT03 a inner join (select COMPONENT, IMPLE_DATE=max(IMPLE_DATE) from ['+@schema+'].PAT03 where STATUS=''I'' group by COMPONENT) b on a.COMPONENT=b.COMPONENT and a.IMPLE_DATE=b.IMPLE_DATE where a.COMPONENT<>'''' and a.STATUS=''I'' delete #tempPATCH from #tempPATCH a inner join (select COMPONENT, COMP_REL=max(COMP_REL) from #tempPATCH group by COMPONENT) b on a.COMPONENT=b.COMPONENT and a.COMP_REL<>b.COMP_REL delete #tempPATCH from #tempPATCH a inner join (select COMPONENT, TO_REL=max(TO_REL) from #tempPATCH group by COMPONENT) b on a.COMPONENT=b.COMPONENT and a.TO_REL<>b.TO_REL set nocount off select * from #tempPATCH a order by a.COMPONENT drop table #tempPATCH end ') "}) $tabSQLSAP.Controls.Add($btnSQLSupportPacks) $btnSQLCIF = new-object System.Windows.Forms.Button $btnSQLCIF.Location = new-object System.Drawing.Size(120,40) $btnSQLCIF.Size = new-object System.Drawing.Size(120,20) $btnSQLCIF.Text = "CIF SMQ1/2 SM58" $btnSQLCIF.Enabled = $True $btnSQLCIF.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @exec nvarchar(MAX) if exists (select * from sysobjects where name='TRFCQOUT') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='TRFCQOUT' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''TRFCQOUT'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''TRFCQOUT'' and schema_name(uid)='''+@schema+''') select ''TCode''=''SMQ1'', ''DB''=db_name(), a.QNAME, a.DEST, ''STATE''=a.QSTATE, ''USER''=a.QRFCUSER, ''NAME''=a.QRFCFNAM, ''DATTIM''=convert(datetime,substring(a.QRFCDATUM,5,2)+''-''+right(a.QRFCDATUM,2)+''-''+left(a.QRFCDATUM,4)+'' ''+stuff(left(a.QRFCUZEIT,4),3,0,'':'')), ''MESSAGE''=a.ERRMESS, AppServer=b.ARFCRHOST, ''SQLServer''=@@servername from TRFCQOUT a left outer join ARFCSSTATE b on a.ARFCIPID=b.ARFCIPID and a.ARFCPID=b.ARFCPID and a.ARFCTIME=b.ARFCTIME and a.ARFCTIDCNT=b.ARFCTIDCNT where a.QSTATE in (''CPICERR'', ''SYSFAIL'') and a.DEST not in (''VERTEX'') union select ''TCode''=''SMQ2'', ''DB''=db_name(), a.QNAME, a.DEST, ''STATE''=a.QSTATE, ''USER''=a.QRFCUSER, ''NAME''=a.QRFCFNAM, ''DATTIM''=convert(datetime,substring(a.QRFCDATUM,5,2)+''-''+right(a.QRFCDATUM,2)+''-''+left(a.QRFCDATUM,4)+'' ''+stuff(left(a.QRFCUZEIT,4),3,0,'':'')), ''MESSAGE''=a.ERRMESS, AppServer=b.ARFCRHOST, ''SQLServer''=@@servername from TRFCQIN a left outer join ARFCSSTATE b on a.ARFCIPID=b.ARFCIPID and a.ARFCPID=b.ARFCPID and a.ARFCTIME=b.ARFCTIME and a.ARFCTIDCNT=b.ARFCTIDCNT where a.QSTATE not in (''READY'',''RUNNING'') union select ''TCode''=''SM58'', ''DB''=db_name(), ''QNAME''=''N/A'', a.ARFCDEST, ''STATE''=a.ARFCSTATE, ''USER''=a.ARFCUSER, ''NAME''=a.ARFCFNAM, ''DATTIM''=convert(datetime,substring(a.ARFCDATUM,5,2)+''-''+right(a.ARFCDATUM,2)+''-''+left(a.ARFCDATUM,4)+'' ''+stuff(left(a.ARFCUZEIT,4),3,0,'':'')), ''MESSAGE''=a.ARFCMSG, AppServer=a.ARFCRHOST, ''SQLServer''=@@servername from ARFCSSTATE a left outer join TRFCQOUT b on a.ARFCIPID=b.ARFCIPID and a.ARFCPID=b.ARFCPID and a.ARFCTIME=b.ARFCTIME and a.ARFCTIDCNT=b.ARFCTIDCNT and a.ARFCDEST=b.DEST and a.ARFCSTATE=b.QSTATE and a.ARFCUSER=b.QRFCUSER and a.ARFCFNAM=b.QRFCFNAM where (a.ARFCSTATE like ''%ERR%'' or a.ARFCSTATE like ''%FAIL%'') and a.ARFCMSG not like ''%You cannot create a customs shipment%'' and b.DEST is null order by ''DATTIM'' else select ''TCode''=''SMQ1'', ''DB''=db_name(), a.QNAME, a.DEST, ''STATE''=a.QSTATE, ''USER''=a.QRFCUSER, ''NAME''=a.QRFCFNAM, ''DATTIM''=convert(datetime,substring(a.QRFCDATUM,5,2)+''-''+right(a.QRFCDATUM,2)+''-''+left(a.QRFCDATUM,4)+'' ''+stuff(left(a.QRFCUZEIT,4),3,0,'':'')), ''MESSAGE''=a.ERRMESS, AppServer=b.ARFCRHOST, ''SQLServer''=@@servername from ['+@schema+'].TRFCQOUT a left outer join ['+@schema+'].ARFCSSTATE b on a.ARFCIPID=b.ARFCIPID and a.ARFCPID=b.ARFCPID and a.ARFCTIME=b.ARFCTIME and a.ARFCTIDCNT=b.ARFCTIDCNT where a.QSTATE in (''CPICERR'', ''SYSFAIL'') and a.DEST not in (''VERTEX'') union select ''TCode''=''SMQ2'', ''DB''=db_name(), a.QNAME, a.DEST, ''STATE''=a.QSTATE, ''USER''=a.QRFCUSER, ''NAME''=a.QRFCFNAM, ''DATTIM''=convert(datetime,substring(a.QRFCDATUM,5,2)+''-''+right(a.QRFCDATUM,2)+''-''+left(a.QRFCDATUM,4)+'' ''+stuff(left(a.QRFCUZEIT,4),3,0,'':'')), ''MESSAGE''=a.ERRMESS, AppServer=b.ARFCRHOST, ''SQLServer''=@@servername from ['+@schema+'].TRFCQIN a left outer join ['+@schema+'].ARFCSSTATE b on a.ARFCIPID=b.ARFCIPID and a.ARFCPID=b.ARFCPID and a.ARFCTIME=b.ARFCTIME and a.ARFCTIDCNT=b.ARFCTIDCNT where a.QSTATE not in (''READY'',''RUNNING'') union select ''TCode''=''SM58'', ''DB''=db_name(), ''QNAME''=''N/A'', a.ARFCDEST, ''STATE''=a.ARFCSTATE, ''USER''=a.ARFCUSER, ''NAME''=a.ARFCFNAM, ''DATTIM''=convert(datetime,substring(a.ARFCDATUM,5,2)+''-''+right(a.ARFCDATUM,2)+''-''+left(a.ARFCDATUM,4)+'' ''+stuff(left(a.ARFCUZEIT,4),3,0,'':'')), ''MESSAGE''=a.ARFCMSG, AppServer=a.ARFCRHOST, ''SQLServer''=@@servername from ['+@schema+'].ARFCSSTATE a left outer join ['+@schema+'].TRFCQOUT b on a.ARFCIPID=b.ARFCIPID and a.ARFCPID=b.ARFCPID and a.ARFCTIME=b.ARFCTIME and a.ARFCTIDCNT=b.ARFCTIDCNT and a.ARFCDEST=b.DEST and a.ARFCSTATE=b.QSTATE and a.ARFCUSER=b.QRFCUSER and a.ARFCFNAM=b.QRFCFNAM where (a.ARFCSTATE like ''%ERR%'' or a.ARFCSTATE like ''%FAIL%'') and a.ARFCMSG not like ''%You cannot create a customs shipment%'' and b.DEST is null order by ''DATTIM''')"}) $tabSQLSAP.Controls.Add($btnSQLCIF) $btnSQLTMSPCONF = new-object System.Windows.Forms.Button $btnSQLTMSPCONF.Location = new-object System.Drawing.Size(480,40) $btnSQLTMSPCONF.Size = new-object System.Drawing.Size(120,20) $btnSQLTMSPCONF.Text = "TMSPCONF" $btnSQLTMSPCONF.Enabled = $True $btnSQLTMSPCONF.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @exec nvarchar(MAX) if exists (select * from sysobjects where name='TMSPCONF') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='TMSPCONF' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''TMSPCONF'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''TMSPCONF'' and schema_name(uid)='''+@schema+''') select a.* from TMSPCONF a inner join (select SYSNAME, VERSION=max(VERSION) from TMSPCONF group by SYSNAME) b on a.SYSNAME=b.SYSNAME and a.VERSION=b.VERSION where a.SYSNAME='''+@sapdb+''' else select a.* from ['+@schema+'].TMSPCONF a inner join (select SYSNAME, VERSION=max(VERSION) from ['+@schema+'].TMSPCONF group by SYSNAME) b on a.SYSNAME=b.SYSNAME and a.VERSION=b.VERSION where a.SYSNAME='''+@sapdb+''' ') "}) $tabSQLSAP.Controls.Add($btnSQLTMSPCONF) $btnSQLTPSTAT = new-object System.Windows.Forms.Button $btnSQLTPSTAT.Location = new-object System.Drawing.Size(480,60) $btnSQLTPSTAT.Size = new-object System.Drawing.Size(120,20) $btnSQLTPSTAT.Text = "TPSTAT" $btnSQLTPSTAT.Enabled = $True $btnSQLTPSTAT.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @sapdb nvarchar(256), @schema nvarchar(256), @rowcount bigint, @exec nvarchar(MAX) if exists (select * from sysobjects where name='TPSTAT') begin set @sapdb=db_name() select @schema=schema_name(uid) from sysobjects where name='TPSTAT' end else select top 1 @sapdb=db_name(dbid), @schema=lower(db_name(dbid)) ,@rowcount=count(*) from master.dbo.sysprocesses where program_name like 'R3%' group by db_name(dbid) order by 2 desc if (@sapdb is null) set @sapdb=db_name() if (@schema is null) set @schema=lower(@sapdb) exec('use ['+@sapdb+'] if not exists (select * from sysobjects where name=''TPSTAT'') select ''ErrorMessage''=''SAP database not found'' else if not exists (select * from sysobjects where name=''TPSTAT'' and schema_name(uid)='''+@schema+''') select * from TPSTAT else select * from ['+@schema+'].TPSTAT ') "}) $tabSQLSAP.Controls.Add($btnSQLTPSTAT) ##### ##### Tab: Sample ##### $btnSQLSample01 = new-object System.Windows.Forms.Button $btnSQLSample01.Location = new-object System.Drawing.Size(0,0) $btnSQLSample01.Size = new-object System.Drawing.Size(120,20) $btnSQLSample01.Text = "Sample01" $btnSQLSample01.Enabled = $True $btnSQLSample01.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample01'='Find this in the powershell script and modify the query'"}) $tabSQLSamples.Controls.Add($btnSQLSample01) $btnSQLSample02 = new-object System.Windows.Forms.Button $btnSQLSample02.Location = new-object System.Drawing.Size(0,20) $btnSQLSample02.Size = new-object System.Drawing.Size(120,20) $btnSQLSample02.Text = "Sample02" $btnSQLSample02.Enabled = $True $btnSQLSample02.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample02'"}) $tabSQLSamples.Controls.Add($btnSQLSample02) $btnSQLSample03 = new-object System.Windows.Forms.Button $btnSQLSample03.Location = new-object System.Drawing.Size(0,40) $btnSQLSample03.Size = new-object System.Drawing.Size(120,20) $btnSQLSample03.Text = "Sample03" $btnSQLSample03.Enabled = $True $btnSQLSample03.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample03'"}) $tabSQLSamples.Controls.Add($btnSQLSample03) $btnSQLSample04 = new-object System.Windows.Forms.Button $btnSQLSample04.Location = new-object System.Drawing.Size(0,60) $btnSQLSample04.Size = new-object System.Drawing.Size(120,20) $btnSQLSample04.Text = "Sample04" $btnSQLSample04.Enabled = $True $btnSQLSample04.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample04'"}) $tabSQLSamples.Controls.Add($btnSQLSample04) $btnSQLSample05 = new-object System.Windows.Forms.Button $btnSQLSample05.Location = new-object System.Drawing.Size(120,0) $btnSQLSample05.Size = new-object System.Drawing.Size(120,20) $btnSQLSample05.Text = "Sample05" $btnSQLSample05.Enabled = $True $btnSQLSample05.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample05'"}) $tabSQLSamples.Controls.Add($btnSQLSample05) $btnSQLSample06 = new-object System.Windows.Forms.Button $btnSQLSample06.Location = new-object System.Drawing.Size(120,20) $btnSQLSample06.Size = new-object System.Drawing.Size(120,20) $btnSQLSample06.Text = "Sample06" $btnSQLSample06.Enabled = $True $btnSQLSample06.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample06'"}) $tabSQLSamples.Controls.Add($btnSQLSample06) $btnSQLSample07 = new-object System.Windows.Forms.Button $btnSQLSample07.Location = new-object System.Drawing.Size(120,40) $btnSQLSample07.Size = new-object System.Drawing.Size(120,20) $btnSQLSample07.Text = "Sample07" $btnSQLSample07.Enabled = $True $btnSQLSample07.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample07'"}) $tabSQLSamples.Controls.Add($btnSQLSample07) $btnSQLSample08 = new-object System.Windows.Forms.Button $btnSQLSample08.Location = new-object System.Drawing.Size(120,60) $btnSQLSample08.Size = new-object System.Drawing.Size(120,20) $btnSQLSample08.Text = "Sample08" $btnSQLSample08.Enabled = $True $btnSQLSample08.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample08'"}) $tabSQLSamples.Controls.Add($btnSQLSample08) $btnSQLSample09 = new-object System.Windows.Forms.Button $btnSQLSample09.Location = new-object System.Drawing.Size(240,0) $btnSQLSample09.Size = new-object System.Drawing.Size(120,20) $btnSQLSample09.Text = "Sample09" $btnSQLSample09.Enabled = $True $btnSQLSample09.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample09'"}) $tabSQLSamples.Controls.Add($btnSQLSample09) $btnSQLSample10 = new-object System.Windows.Forms.Button $btnSQLSample10.Location = new-object System.Drawing.Size(240,20) $btnSQLSample10.Size = new-object System.Drawing.Size(120,20) $btnSQLSample10.Text = "Sample10" $btnSQLSample10.Enabled = $True $btnSQLSample10.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample10'"}) $tabSQLSamples.Controls.Add($btnSQLSample10) $btnSQLSample11 = new-object System.Windows.Forms.Button $btnSQLSample11.Location = new-object System.Drawing.Size(240,40) $btnSQLSample11.Size = new-object System.Drawing.Size(120,20) $btnSQLSample11.Text = "Sample11" $btnSQLSample11.Enabled = $True $btnSQLSample11.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample11'"}) $tabSQLSamples.Controls.Add($btnSQLSample11) $btnSQLSample12 = new-object System.Windows.Forms.Button $btnSQLSample12.Location = new-object System.Drawing.Size(240,60) $btnSQLSample12.Size = new-object System.Drawing.Size(120,20) $btnSQLSample12.Text = "Sample12" $btnSQLSample12.Enabled = $True $btnSQLSample12.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample12'"}) $tabSQLSamples.Controls.Add($btnSQLSample12) $btnSQLSample13 = new-object System.Windows.Forms.Button $btnSQLSample13.Location = new-object System.Drawing.Size(360,0) $btnSQLSample13.Size = new-object System.Drawing.Size(120,20) $btnSQLSample13.Text = "Sample13" $btnSQLSample13.Enabled = $True $btnSQLSample13.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample13'"}) $tabSQLSamples.Controls.Add($btnSQLSample13) $btnSQLSample14 = new-object System.Windows.Forms.Button $btnSQLSample14.Location = new-object System.Drawing.Size(360,20) $btnSQLSample14.Size = new-object System.Drawing.Size(120,20) $btnSQLSample14.Text = "Sample14" $btnSQLSample14.Enabled = $True $btnSQLSample14.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample14'"}) $tabSQLSamples.Controls.Add($btnSQLSample14) $btnSQLSample15 = new-object System.Windows.Forms.Button $btnSQLSample15.Location = new-object System.Drawing.Size(360,40) $btnSQLSample15.Size = new-object System.Drawing.Size(120,20) $btnSQLSample15.Text = "Sample15" $btnSQLSample15.Enabled = $True $btnSQLSample15.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample15'"}) $tabSQLSamples.Controls.Add($btnSQLSample15) $btnSQLSample16 = new-object System.Windows.Forms.Button $btnSQLSample16.Location = new-object System.Drawing.Size(360,60) $btnSQLSample16.Size = new-object System.Drawing.Size(120,20) $btnSQLSample16.Text = "Sample16" $btnSQLSample16.Enabled = $True $btnSQLSample16.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample16'"}) $tabSQLSamples.Controls.Add($btnSQLSample16) $btnSQLSample17 = new-object System.Windows.Forms.Button $btnSQLSample17.Location = new-object System.Drawing.Size(480,0) $btnSQLSample17.Size = new-object System.Drawing.Size(120,20) $btnSQLSample17.Text = "Sample17" $btnSQLSample17.Enabled = $True $btnSQLSample17.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample17'"}) $tabSQLSamples.Controls.Add($btnSQLSample17) $btnSQLSample18 = new-object System.Windows.Forms.Button $btnSQLSample18.Location = new-object System.Drawing.Size(480,20) $btnSQLSample18.Size = new-object System.Drawing.Size(120,20) $btnSQLSample18.Text = "Sample18" $btnSQLSample18.Enabled = $True $btnSQLSample18.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample18'"}) $tabSQLSamples.Controls.Add($btnSQLSample18) $btnSQLSample19 = new-object System.Windows.Forms.Button $btnSQLSample19.Location = new-object System.Drawing.Size(480,40) $btnSQLSample19.Size = new-object System.Drawing.Size(120,20) $btnSQLSample19.Text = "Sample19" $btnSQLSample19.Enabled = $True $btnSQLSample19.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample19'"}) $tabSQLSamples.Controls.Add($btnSQLSample19) $btnSQLSample20 = new-object System.Windows.Forms.Button $btnSQLSample20.Location = new-object System.Drawing.Size(480,60) $btnSQLSample20.Size = new-object System.Drawing.Size(120,20) $btnSQLSample20.Text = "Sample20" $btnSQLSample20.Enabled = $True $btnSQLSample20.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "select 'Sample20'"}) $tabSQLSamples.Controls.Add($btnSQLSample20) ##### ##### Tab: Security ##### $btnSQLLogins = new-object System.Windows.Forms.Button $btnSQLLogins.Location = new-object System.Drawing.Size(0,0) $btnSQLLogins.Size = new-object System.Drawing.Size(120,20) $btnSQLLogins.Text = "SQL Logins" $btnSQLLogins.Enabled = $True $btnSQLLogins.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager SET NOCOUNT ON select loginname, dbname, 'type'=isnull(nullif(isnull(nullif(isnull(nullif(isnull(nullif(convert(nvarchar(4),isntgroup),'1'),'Grp'),'0'),convert(nvarchar(4),isntuser)),'1'),'User'),'0'),'SQL'), 'ServerRoles'= isnull(nullif(isnull(nullif(convert(nvarchar(14),sysadmin),'1'),'sysadmin,'),'0'),'')+ isnull(nullif(isnull(nullif(convert(nvarchar(14),securityadmin),'1'),'securityadmin,'),'0'),'')+ isnull(nullif(isnull(nullif(convert(nvarchar(14),serveradmin),'1'),'serveradmin,'),'0'),'')+ isnull(nullif(isnull(nullif(convert(nvarchar(14),setupadmin),'1'),'setupadmin,'),'0'),'')+ isnull(nullif(isnull(nullif(convert(nvarchar(14),processadmin),'1'),'processadmin,'),'0'),'')+ isnull(nullif(isnull(nullif(convert(nvarchar(14),diskadmin),'1'),'diskadmin,'),'0'),'')+ isnull(nullif(isnull(nullif(convert(nvarchar(14),dbcreator),'1'),'dbcreator,'),'0'),'')+ isnull(nullif(isnull(nullif(convert(nvarchar(14),bulkadmin),'1'),'bulkadmin,'),'0'),'') , createdate, updatedate, ntsid=convert(nvarchar(256),null), sid, isntgroup, isntuser into #templogins from master.dbo.syslogins update #templogins set ntsid=convert(nvarchar(256), 'S-'+convert(nvarchar(12),convert(bigint,UNICODE(left(convert(nvarchar(256),sid),1)))-convert(bigint,256)*convert(bigint,UNICODE(left(convert(nvarchar(256),sid),1))/256)) +'-'+convert(nvarchar(12),UNICODE(right(left(convert(nvarchar(256),sid),4),1))/256+convert(bigint,nullif(UNICODE(left(convert(nvarchar(256),sid),1))/256,0))-convert(bigint,UNICODE(left(convert(nvarchar(256),sid),1))/256)) +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),5),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),6),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),sid))-6),-1))*0),'') +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),7),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),8),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),sid))-8),-1))*0),'') +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),9),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),10),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),sid))-10),-1))*0),'') +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),11),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),12),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),sid))-12),-1))*0),'') +isnull('-'+convert(nvarchar(12),convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),13),1)))+convert(bigint,UNICODE(right(left(convert(nvarchar(256),sid),14),1)))*convert(bigint,65536)+convert(bigint,nullif(sign(len(convert(nvarchar(256),sid))-14),-1))*0),'') ) where isntgroup=1 or isntuser=1 SET NOCOUNT OFF select loginname, type, dbname, ServerRoles=isnull(left(nullif(ServerRoles,''),len(ServerRoles)-1),''), createdate, updatedate, ntsid, sid from #templogins --where ServerRoles like '%sysadmin%' order by loginname drop table #templogins "}) $tabSQLSecurity.Controls.Add($btnSQLLogins) $btnSQLDBUsers = new-object System.Windows.Forms.Button $btnSQLDBUsers.Location = new-object System.Drawing.Size(0,20) $btnSQLDBUsers.Size = new-object System.Drawing.Size(120,20) $btnSQLDBUsers.Text = "SQL DB Users" $btnSQLDBUsers.Enabled = $True $btnSQLDBUsers.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager SET NOCOUNT ON select 'loginname'=l.name, 'name'=u.name, 'DB_Role'=r.name, u.createdate, u.updatedate from dbo.sysusers u inner join dbo.sysmembers m on u.uid=m.memberuid inner join dbo.sysusers r on r.uid=groupuid inner join master.dbo.syslogins l on u.sid=l.sid where l.sysadmin=0 union select 'loginname'=l.name, 'name'=u.name, 'DB_Role'='public', u.createdate, u.updatedate from dbo.sysusers u inner join master.dbo.syslogins l on u.sid=l.sid where l.sysadmin=0 order by loginname, DB_Role "}) $tabSQLSecurity.Controls.Add($btnSQLDBUsers) $btnSQLObjectSecurity = new-object System.Windows.Forms.Button $btnSQLObjectSecurity.Location = new-object System.Drawing.Size(0,40) $btnSQLObjectSecurity.Size = new-object System.Drawing.Size(120,20) $btnSQLObjectSecurity.Text = "Object Security" $btnSQLObjectSecurity.Enabled = $True $btnSQLObjectSecurity.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager SET NOCOUNT ON create table #tempsubpermlist (Owner nvarchar(256), Object nvarchar(256), Grantee nvarchar(256), Grantor nvarchar(256), ProtectType nvarchar(64), [Action] nvarchar(64),[Column] nvarchar(256)) insert into #tempsubpermlist exec sp_helprotect SET NOCOUNT OFF select * from #tempsubpermlist where Grantee in (select name from dbo.sysusers where islogin=1) drop table #tempsubpermlist "}) $tabSQLSecurity.Controls.Add($btnSQLObjectSecurity) $btnSQLServerPermissions = new-object System.Windows.Forms.Button $btnSQLServerPermissions.Location = new-object System.Drawing.Size(0,60) $btnSQLServerPermissions.Size = new-object System.Drawing.Size(120,20) $btnSQLServerPermissions.Text = "ServerPermissions" $btnSQLServerPermissions.Enabled = $True $btnSQLServerPermissions.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "set transaction isolation level read uncommitted --Written by: John Merager SELECT u.name, p.* from sys.server_principals u left outer join sys.server_permissions p on u.principal_id=p.grantee_principal_id "}) $tabSQLSecurity.Controls.Add($btnSQLServerPermissions) ##### ##### Tab: Partition ##### $btnSQLPartitionDetails = new-object System.Windows.Forms.Button $btnSQLPartitionDetails.Location = new-object System.Drawing.Size(0,0) $btnSQLPartitionDetails.Size = new-object System.Drawing.Size(120,20) $btnSQLPartitionDetails.Text = "Partition Details" $btnSQLPartitionDetails.Enabled = $True $btnSQLPartitionDetails.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SELECT OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName ,OBJECT_NAME(pstats.object_id) AS TableName ,ps.name AS PartitionSchemeName ,ds.name AS PartitionFilegroupName ,pf.name AS PartitionFunctionName ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary ,prv.value AS PartitionBoundaryValue ,c.name AS PartitionKey ,CASE WHEN pf.boundary_value_on_right = 0 THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) END AS PartitionRange ,pstats.partition_number AS PartitionNumber ,pstats.row_count AS PartitionRowCount ,p.data_compression_desc AS DataCompression FROM sys.dm_db_partition_stats AS pstats (NOLOCK) INNER JOIN sys.partitions AS p (NOLOCK) ON pstats.partition_id = p.partition_id INNER JOIN sys.destination_data_spaces AS dds (NOLOCK) ON pstats.partition_number = dds.destination_id INNER JOIN sys.data_spaces AS ds (NOLOCK) ON dds.data_space_id = ds.data_space_id INNER JOIN sys.partition_schemes AS ps (NOLOCK) ON dds.partition_scheme_id = ps.data_space_id INNER JOIN sys.partition_functions AS pf (NOLOCK) ON ps.function_id = pf.function_id INNER JOIN sys.indexes AS i (NOLOCK) ON pstats.object_id = i.object_id AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id AND i.type <= 1 /* Heap or Clustered Index */ INNER JOIN sys.index_columns AS ic (NOLOCK) ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0 INNER JOIN sys.columns AS c (NOLOCK) ON pstats.object_id = c.object_id AND ic.column_id = c.column_id LEFT JOIN sys.partition_range_values AS prv (NOLOCK) ON pf.function_id = prv.function_id AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END) --WHERE pstats.object_id = OBJECT_ID('????')--Table name ORDER BY TableName, PartitionNumber"}) $tabSQLPartition.Controls.Add($btnSQLPartitionDetails) $btnSQLPartitionList = new-object System.Windows.Forms.Button $btnSQLPartitionList.Location = new-object System.Drawing.Size(0,20) $btnSQLPartitionList.Size = new-object System.Drawing.Size(120,20) $btnSQLPartitionList.Text = "List Partitions" $btnSQLPartitionList.Enabled = $True $btnSQLPartitionList.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SELECT pf.name AS PartitionFunctionName ,ds.name AS PartitionFilegroupName ,prv.value AS PartitionBoundaryValue ,pstats.partition_number AS PartitionNumber ,pstats.row_count AS PartitionRowCount FROM sys.dm_db_partition_stats AS pstats (NOLOCK) INNER JOIN sys.destination_data_spaces AS dds (NOLOCK) ON pstats.partition_number = dds.destination_id INNER JOIN sys.data_spaces AS ds (NOLOCK) ON dds.data_space_id = ds.data_space_id INNER JOIN sys.partition_schemes AS ps (NOLOCK) ON dds.partition_scheme_id = ps.data_space_id INNER JOIN sys.partition_functions AS pf (NOLOCK) ON ps.function_id = pf.function_id INNER JOIN sys.indexes AS i (NOLOCK) ON pstats.object_id = i.object_id AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id AND i.type <= 1 /* Heap or Clustered Index */ LEFT JOIN sys.partition_range_values AS prv (NOLOCK) ON pf.function_id = prv.function_id AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END) --WHERE pf.name = '????' ORDER BY prv.value desc "}) $tabSQLPartition.Controls.Add($btnSQLPartitionList) $btnSQLPartitionMissing = new-object System.Windows.Forms.Button $btnSQLPartitionMissing.Location = new-object System.Drawing.Size(0,40) $btnSQLPartitionMissing.Size = new-object System.Drawing.Size(120,20) $btnSQLPartitionMissing.Text = "Missing/Wrong" $btnSQLPartitionMissing.Enabled = $True $btnSQLPartitionMissing.Add_Click({ExecuteSQLCommandFromButton $tbxSQLServer.Text $cbxSQLDatabase.Text "SELECT pf.name AS PartitionFunctionName ,fg.name AS PartitionFilegroupName ,prv.value AS PartitionBoundaryValue ,pf.name AS PartitionFunctionName FROM sys.filegroups AS fg (NOLOCK) LEFT OUTER JOIN sys.destination_data_spaces AS dds (NOLOCK) on dds.data_space_id=fg.data_space_id LEFT OUTER JOIN sys.data_spaces AS ds (NOLOCK) ON dds.data_space_id = ds.data_space_id LEFT OUTER JOIN sys.partition_schemes AS ps (NOLOCK) ON dds.partition_scheme_id = ps.data_space_id LEFT OUTER JOIN sys.partition_functions AS pf (NOLOCK) ON ps.function_id = pf.function_id LEFT JOIN sys.partition_range_values AS prv (NOLOCK) ON pf.function_id = prv.function_id AND dds.destination_id = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END) --WHERE --pf.name = '???' and --left(fg.name,4)+replace(replace(replace(replace(substring(fg.name,5,2),'??','????'),'??','????'),'??','????'),'??','????') <> convert(nvarchar(20),isnull(prv.value,'')) ORDER BY fg.name, prv.value "}) $tabSQLPartition.Controls.Add($btnSQLPartitionMissing) ############################################################################### ############################################################################### ############################################################################### $global:TabIndexList=@() for ($iTab=0; $iTab -lt $tab.TabCount; $iTab++) { $global:TabIndexList += New-Object PSObject -Property @{ Status=$tab.TabPages[$iTab].text Details="" Server="$CurrentComputer" } } ############################################################################### ### Data tables: ############################################################################### $Dataset = New-Object System.Data.DataSet $TblServersList = New-Object System.Data.DataTable $TblServersList.TableName = "ServersListDropdown" [void]$TblServersList.Columns.Add("Name") [void]$TblServersList.Columns.Add("Server") [void]$TblServersList.Columns.Add("Database") $Dataset.tables.add($TblServersList) if ("$File" -ne "") { if (test-path $File) { Try { $ImportCSV=Import-CSV -Path $File } Catch {$ImportCSV=@()} Foreach ($row in $ImportCSV) { Try { [void]$TblServersList.Rows.Add($row.Name,$row.Server,$row.Database) } Catch {} } } } else { $SampleCSV=@() <# $SampleCSV += New-Object PSObject -Property @{ Name="ThisComputer" Server="." Database="master" } $SampleCSV += New-Object PSObject -Property @{ Name="Test1" Server="MyServer" Database="" } $SampleCSV += New-Object PSObject -Property @{ Name="Test1" Server="TestServer1" Database="" } $SampleCSV += New-Object PSObject -Property @{ Name="Server2" Server="TestServer2" Database="" } $SampleCSV += New-Object PSObject -Property @{ Name="msdb" Server="." Database="msdb" } $SampleCSV += New-Object PSObject -Property @{ Name="master" Server="." Database="master" } $SampleCSV += New-Object PSObject -Property @{ Name=".,.\Express" Server=".,.\Express" Database="master" } #$SampleCSV | Export-CSV -Path "C:\Sample.CSV" #> Foreach ($row in $SampleCSV) { Try { [void]$TblServersList.Rows.Add($row.Name,$row.Server,$row.Database) } Catch {} } } if ($TblServersList.Rows.count -eq 0) { $cbxSQLSource.Visible=$False } ClickSQLSourceList ############################################################################### ### Add the Datagrid View and resize the columns to fit the data ############################################################################### $dgServersList=new-object System.windows.forms.DataGridView $dgServersList.Location = new-object System.Drawing.Size(0,0) $dgServersList.size = new-object System.Drawing.Size(($tab.Width-16),($tab.Height-$tab3.Top-52-$dgServersList.Top))#974,415 $dgServersList.DataSource = $TblServersList $dgServersList.DataBindings.DefaultDataSourceUpdateMode = 0 $dgServersList.AutoSizeColumnsMode = "AllCells" $dgServersList.Add_CellDoubleClick({SQLServersList_DClick}) $dgServersList.ReadOnly = $True $dgServersList.AllowUserToDeleteRows = $False $dgServersList.AllowUserToAddRows = $False $dgServersList.AllowUserToOrderColumns = $True $tabSQLDropdownList.Controls.Add($dgServersList) $global:SQLResultGrids=@() $global:SQLResultGrids+=new-object System.windows.forms.DataGridView $global:SQLResultGrids[0].Location = new-object System.Drawing.Size(0,0) $global:SQLResultGrids[0].size = new-object System.Drawing.Size(($tab.Width-16),($tab.Height-$tab3.Top-52-$global:SQLResultGrids[0].Top))#974,415 $global:SQLResultGrids[0].DataBindings.DefaultDataSourceUpdateMode = 0 $global:SQLResultGrids[0].AutoSizeColumnsMode = "AllCells" $global:SQLResultGrids[0].Add_CellDoubleClick({SQLResult_DClick}) $global:SQLResultGrids[0].AutoGenerateColumns = $True $global:SQLResultGrids[0].ReadOnly = $True $global:SQLResultGrids[0].AllowUserToDeleteRows = $False $global:SQLResultGrids[0].AllowUserToAddRows = $False $global:SQLResultGrids[0].AllowUserToOrderColumns = $True $global:SQLResultTabs[0].Controls.Add($global:SQLResultGrids[0]) ############################################################################### ### Timer - Used to check status of executing code ############################################################################### $timer = New-Object System.Windows.Forms.Timer if ($interval -gt 10 ) {$timer.Interval = 5000} Else {$timer.Interval = 1000} $global:SecsToInterval = $interval $timer.add_Tick( { $global:SecsToInterval -= ($timer.Interval / 1000) if ($global:SecsToInterval -lt 1 ) { $global:SecsToInterval = $interval if ($chkSQLAutoRefresh.Checked -eq $True -And "$($global:SQLLastQuery)" -ne "") { if ([int]$global:SQLCounter -gt 0) { $global:SQLCounter-- $lblSQLAutoRefreshCountDown.Text="($($global:SQLCounter) secs)" } else { $lblSQLAutoRefreshCountDown.Text="(Executing)" ExecuteSQLCommand $tbxSQLServer.Text $cbxSQLDatabase.Text $global:SQLLastQuery } } } }) $timer.Enabled = $true $timer.Start() ############################################################################### ### Status Bar: ############################################################################### $StatusBar = new-object System.Windows.Forms.StatusBar $StatusBar_P1 = new-object System.Windows.Forms.StatusBarPanel $StatusBar_P2 = new-object System.Windows.Forms.StatusBarPanel $StatusBar_P3 = new-object System.Windows.Forms.StatusBarPanel $StatusBar_P1.Text = "" $StatusBar_P1.BorderStyle = "Sunken" $StatusBar_P1.AutoSize = "Spring" $StatusBar_P2.Text = "" $StatusBar_P2.BorderStyle = "Sunken" $StatusBar_P2.AutoSize = "Spring" $StatusBar_P3.Text = "$CurrentComputer" $StatusBar_P3.BorderStyle = "Sunken" $StatusBar_P3.width = 150 [void]$StatusBar.Panels.Add($StatusBar_P1) [void]$StatusBar.Panels.Add($StatusBar_P2) [void]$StatusBar.Panels.Add($StatusBar_P3) $form.Controls.Add($StatusBar) $StatusBar.ShowPanels = $true ClickChangeTab ############################################################################### ### Disable Buttons and Checkboxes: ############################################################################### ############################################################################### ### Draw the GUI: ############################################################################### $Form.Controls.Add($tab) $Form.Text = "SQL Query Window with Multiple Pre-written Queries - Reporting Tool - Written By: John Merager" $Form.size = new-object System.Drawing.Size(($tab.Width+20), ($tab.Height+65))#1010,635 $Form.autoscroll = $true $Form.topmost = $false $chkSQLAlwaysOnTop.Checked=$Form.topmost $Form.MaximizeBox = $False $Form.Add_SizeChanged({FormSizeChanged}) $Form.Add_Shown({$Form.Activate()}) $Ended = $Form.ShowDialog() Write-host "$Ended" If ($Ended -eq "Cancel") { write-host "Form Closed" } ############################################################################### ### END ############################################################################### |