Use DMVs to determine based on past executions which indexes would be beneficial in that database.

The following SQL should be run in Production SQL server (off hours). It basically uses DMVs to determine based on past executions which indexes would be beneficial in that database. It recommends what indexes could be created on the specified tables.

SELECT
 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
 ,
 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
 + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
 + ' ON ' + mid.statement
 + ' (' + ISNULL (mid.equality_columns,'')
 + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
 + ISNULL (mid.inequality_columns, '')
 + ')'
 + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
--, migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

 

IdentityServer3 Custom Views for login

So in my endeavors to support Facebook, Google, Outlook and etc login providers the middle ware IdentityServer3 on git has proven worth its weight in gold.

If you need to customize the views for a client, let us suppose a customer wants the login page to be branded for their company logo….What??… the nerve of them.. lol… Seriously, it is extremely easy… See this link: [ branding ]  Perhaps they want to use a bootstrap theme, different layout or various validation rules… See that link…

UP NEXT:

My next post will be about using this with an Angular2 application. Much easier than I even thought!

IdentityServer3 Login Form + Anonymous Auth on IIS

When you deploy to IIS be sure to edit the web sites Authentication settings

Anonymous Authentication: Enabled

Basic Authentication: Disabled

Windows Authentication: Disabled

Digest Authentication: Disabled

Forms Authentication: Disabled

ASP.NET Authentication: Disabled

With this setup, you will not automatically be logged in via your Domain Account, but instead prompted to login via the Middleware Login Page.

If you want your users to use their windows login, you can enable Windows Authentication.

 

DEV/OP: Execute Stored Procedure against database using Powershell and ODBC

I had a need to execute a stored procedure stored in a ASE IQ database version 12. (VERY OLD).

I could only use a ODBC command…

Here is an example powershell example


param(
 [parameter(Mandatory=$true)][string]$Environment= "DEV"
)

$SybaseLogin = ""
$SybasePassword = ""
$IQLogin = ""
$IQPassword = ""
$EmailRecipients = "email"

# Determine script location for PowerShell
$PowerShellDir = Split-Path $script:MyInvocation.MyCommand.Path
Write-Host "Current Powershell script directory is $PowerShellDir"

$DllFullPath = "C:\Program Files\DataDirect\Connect for ADO.NET 3.3\DDTek.Sybase.dll"#$PowerShellDir + "\DDTek.Sybase.dll"
$Global:IqConnectionString = ""
$Global:SybaseConnectionString = ""
$Global:PY = ""
$Global:ObsDate = ""
$Global:SybaseData = @()

#============================================================================
# Initialize-SqlModule
#
# This method will set up all db connections
#============================================================================
function Initialize-SqlModule {

 param(
 [Parameter(Mandatory=$true)][string] $DbEnv,
 [Parameter(Mandatory=$true)][string] $DbSybaseLogin,
 [Parameter(Mandatory=$true)][string] $DbSybasePassword,
 [Parameter(Mandatory=$true)][string] $DbIQLogin,
 [Parameter(Mandatory=$true)][string] $DbIQPassword,
 [Parameter(Mandatory=$true)][string] $PowerShellDir
 )

 try {
 [reflection.assembly]::loadfile($DllFullPath)
 }
 catch [Exception] {
 Write-Host "Error loading sybase dll. Check location on next line."
 Write-Host $DllFullPath
 Write-Host $_.Exception.Message
 return "Error"
 }

 switch ( $DbEnv)
 {
 "DEV"
 {
 $Global:IqConnectionString = ""
 $Global:SybaseConnectionString = ""
 }
 "TAC"
 {
 $Global:IqConnectionString = " "
 $Global:SybaseConnectionString = " "
 }
 "PROD"
 {
 $Global:IqConnectionString = " "
 $Global:SybaseConnectionString = " "
 }
 default
 {
 Write-Host "Environment $Environment is not a valid parameter"
 exit -1
 }
 }
 return "OK"
}


#============================================================================
# Invoke-GetIqDataTables
#============================================================================
function Invoke-GetIqDataTables {
 param(
 [Parameter(Mandatory=$true)][string] $SqlToRun
 )

 $dataset = New-Object System.Data.DataSet

 $DbConn = New-Object System.Data.Odbc.OdbcConnection
 $DbConn.ConnectionString = $Global:IqConnectionString
 $DbConn.Open()
 $DBCmd= $DbConn.CreateCommand()
 $DbCmd.CommandTimeout = 3000
 $DBCmd.CommandText = $SqlToRun
 $adapter = New-Object System.Data.Odbc.OdbcDataAdapter $DBCmd
 $adapter.Fill($dataSet) | Out-Null
 $DBConn.Close()
 $DBConn.Dispose()

 return $dataset
}


