The enlist operation failed (reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server ‘Servername’. Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server, Error: 22026)

While configuring SQL Server Multi Server Administration: Master and Target Servers in SQL Agent, we encountered an error as below:

TITLE: Microsoft.SqlServer.Smo
——————————

MSX enlist failed for JobServer ‘TargetServer’.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&
EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

The enlist operation failed
(reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server ‘MasterServer’.
Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server, Error: 22026)

Root cause Analysis & Resolution

As per the investigation, it is identified as an issue associated with a registry value on the target server MsxEncryptChannelOptions. When we configure the set up, it tries to establish the connection between master and targets in a secure channel with full SSL encryption. And if SSL encryption is not enabled between servers/instances, then this setting has to be changed in target servers using the registry. You can change the registry value here: \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\SQLServerAgent. Please note the default value is 2. We need to change it to 0, however, this change needs to be verified and evaluated with you security norms and standards.

Refer Also:

Set Encryption Options on Target Servers

Stairway to SQL Server Table Partitioning – How do we partition an existing table

This post explains how to implement table partitioning for an existing table in SQL Server. Let us create a table as below for our explanation.

Create Table and Populate table data

Create Table AuditData (ID int identity(1,1) Primary Key not null, AuditDate Datetime not null)

Insert into AuditData Values(getdate() - (365*5)) ,(getdate() - (365*4)) ,(getdate() - (365*3))
,(getdate() - (365*2)) ,(getdate() - (365*1)) ,(getdate()) ,(getdate() + (365*5))
,(getdate() + (365*4)) ,(getdate() + (365*3)) ,(getdate() + (365*2)) ,(getdate() + (365*1))

--Check the data
Select * From AuditData

The above code will create a table called AuditData and inserts a single row for every previous and upcoming 5 years from now. Please note this test data is created on year 2020, so there might be difference in the data at the time you refer this blog.

Check the partition and its data allocation

select Object_name(p.object_id) Table_Name ,(Select name From sys.indexes where Object_id = p.object_id and index_id = p.index_id) 'Index_name',
partition_number, lv.value leftValue, rv.value rightValue,p.rows,
s.name 'Partition_Schema_Name',f.name 'Partition_Function_Name'
from sys.partitions p
join sys.allocation_units a on p.hobt_id = a.container_id
join sys.indexes i on p.object_id = i.object_id
Left join sys.partition_schemes s on i.data_space_id = s.data_space_id
left join sys.partition_functions f on s.function_id = f.function_id
left join sys.partition_range_values rv on f.function_id = rv.function_id and p.partition_number = rv.boundary_id
left join sys.partition_range_values lv on f.function_id = lv.function_id and p.partition_number - 1 = lv.boundary_id
where p.object_id = object_id('AuditData')

From the above, its clear that the index created on the table as part of Primary key has only one partition and all the data is part of that partition. Now, let us quickly convert this existing table as as partitioned table. To do so, we need to create partition function and partition schema as below. The function fn_AuditDate has been created on Datetime field so that in our example, we can create partition based on AuditDate.
--Create partition function on Datetime
Create partition function fn_AuditDate (Datetime) as
Range right for values('20150101', '20160101','20170101','20180101', '20190101','20200101')
/*Please note this test data is created on year 2020, so there might be difference in the data at the time you refer this blog.*/

--Create partition Schema to associate the function
Create partition scheme sc_AuditDate As 
Partition  fn_AuditDate ALL to ([Primary])
On successful execution, we will get the below Message:
Partition scheme ‘sc_AuditDate’ has been created successfully. ‘PRIMARY’ is marked as the next used filegroup in partition scheme ‘sc_AuditDate’.

Recreate clustered index to make the partition column as part of clustered index

ALTER TABLE dbo.AuditData DROP CONSTRAINT [PK__AuditDat__3214EC2760DC1A18]
GO
ALTER TABLE dbo.AuditData ADD CONSTRAINT [PK__AuditDat__3214EC2760DC1A18] PRIMARY KEY NONCLUSTERED  (ID)
   ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_AuditData_AuditDate_ID ON dbo.AuditData (AuditDate)
  ON sc_AuditDate(AuditDate)
GO

Now, it is important to make sure that the clustered index to be recreated on partition schema to partition the AuditData table. If you look at the above example, since the clustered index is created as part of Primary key creation, we need to drop the primary key and re create the primary key as non clustered index and create a separate clustered index on AuditDate. Note that the clustered index is created on partition that we created recently – sc_AuditDate.

Check the partition and its data allocation

select Object_name(p.object_id) 'Table_Name',(Select name From sys.indexes where Object_id = p.object_id and index_id = p.index_id) 'Index_name',
partition_number, lv.value leftValue, rv.value rightValue,p.rows,
s.name 'Partition_Schema_Name',f.name 'Partition_Function_Name'
from sys.partitions p
join sys.allocation_units a on p.hobt_id = a.container_id
join sys.indexes i on p.object_id = i.object_id
join sys.partition_schemes s on i.data_space_id = s.data_space_id
join sys.partition_functions f on s.function_id = f.function_id
left join sys.partition_range_values rv on f.function_id = rv.function_id and p.partition_number = rv.boundary_id
left join sys.partition_range_values lv on f.function_id = lv.function_id and p.partition_number - 1 = lv.boundary_id
where p.object_id = object_id('AuditData')

Now, you can see the clustered index has 7 partitions as per the range that we defined in the schema and function. The last partition contains all the data right to the range (eg: 2020/2021/2022/2023/2024/2025); 6 rows in our AuditData table.

Hope this explains how to partition an existing table simply. But wait, that may not be so easy as I explained for your real time scenario. You may want to implement partition on a table that has lots of data/ multiple indexes/foreign key relations defined etc. We need to carefully evaluate the steps to reduce the down time during the implementation of partition. I would suggest partitioning a table (especially bigger ones) should be an offline activity rather than an online for better performance and easy implementation and testing.

Cleanup Objects

Clean up is very important, so my test objects.

DROP TABLE AuditData
DROP PARTITION SCHEME sc_AuditDate
DROP PARTITION FUNCTION fn_AuditDate

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Search a value in your database in SQL Server

Here is a script to identify a value in your database. The script will identify the presence of the search value from all tables and generate select queries from the respective tables which you can execute and confirm.

	DECLARE @STRINGTOLOOKFOR VARCHAR(500)
		,@TABLENAME SYSNAME
		,@FULLTABLENAME SYSNAME
        --<-- Give the value to search------->
	SELECT @STRINGTOLOOKFOR = '7F0B0DD5-22BC-421B-9416-3A7C24146A98'  
        --<-- Give the value to search------->

	DECLARE @COLUMNNAME NVARCHAR(128),
		@DATETYPE NVARCHAR(128),
		@ROW SMALLINT,
		@ROWCOUNT INT,
		@SQL NVARCHAR(1000)
		
	DECLARE STRING_FIND_CURSOR CURSOR FAST_FORWARD FOR 
		
	SELECT TABLE_NAME, TABLE_SCHEMA+'.'+TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
	WHERE TABLE_TYPE ='BASE TABLE' 
	
	OPEN STRING_FIND_CURSOR
	
	FETCH NEXT FROM STRING_FIND_CURSOR 
	INTO @TABLENAME, @FULLTABLENAME
	
	SET @STRINGTOLOOKFOR = @STRINGTOLOOKFOR 
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @ROW = 1
	
		SELECT @ROWCOUNT = MAX([ORDINAL_POSITION])
		FROM [INFORMATION_SCHEMA].[COLUMNS]
		WHERE [TABLE_NAME] = @TABLENAME 
		GROUP BY [ORDINAL_POSITION]
	
		WHILE @ROW  <= @ROWCOUNT
                BEGIN 
		SELECT @COLUMNNAME =  QUOTENAME(COLUMN_NAME) ,
			@DATETYPE = [DATA_TYPE]
		FROM [INFORMATION_SCHEMA].[COLUMNS]
		WHERE [TABLE_NAME] = @TABLENAME 
			AND [ORDINAL_POSITION] = @ROW
		ORDER BY [ORDINAL_POSITION]
	
		SET @ROW = @ROW + 1  
			
		SET @SQL = NULL
	
		IF @DATETYPE IN ( N'CHAR', N'VARCHAR', N'TEXT')
			SET @SQL = 'SELECT * FROM ' + @FULLTABLENAME + ' WHERE PATINDEX(''%' + @STRINGTOLOOKFOR + '%'', ' + @COLUMNNAME + ') > 0'
			IF @DATETYPE IN (N'UNIQUEIDENTIFIER') AND @STRINGTOLOOKFOR LIKE '[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][-][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][-][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][-][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][-][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]'
				SET @SQL = 'SELECT * FROM ' + @FULLTABLENAME + ' WHERE' + @COLUMNNAME + ' = ''' + @STRINGTOLOOKFOR + ''''
			IF @DATETYPE IN (N'NCHAR', N'NVARCHAR', N'NTEXT')
				SET @SQL = 'SELECT * FROM ' + @FULLTABLENAME + ' WHERE PATINDEX(''%' + @STRINGTOLOOKFOR + '%'', CAST(' + @COLUMNNAME + ' AS TEXT)) > 0'
			
			IF @DATETYPE IN (N'SQL_VARIANT',N'SMALLINT',N'INT',N'BIGINT',N'TINYINT')
				SET @SQL = 'SELECT * FROM ' + @FULLTABLENAME + ' WHERE CONVERT(VARCHAR(8000),' + @COLUMNNAME + ') LIKE ''%'+ @STRINGTOLOOKFOR + '%'''
			
			IF @SQL IS NOT NULL
			BEGIN
				SET @SQL = 'IF EXISTS(' + @SQL + ') PRINT ''SELECT '+@COLUMNNAME+' FROM ' + @FULLTABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''''%'+@STRINGTOLOOKFOR+'%'''''''
				EXEC (@SQL)
			END
		END
	
		FETCH NEXT FROM STRING_FIND_CURSOR 
		INTO @TABLENAME, @FULLTABLENAME

	END
	
	CLOSE STRING_FIND_CURSOR
	DEALLOCATE STRING_FIND_CURSOR
	
	

How to build comma separated string in SQL Server

Building a comma separated string is a very frequent&common requirement for SQL Server developers. Developers find different ways to achieve this like CLR/XML PATH/Co-related queries etc. With this post, we are going to see two options that are widely used XML PATH & string_AGG and a quick look at its performance comparison.

— Using STUFF & XML PATH

This is the most widely used method to build the comma separated string. In my personal experience, I observed many performance issues related to this method for a large set of data.


Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))

Insert into BuildCommaSeparatedString Values 
(1,'English'),(1,'Maths'),(1,'Accountancy'),(2,'History'),(2,'Biology')

SELECT  SessionID,STUFF((SELECT  ',' + CourseID FROM BuildCommaSeparatedString EE
            WHERE  EE.SessionID=E.SessionID
            ORDER BY CourseID -- To order the Courses in order 
        FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM BuildCommaSeparatedString E
GROUP BY E.SessionID


Sample Execution results — Using string_AGG in SQL Server 2017

With SQL Server 2017 release, there is a better way to do this using string_agg built-in function. The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator.

STRING_AGG ( input_string, separator ) [ order_clause ]

input_string - is the column name of any type that can convert to a comma separated string
separator - is the separator caharacter like , or ; etc
[ order_clause ] - specifies the sort order of concatenated results using WITHIN GROUP clause

WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] )
Please note that, The STRING_AGG() ignores NULL and it does not add the separator for NULL when performing concatenation.

Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))

Insert into BuildCommaSeparatedString Values 
(1,'English'),(1,'Maths'),(1,'Accountancy'),(2,'History'),(2,'Biology')

Select SessionID, STRING_AGG(CourseID,',') From BuildCommaSeparatedString Group by SessionID

--To build the comma separated string in an order way
Select SessionID, STRING_AGG(CourseID,',') within group (Order by CourseID asc) 
From BuildCommaSeparatedString Group by SessionID

Sample Execution results A quick look on Performance benefit

The below snapshot clearly indicates that the performance benefit of string_AGG built-in function over XML PATH approach.

Conclusion

If you are in SQL Server 2017 – use built-in function string_AGG instead of any other method. I have seen this outperform many times than a custom built code. If you do not agree or have any different experience, please share the specific case with an example for the benefits of readers. Thanks in advance!

How to remove special characters like TAB, Carriage Return, and Line Feed characters from string in SQL Server

Today, we are going to see a scenario where we need to remove special characters from a string in SQL Server. Let us discuss on few methods to achieve this “extended” trim functionality.

1. Option: Using REPLACE

REPLACE function can be effectively used to replace the special characters.

Script:

DECLARE @BaddataString NVARCHAR(max);
SET @BaddataString = 'my '+
			CHAR(10)+CHAR(10)+CHAR(10) +
			 'text   ' + 
			CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) + 
			 '     ' +
			CHAR(10)+CHAR(10)+CHAR(10);

SELECT 
           @BaddataString,
           LEN(@BaddataString)
           , REPLACE(REPLACE(REPLACE(RTRIM(@BaddataString),CHAR(9), ''),CHAR(10),''),CHAR(13),'') ,
           LEN(REPLACE(REPLACE(REPLACE(RTRIM(@BaddataString),CHAR(9), ''),CHAR(10),''),CHAR(13),''))


Here is the output of the below code:

2. Option: Using CDATA Another effective option to use CDATA method.

Script


/*
The below function replaces special characters like invisible TAB, Carriage Return, and Line Feed characters.
*/
CREATE FUNCTION dbo.FN_RemoveBadCharacter(@input VARCHAR(MAX))
   RETURNS VARCHAR(MAX)
AS
BEGIN 
   RETURN (SELECT CAST('' AS XML).value('(/r/text())[1] cast as xs:token?','VARCHAR(MAX)'));
END
GO
DECLARE @BaddataString NVARCHAR(max);
SET @BaddataString = 'my '+
						CHAR(10)+CHAR(10)+CHAR(10) +
					 'text   ' + 
						CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) + 
					 '     ' +
						CHAR(10)+CHAR(10)+CHAR(10);

SELECT		@BaddataString, 
		LEN(@BaddataString)
			, dbo.FN_RemoveBadCharacter(@BaddataString),
		LEN(dbo.FN_RemoveBadCharacter(@BaddataString))

Here is the output of the below code:

3. Option: Using CLR to define a function to remove

There are certain things its best to do at application code rather than T-SQL. SQL Server may not be the best place to formatting string, date etc. There will be lots of available resources just away from a google search.

Hope, you enjoy this post, please share your thoughts as always!

Windows Power Shell Script to Find Full File Path Length for all files in Directory

In some cases, we may need to identify the maximum length of full file path in a directory such that we can reduce the file name to avoid file length/security policy issues.

PS Script:-

$pathToScan = "C:\temp\File_Length"  
$outputFilePath = "C:\temp\File_Length\output.txt" 
$writeOnConsole = $true   

$outputDir = Split-Path $outputFilePath -Parent
if (!(Test-Path $outputDir)) { New-Item $outputDir -ItemType Directory }

if ($writeOnConsole) {Write-Host "*************************************"}
if ($writeOnConsole) {Write-Host "  List of files with file Length :-  "}
if ($writeOnConsole) {Write-Host "*************************************"}
$stream = New-Object System.IO.StreamWriter($outputFilePath, $false)
Get-ChildItem -Path $pathToScan -Recurse -Force | Sort-Object {($_.FullName.Length)} -Descending | ForEach-Object {
    $Path = $_.FullName
    $len = $_.FullName.Length
    $strg = "$len : $Path"
    
    if ($writeOnConsole) { Write-Host $strg }

    $stream.WriteLine($strg)
}
$stream.Close()

Output:-

Hope this would be helpful, thanks for reading !!

For more Powershell related blogs: refer here.

 

Curious case of varchar to uniqueidentifier in SQL Server

Converting a datatype to another datatype is a very common requirement in real world and we usually do it with CAST or CONVERT (eg. int to char or varchar etc).

There are two types of conversions – implicit and explicit.

Explicit conversions are the conversions done by developers using SQL server functions like cast/convert etc and Implicit conversions are mainly managed by SQL Server internally. If you want to see these types of conversions, you can check for IMPLICIT_CONVERT in the execution plan generated by SQL Server.

Today, we are going to see a curious case of an implicit conversion – varchar to uniquidentifier.

SQL Server does a implicit conversion from varchar to uniqueidentifier by default as below:

Declare @varchar nvarchar(MAX), @uniqueidentifier uniqueidentifier
Set @varchar ='f124656c-136b-4beb-ab3a-b348053f898a,7f0b0dd5-22bc-421b-9416-3a7c24146a98'

Set @uniqueidentifier = @varchar
Select @uniqueidentifier

Now, if you look at the results, you can see the first 36 character is being converted implicitly. Since its a trivial, the operator may not be able to see in your execution plan.

The word of caution

Sometimes, if we are assigning more values/characters in the varchar variable, it would ONLY pick the first 36 characters and ignoring the rest characters by default. This default character of the conversion may end up with a wrong results, however, there would not be any sign of error! So beware of this characteristic while you writing a code.