Category Archives: Computers

Full Text Index – Find Index Details

Recently I’ve had an issue with the primary key on a Full Text Index. This SQL query was helpful in highlighting the primary key which was being used:

SELECT tblOrVw.[name] AS TableOrViewName
	,tblOrVw.[type_desc] AS TypeDesc
	,tblOrVw.[stoplist_id] AS StopListID
	,c.name AS FTCatalogName
	,cl.name AS ColumnName
	,i.name AS UniqueIdxName
FROM (
	SELECT idxs.[object_id]
		,idxs.[stoplist_id]
		,tbls.[name]
		,tbls.[type_desc]
	FROM sys.fulltext_indexes idxs
	INNER JOIN sys.tables tbls ON tbls.[object_id] = idxs.[object_id]
	
	UNION ALL
	
	SELECT idxs.[object_id]
		,idxs.[stoplist_id]
		,[name]
		,[type_desc]
	FROM sys.fulltext_indexes idxs
	INNER JOIN sys.VIEWS vws ON vws.[object_id] = idxs.[object_id]
	) tblOrVw
INNER JOIN sys.fulltext_indexes fi ON tblOrVw.[object_id] = fi.[object_id]
INNER JOIN sys.fulltext_index_columns ic ON ic.[object_id] = tblOrVw.[object_id]
INNER JOIN sys.columns cl ON ic.column_id = cl.column_id
	AND ic.[object_id] = cl.[object_id]
INNER JOIN sys.fulltext_catalogs c ON fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN sys.indexes i ON fi.unique_index_id = i.index_id
	AND fi.[object_id] = i.[object_id];

Code initially from here, but has been tweaked slightly.

Microsoft Dynamics Nav – Invalid Security

Received the following error message with a Microsoft Dynamics Nav Client:

The client could not establish a connection to the Microsoft Dynamics NAV Server.
FaultCode = 'Invalid Security'
Reason = 'An error occurred when verifying the security for the message.'

The fix is a simple one. Make sure the date / time is correct both on the client, plus also the server.

Microsoft allow a time difference of up to 5 minutes between client/server. Over 5 minutes is typically not allowed as this can allow “replay attacks” (where credentials and authentication is replayed again at a later date).

A simple explanation of a “replay attack” is available here.

The setting can be changed in Group Policy, but I’d recommend keeping this to the 5 minutes by default.

https://docs.microsoft.com/en-us/windows/security/threat-protection/security-policy-settings/maximum-tolerance-for-computer-clock-synchronization

Server 2019 – Installing Legacy Software Requiring IIS 7

I’ve recently had an issue installing legacy software on Server 2019. This legacy software uses IIS 7 or newer.

Server 2019 comes with IIS version 10.

Unfortunately the installer could not pick up that a version newer than 7 was installed. This is due to how it checked the registry to find the version installed.

For fix this, I amended the following Registry value using RegEdit:

ComputerHKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesW3SVCParametersMajorVersion

I changed the value from 10 to 9, ran the installation, then changed it back to 10 afterwards.

BPA Custom Message Body in Web Service Tool

Within BPA it is possible to add a custom message body. These are not in the documentation, but are very useful. These functions override the standard operation and allow you to modify the document before it is sent by POST.

(In the screenshot below, the word “TEST” appears before the XML generated as part of the input step.)

Operations need to be wrapped within Curly brackets.

A few examples:

{=ThisOperation.XML}

The XML created from the input data.

{=ThisOperation.JSON}

The JSON created from the input data

{=FunctionCall(ThisOperation.XML)}

Call the function “FunctionCall” passing in the XML Input Data.

{=ThisOperation.Parameters(“ParameterName”)}

The parameter “ParameterName”.

Multiple steps can be wrapped together too, for example:

{=BuildRequest(ThisOperation.XML,”CREATE”,ThisOperation.Parameters(“APIKEY”))}

This calls the “BuildRequest” function, passing in the XML, the text “CREATE” and the input parameter “APIKEY”. The output is wrote into the body of the message.

MS SQL – List Tables by Table Size

The following handy script will show the size of tables, sorted by size.