#============================================================================
# Invoke-GetIqDataTables
#============================================================================
function Invoke-GetSybaseDataTables {
 param(
 [Parameter(Mandatory=$true)][string] $SqlToRun
 )

 $dataset = New-Object System.Data.DataSet

 $DbConn = New-Object DDTek.Sybase.SybaseConnection
 $DbConn.ConnectionString = $Global:SybaseConnectionString
 $DbConn.Open()
 $DBCmd= $DbConn.CreateCommand()
 $DbCmd.CommandTimeout = 3000
 $DBCmd.CommandText = $SqlToRun
 $adapter = New-Object DDTek.Sybase.SybaseDataAdapter $DBCmd
 $adapter.SelectCommand.Connection = $DbConn
 $adapter.Fill($dataSet) | Out-Null
 $DBConn.Close()
 $DBConn.Dispose()

 return $dataset
}


#============================================================================
# New-IqWeeklyOBS
#============================================================================
function New-IqWeeklyOBS {

 # Get todays date.
 # If todays date is less then July 1 then subtract 1 from current year and build PYXXXX
 # If todays date is july 1 or greater then use current year and build PYXXXX.
 $today =(Get-date).AddDays(-1).AddYears(-1).ToString("dd/mm/yyyy")
 $Global:PY = (Get-date).Year
 $july1 = (Get-Date -Day 1 -Month 7)
 if( $today -lt $july1) {
 $Global:PY = [int]$Global:PY - 1
 }

 # build SQL
 $SqlToRun = [string]::Format(“EXECUTE dbo.OBSWeekly ""{0}"",""{1}"",""PY{2}""”,"DEV",$today,$Global:PY)
 


 #$SqlToRun = " declare @obs_dt varchar(10) "
 #$SqlToRun += " select @obs_dt = '$Global:ObsDate' "

 #$SqlToRun += " select c.ctr_shname, 0 as avg_act_obs ,convert(numeric(10,2), (ISNULL(cdo.planned_male_res,0) + ISNULL(cdo.planned_male_non_res,0) + ISNULL(cdo.planned_female_res,0) + ISNULL(cdo.planned_female_non_res,0))) as plan_obs "
 #$SqlToRun += " from ctr_daily_obs cdo, center c "
 #$SqlToRun += " where c.ctr_id = cdo.ctr_id and cdo.obs_dt = Convert(DATE,'01/01/2016',101) "
 #$SqlToRun += " group by c.ctr_shname,cdo.planned_female_res, cdo.planned_female_non_res, cdo.planned_male_non_res, cdo.planned_male_res "
 #$SqlToRun += " order by c.ctr_shname "

 Write-Host $SqlToRun;

 $FoundTables = Invoke-GetIqDataTables $SqlToRun
 $IqData = @()

 ForEach ( $oneRow in $FoundTables[0].Tables.Rows)
 {
 $ObjectRow = New-Object System.Object
 $ObjectRow | Add-Member -type NoteProperty -Name ctr_shname -Value $oneRow.ctr_shname
 $ObjectRow | Add-Member -type NoteProperty -Name avg_act_obs -Value $oneRow.avg_Act_obs
 $ObjectRow | Add-Member -type NoteProperty -Name plan_obs -Value $oneRow.plan_obs
 $IqData += $ObjectRow
 }

 return $IqData
}


#============================================================================
#
# Execution Starts
#
#============================================================================
$Result = Initialize-SqlModule $Environment $SybaseLogin $SybasePassword $IQLogin $IQPassword $PowerShellDir
if ($Result -eq "OK" )
{
 Write-Host "Enter the word: DEV"
 # get data
 New-IqWeeklyOBS

 
}

VS Code Bootstap intellisense

Great tool, just wanted to re-post about it here

https://marketplace.visualstudio.com/items?itemName=ecmel.vscode-html-css

This gives you css class support in vs code when working with bootstrap or whatever you have in your resource json file.

So in the image below you can see I am adding a bootstrap panel and have access to the various classes…  nice…

 

vscodebootstrap