Differences

This shows you the differences between two versions of the page.

--- vendors:microsoft:sql [2024/05/20 09:07] – gerardorourke
+++ vendors:microsoft:sql [2026/04/28 14:24] (current) – [Column types] gerardorourke
@@ Line 258: / Line 258: @@
 ===== What do you do when SQL Server is unusually slow?=====
   *https://www.brentozar.com/responder/
+===== SPN issues ======
+====Cannot generate SSPI context" error when using Windows authentication to connect SQL Server====
+I found that the SPN (ServicePrincpalName) - were not in AD as they should be.
+there should be two similar to below:
+  * MSSQLSvc/MY-SERVER-NAME.mydomain.com:1433
+  * MSSQLSvc/MY-SERVER-NAME.mydomain.com
+  *https://houseofbrick.com/blog/microsoft-made-an-easy-button-for-spn-and-double-hop-issues/
+  *https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/cannot-generate-sspi-context-error
+you can use the tool - download and run from a client machine - to confirm
+  * https://www.microsoft.com/en-us/download/details.aspx?id=39046&WT.mc_id=soc-n-%5bTR%5d-loc-%5bServices%5d-%5bfarukc%5d
+you can check AD attributes using
+This query will return back the LDAP object name for the server "MY-SERVER-NAME"
+<code>dsquery computer -name MY-SERVER-NAME</code>
+Example Response:
+<code>"CN=MY-SERVER-NAME,OU="W2019",OU=Servers,DC=mydomain,DC=com"
+And then using this - you can find out all its LDAP attributes:
+<code>
+dsquery * "CN=MY-SERVER-NAME,OU="W2019",OU=Servers,DC=mydomain,DC=com" -attr *
+</code>
+Check the Attributes "servicePrincpalName:"
+===== Parsing a string with a separator =====
+<code>
+ID	PV1	PV2
+	boo     MFA|1|boo|5:a:b:c
+	blah    MFA|2|boo|6:2
+	bop     MFA|3|boo|7:3
+	banana  blah
+	a       MFA.1.blah.2
+	b       MFA.1.blah.3
+</code>
+Using String_Split - seems the easiest way to do this - but you need a uniue ID / Primary Key - which you can group off.
+<code sql>
+WITH SplitData AS (
+    SELECT
+        ID,
+		PV2,
+        value AS PartValue,
+        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS PartNumber
+    FROM RCD
+    CROSS APPLY STRING_SPLIT(PV2, '|')
+	WHERE PV2 like 'MFA|%'
+)
+SELECT
+    ID,
+	PV2,
+    MAX(CASE WHEN PartNumber = 1 THEN PartValue END) AS Part1,
+    MAX(CASE WHEN PartNumber = 2 THEN PartValue END) AS Part2,
+    MAX(CASE WHEN PartNumber = 3 THEN PartValue END) AS Part3,
+    MAX(CASE WHEN PartNumber = 4 THEN PartValue END) AS Part4,
+    MAX(CASE WHEN PartNumber = 5 THEN PartValue END) AS Part5,
+    MAX(CASE WHEN PartNumber = 6 THEN PartValue END) AS Part6,
+    MAX(CASE WHEN PartNumber = 7 THEN PartValue END) AS Part7,
+    MAX(CASE WHEN PartNumber = 8 THEN PartValue END) AS Part8,
+    MAX(CASE WHEN PartNumber = 9 THEN PartValue END) AS Part9,
+    MAX(CASE WHEN PartNumber = 10 THEN PartValue END) AS Part10
+FROM SplitData
+GROUP BY ID, PV2
+ORDER BY ID;`
+</code>
+<code>
+ID	PV2	Part1	Part2	Part3	Part4	Part5	Part6	Part7	Part8	Part9	Part10
+	MFA|1|boo|5:a:b:c	MFA	1	boo	5:a:b:c	NULL	NULL	NULL	NULL	NULL	NULL
+	MFA|2|boo|6:2	MFA	2	boo	6:2	NULL	NULL	NULL	NULL	NULL	NULL
+	MFA|3|boo|7:3	MFA	3	boo	7:3	NULL	NULL	NULL	NULL	NULL	NULL
+</code>
+An alternative approach is to use the PARSENAME function.
+However this allows only up to 4 fields. So we would need to use different seperators - which becomes very messy.
+Why only 4?
+Because, PARSENAME (Transact-SQL) Returns the specified part of an object name.
+The parts of an object that can be retrieved are the object name, owner name, database name, and server name.
+example: SELECT PARSENAME('[Server].database.schema.table', 1) AS 'Object Name';
+<code sql>
+SELECT
+ID
+,PV2
+,LEFT(PV2, CHARINDEX(':', PV2) - 1) AS Part1
+,PARSENAME(REPLACE(PV2, '|', '.'), 4) AS MFA1
+,PARSENAME(REPLACE(PV2, '|', '.'), 3) AS MFA2
+,PARSENAME(REPLACE(PV2, '|', '.'), 2) AS MFA3
+,PARSENAME(REPLACE(LEFT(PV2, CHARINDEX(':', PV2) - 1), '|', '.'), 1) AS MFA4
+FROM RCD
+Where PV2 like 'MFA|%'
+</code>
+<code>
+ID	PV2	Part1	MFA1	MFA2	MFA3	MFA4
+	MFA|1|boo|5:a:b:c	MFA|1|boo|5	MFA	1	boo	5
+	MFA|2|boo|6:2	MFA|2|boo|6	MFA	2	boo	6
+	MFA|3|boo|7:3	MFA|3|boo|7	MFA	3	boo	7
+</code>
+==== Get SQL Column Data TYPE ====
+<code sql>
+SELECT
+    COLUMN_NAME,
+    DATA_TYPE,
+    CHARACTER_MAXIMUM_LENGTH
+FROM INFORMATION_SCHEMA.COLUMNS
+WHERE TABLE_NAME = 'YourTableName';
+</code>
+==== Check if Column is a primary Key====
+<code sql>
+SELECT
+    kcu.COLUMN_NAME
+FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
+JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
+    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
+WHERE tc.TABLE_NAME = 'YourTableName'
+  AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
+  AND kcu.COLUMN_NAME = 'agentId';
+</code>