0

Fix Ssms.exe 2014 on 4K monitors

Posted by Chris Straw on August 29, 2018 in Database, SQL Server |

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

 

0

Find missing SQL Server Indexes

Posted by Chris Straw on August 22, 2018 in Database, SQL Server |

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);

Tags:

0

Find SQL Tables with Identities

Posted by Chris Straw on May 2, 2018 in Database, SQL Server |
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    

Tags:

0

Angular isNumeric

Posted by Chris Straw on April 3, 2018 in Angular, RxJS, Typescript |

For Angular, you can utilize the isNumeric from rxjs.

import { isNumeric } from "rxjs/util/isNumeric"

const value = isNumeric(data) ? Number(data) : 0;

1

Loop through Angular Reactive Form Controls

Posted by Chris Straw on December 19, 2017 in Angular, Typescript |
    for (const c in this.searchForm.controls) {
      if (this.searchForm.controls.hasOwnProperty(c) && this.searchForm.controls.value) {
        const value = this.searchForm.controls.value;
      }
    }

0

Install .Net windows services without using installutil.exe

Posted by Chris Straw on November 2, 2013 in c#, vb.net, Windows Service |

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);
            }

        }
    }

Tags: , , ,

0

Find Orphaned DTA Service Instances with SQL Query

Posted by Chris Straw on October 26, 2013 in BizTalk, SQL Server |

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.

Tags: ,

0

SQL Query to list BizTalk Hosts

Posted by Chris Straw on October 26, 2013 in BizTalk, SQL Server |

This query will give you a list of the BizTalk Hosts:

--Hosts
SELECT  nvcApplicationName,
        uidAppID,
        fAttributes
FROM [BizTalkMsgBoxDb].[dbo].[Applications](NOLOCK)

Tags: ,

0

Disable WCF Certificate Check for Self-Assigned Certificates

Posted by Chris Straw on August 28, 2013 in c#, WCF |

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>

Tags: ,

0

Enable User without Login

Posted by Chris Straw on April 25, 2013 in Database, SQL Server |

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]

Tags:

Copyright © 2011-2019 Chris Straw All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.5, from BuyNowShop.com.