Fix Ssms.exe 2014 on 4K monitors
I was having problems with Sql Management Studio 2014 running on 4K monitors.
This post tells you have to fix the issue:
SSMS in High-DPI Displays: How to Stop the Madness
Find missing SQL Server Indexes
The original post is here:
Find missing indexes using SQL Servers index related DMVs
SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]
,db.[database_id] AS [DatabaseID]
,db.[name] AS [DatabaseName]
,id.[object_id] AS [ObjectID]
,id.[statement] AS [FullyQualifiedObjectName]
,id.[equality_columns] AS [EqualityColumns]
,id.[inequality_columns] AS [InEqualityColumns]
,id.[included_columns] AS [IncludedColumns]
,gs.[unique_compiles] AS [UniqueCompiles]
,gs.[user_seeks] AS [UserSeeks]
,gs.[user_scans] AS [UserScans]
,gs.[last_user_seek] AS [LastUserSeekTime]
,gs.[last_user_scan] AS [LastUserScanTime]
,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
,gs.[avg_user_impact] AS [AvgUserImpact]
,gs.[system_seeks] AS [SystemSeeks]
,gs.[system_scans] AS [SystemScans]
,gs.[last_system_seek] AS [LastSystemSeekTime]
,gs.[last_system_scan] AS [LastSystemScanTime]
,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
,gs.[avg_system_impact] AS [AvgSystemImpact]
,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN '_'
ELSE ''
END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN ','
ELSE ''
END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK)
ON db.[database_id] = id.[database_id]
WHERE id.[database_id] > 4 -- Remove this to see for entire instance
ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);
Find SQL Tables with Identities
SELECT
OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME) ObjectId,
IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed,
IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment,
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS Current_Identity,
TABLE_SCHEMA + '.' + TABLE_NAME AS Table_Name
FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'TableHasIdentity') = 1
Angular isNumeric
For Angular, you can utilize the isNumeric from rxjs.
import { isNumeric } from "rxjs/util/isNumeric"
const value = isNumeric(data) ? Number(data) : 0;
Loop through Angular Reactive Form Controls
for (const c in this.searchForm.controls) {
if (this.searchForm.controls.hasOwnProperty(c) && this.searchForm.controls.value) {
const value = this.searchForm.controls.value;
}
}
Install .Net windows services without using installutil.exe
When writing a windows service using c# (or vb.net), you can install the service locally using installutil.exe. You can simply just install the service by using a few lines of c# code within your program’s startup. By doing this, you will not be able to use Client Profile of the framework.
By changing the Program.cs (main entry point for your service) to accept arguments, you can install/uninstall the service via a the .Net ManagedInstallerClass assembly.
static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
if (Environment.UserInteractive)
{
string parameter = string.Concat(args);
switch (parameter)
{
case "/install":
ManagedInstallerClass.InstallHelper(new[] { Assembly.GetExecutingAssembly().Location });
break;
case "/uninstall":
ManagedInstallerClass.InstallHelper(new[] { "/u", Assembly.GetExecutingAssembly().Location });
break;
}
}
else
{
ServiceBase[] servicesToRun =
{
new NotificationService()
};
ServiceBase.Run(servicesToRun);
}
}
}
Find Orphaned DTA Service Instances with SQL Query
I was trying to find what messages were being orphaned, so I started doing a little digging to figure out how I could get more detail.
With that being said, I still use the Terminator to fix the issue. I do NOT recommend updating or changing any messages in the messagebox without consent from Microsoft, as doing so could violate your support agreement.
This SQL query will give you the Orphaned Service Instances in the tracking database.
SELECT *
FROM BizTalkDTADb.dbo.dta_ServiceInstances (NOLOCK) srvi
JOIN BizTalkDTADb.dbo.dta_Host(NOLOCK) host ON srvi.nHostId = host.nHostId
WHERE dtEndTime IS NULL
AND uidServiceInstanceId NOT IN (
SELECT uidInstanceID
FROM BizTalkMsgBoxDb.dbo.Instances (NOLOCK)
UNION
SELECT StreamID
FROM BizTalkMsgBoxDb.dbo.TrackingData (NOLOCK))
Why does this happen? Well, first, you have tracking enabled for the port or orchestration. The above query will display the error column, which could give you which port and/or orchestration.
SQL Query to list BizTalk Hosts
This query will give you a list of the BizTalk Hosts:
--Hosts
SELECT nvcApplicationName,
uidAppID,
fAttributes
FROM [BizTalkMsgBoxDb].[dbo].[Applications](NOLOCK)
Disable WCF Certificate Check for Self-Assigned Certificates
To “turn off” the trusted certificate check for self-assigned certificates, just add this to the web.config
<settings>
<servicePointManager
checkCertificateName="false"
checkCertificateRevocationList="false" />
</settings>
Enable User without Login
This is a little tricky in the syntax. If you have a SQL User without Login that is showing disabled, you have to grant it connect access, since there isn’t a SQL login.
Grant Connect To [SQLUserNameHere]