SELECT schema_name(tab.schema_id) + '.' + tab.name AS [table]
	,cast(sum(spc.used_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS used_mb
	,cast(sum(spc.total_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS allocated_mb
FROM sys.tables tab
INNER JOIN sys.indexes ind ON tab.object_id = ind.object_id
INNER JOIN sys.partitions part ON ind.object_id = part.object_id
	AND ind.index_id = part.index_id
INNER JOIN sys.allocation_units spc ON part.partition_id = spc.container_id
GROUP BY schema_name(tab.schema_id) + '.' + tab.name
ORDER BY sum(spc.used_pages) DESC

Mockbin and BPA (Formerly Taskcentre)

I use http://mockbin.org/ to test web service calls, and quite recently have been using it with BPA in order to test integrations when access to a live service is not available.

Recently I’ve had an issue with BPA not picking up MockBin correctly and completely ignoring the data which has been returned via the web service call.

After lots of head scratching, in order to fix this, I had to add the headers of “charset: utf-8”. After adding this, Mockbin works perfectly with BPA.

Example below:

{
  "status": 200,
  "statusText": "OK",
  "httpVersion": "HTTP/1.1",
  "headers": [
    {
      "name": "Content-Type",
      "value": "application/xml"
    },
    {
      "name": "charset",
      "value": "utf-8"
    }
  ],
  "cookies": [],
  "content": {
    "mimeType": "application/xml",
    "text": "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n<tests>\n<test>\n<status>Example</status>\n</test>\n</tests>",
    "size": 0
  },
  "redirectURL": "",
  "bodySize": 0,
  "headersSize": 0
}

Microsoft Teams – Webhook Template Designer

With Microsoft Teams, it is possible to create webhooks. A webhook is an URL which is opened up to the internet and allows other applications to interact with the application.

In the case of teams, these webhooks allow notifications to be shown in Microsoft Teams.

The document which is sent to Teams via the webhook is in JSON format, and teams then takes this JSON and displays accordingly.

A basic notification may look like this:

This is created using the following JSON

{
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "type": "AdaptiveCard",
    "version": "1.0",
    "body": [
        {
            "type": "TextBlock",
            "id": "cccdb0fd-73f3-e358-6147-c7ed2254793e",
            "text": "This is a test",
            "wrap": true
        }
    ],
    "padding": "None"
}

To the more advanced:

Which is the following JSON:

{
	"type": "AdaptiveCard",
	"$schema": "https://adaptivecards.io/schemas/adaptive-card.json",
	"version": "1.0",
	"padding": "none",
	"body": [
		{
			"type": "Container",
			"style": "emphasis",
			"items": [
				{
					"type": "ColumnSet",
					"columns": [
						{
							"type": "Column",
							"width": "stretch",
							"items": [
								{
									"type": "TextBlock",
									"text": "**SATISFACTION SURVEY**",
									"weight": "bolder"
								}
							]
						},
						{
							"type": "Column",
							"items": [
								{
									"type": "Image",
									"horizontalAlignment": "Right",
									"verticalContentAlignment": "center",
									"url": "https://qms3.blob.core.windows.net/test/servicenow.png",
									"height": "20px",
									"altText": "Service Now Logo"
								}
							]
						}
					]
				}
			]
		},
		{
			"type": "Container",
			"padding": {
				"right": "padding",
				"left": "padding"
			},
			"items": [
				{
					"type": "TextBlock",
					"text": "Help us improve by taking our short satisfaction survey.",
					"weight": "bolder",
					"wrap": true
				}
			]
		},
		{
			"type": "Container",
			"padding": {
				"right": "padding",
				"left": "padding",
				"bottom": "padding"
			},
			"items": [
				{
					"type": "TextBlock",
					"text": "1\. How satisfied were you with the response time to your incident?",
					"wrap": true
				},
				{
					"type": "Input.ChoiceSet",
					"id": "responseTime",
					"isRequired": true,
					"placeholder": "Select a rating",
					"choices": [
						{
							"title": "Very Satisfied",
							"value": "1"
						},
						{
							"title": "Not Satisfied",
							"value": "2"
						}
					]
				},
				{
					"type": "TextBlock",
					"text": "2\. How courteous and respectful was the technician who responded to your query?",
					"wrap": true
				},
				{
					"type": "Input.ChoiceSet",
					"id": "technicianQuery",
					"isRequired": true,
					"placeholder": "Select a rating",
					"choices": [
						{
							"title": "Excellent",
							"value": "1"
						},
						{
							"title": "Very Good",
							"value": "2"
						}
					]
				},
				{
					"type": "TextBlock",
					"text": "3\. Was the technician able to resolve your issue during their first consultation?",
					"wrap": true
				},
				{
					"type": "Input.ChoiceSet",
					"id": "firstConsultation",
					"isRequired": true,
					"isMultiSelect": false,
					"style": "expanded",
					"choices": [
						{
							"title": "Yes",
							"value": "yes"
						},
						{
							"title": "No",
							"value": "no"
						}
					]
				},
				{
					"type": "TextBlock",
					"text": "4\. How satisfied are you with the overall service experience?",
					"wrap": true
				},
				{
					"type": "Input.ChoiceSet",
					"id": "OverallRating",
					"isRequired": true,
					"placeholder": "Select a rating",
					"choices": [
						{
							"title": "Excellent",
							"value": "1"
						},
						{
							"title": "Very Good",
							"value": "2"
						}
					]
				},
				{
					"type": "TextBlock",
					"text": "5\. Is there any other comment you would like us to know?",
					"wrap": true
				},
				{
					"type": "Input.Text",
					"id": "comment",
					"isMultiline": true,
					"placeholder": "Add a comment"
				},
				{
					"type": "ActionSet",
					"actions": [
						{
							"type": "Action.Http",
							"method": "POST",
							"title": "Submit",
							"isPrimary": true,
							"body": "{"responseTime": "{{responseTime.value}}", "technicianQuery":"{{technicianQuery.value}}",  "firstConsultation":"{{firstConsultation.value}}", "OverallRating":"{{OverallRating.value}}" }",
							"url": "https://actionsplayground.azurewebsites.net/workspaces/IREXSqpHRk-hkfa7y7CAAw"
						}
					]
				}
			]
		}
	]
}

Designing these cards can be quite complex and time consuming, however the following websites are very useful, as these allow you to experiment with the JSON and visually show the results:

https://messagecardplayground.azurewebsites.net/

https://amdesigner.azurewebsites.net/

MS SQL – Normalise Case

Wow not wrote any notes for a very long time…!

Recently I’ve had an issue where some text was presented in a table, and I needed to normalise the text.

For example:

TESTiNG 123

Would need to become:

Testing 123

With thanks to Justin Cooney this was possible using the SQL below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION NormalizeCase (@InputString VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @OutputString VARCHAR(500)
DECLARE @Location INT

SET @Location = 1

-- Pre-set to a character string if the input string exists and has a length. otherwise the out string remains a NULL
IF DATALENGTH(@InputString) &amp;amp;amp;amp;gt; 0
BEGIN
SET @OutputString = ''
END

WHILE @Location &amp;amp;amp;amp;lt;= DATALENGTH(@InputString)
BEGIN
DECLARE @CheckCharacter VARCHAR(1)
DECLARE @PrevCheckCharacter VARCHAR(1)
DECLARE @OutCharacter VARCHAR(1)

-- Set the current character to lower case in case a capitalization condition is not met.
SELECT @CheckCharacter = LOWER(SUBSTRING(@InputString, @Location, 1))

SELECT @PrevCheckCharacter = SUBSTRING(@InputString, @Location - 1, 1)

-- Set the current letter to uppercase if the preceeding letter is a non-letter character
-- and the current character is a letter
IF @PrevCheckCharacter NOT LIKE '[a-z]'
AND @CheckCharacter LIKE '[a-z]'
BEGIN
SELECT @OutCharacter = UPPER(@CheckCharacter)
END
ELSE
BEGIN
SELECT @OutCharacter = @CheckCharacter
END

SET @OutputString = @OutputString + @OutCharacter
SET @Location = @Location + 1
END

RETURN @OutputString
END
GO

The code can be used as follows:

SELECT dbo.NormalizeCase('THIS IS a reaLLY GooD example')

With the result being:

This Is A Really Good Example

Migrating Servers to Azure – Activating Windows

Recently I have migrated several servers from Vultr to Microsoft Azure.

As part of this migration, windows became un-activated – most likely because Vultr runs it’s own KMS service and it is no longer able to contact the server:

In order to resolve this, I changed the product key to Microsoft’s KMS key.

Then the KMS server was changed to Microsoft’s, and activation was forced: (run from administrative command prompt)

cscript c:\windows\system32\slmgr.vbs /ipk <product key>
cscript c:\windows\system32\slmgr.vbs /skms kms.core.windows.net:1688
cscript c:\windows\system32\slmgr.vbs /ato

SQL Server – Identify Unused Indexes

Within SQL Server an index is used to optimise the reading of data from the database.

However the side effect of having such an index is reduced write speed for inserts and updates. This is because SQL Server will need to update and maintain the index.

Running the below SQL will highlight indexes which have not been used recently, allowing you to remove these indexes (if these are no longer required). This is sorted by the number of updates, as tables with high number of updates will most likely benefit from removal of this index.

SELECT TOP 200 o.NAME AS ObjectName
	,i.NAME AS IndexName
	,i.index_id AS IndexID
	,dm_ius.user_seeks AS UserSeek
	,dm_ius.user_scans AS UserScans
	,dm_ius.user_lookups AS UserLookups
	,dm_ius.user_updates AS UserUpdates
	,p.TableRows
	,'DROP INDEX ' + QUOTENAME(i.NAME) + ' ON ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (
	SELECT SUM(p.rows) TableRows
		,p.index_id
		,p.OBJECT_ID
	FROM sys.partitions p
	GROUP BY p.index_id
		,p.OBJECT_ID
	) p ON p.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID, 'IsUserTable') = 1
	AND dm_ius.database_id = DB_ID()
	AND i.type_desc = 'nonclustered'
	AND i.is_primary_key = 0
	AND i.is_unique_constraint = 0
	AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0
ORDER BY UserUpdates DESC

The following line can also be altered in order to include indexes which are rarely used (The code above is for unused), as there may be some indexes which are expensive to maintain, but only used once a day for example!

AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0

Example of result from this is shown below: