Plastic: The Convenience That Slowly Became a Global Crisis

SQL Server Management Studio has limitation of showing 43679 Characters only

In SQL Sever, if we create a table with column varchar(max) or nvarchar(max), then we can store upto 2 GB of data. However when we retrieve such columns, we can only see the data upto 43679 character. If we want to see complete set of data, we can use below script. User needs to provide following three inputs:
1. @StrLenSplit: It should not be more than 43679
2. @ColumnName: Provide the column name with data type as varchar(max) or nvarchar(max)
3. @TableName: Provide table name

--Required input from user
DECLARE @StrLenSplit BIGINT=4000 --should not be more than 43679
DECLARE @ColumnName VARCHAR(256)='jasonstringNvarchar'
DECLARE @TableName VARCHAR(256)='TableJson'

--Variable Declaration Part
DECLARE @stringlen BIGINT, @ExecuteQuery VARCHAR(2000)
DECLARE @ColumnDetails VARCHAR(1000)
DECLARE @idx INT=1, @SplitCount INT, @StringStartIndex INT=1
SELECT @stringlen=MAX(LEN(jasonstringNvarchar)) FROM TableJson

IF(@stringlen>@StrLenSplit)
BEGIN
SET @ColumnDetails=''
SET @SplitCount=1
WHILE (1=1)
BEGIN    

SET @ColumnDetails = @ColumnDetails+' SUBSTRING('+@ColumnName+','+CONVERT(VARCHAR(5),@StringStartIndex)+','+CONVERT(VARCHAR(5),@StrLenSplit)+') AS '+@ColumnName+'_'+CONVERT(VARCHAR(3),@SplitCount)+', '
SET @StringStartIndex=@StrLenSplit+1
SET @StrLenSplit=@StringStartIndex+@StrLenSplit
IF (@StrLenSplit>@stringlen)
BEGIN
SET @StrLenSplit=@stringlen
SET @idx=@idx+1
END
IF(@idx>2)
BEGIN
Break
END
SET @SplitCount=@SplitCount+1
END
END
SET @ColumnDetails = LEFT(@ColumnDetails, LEN(@ColumnDetails) - 1)
SET @ExecuteQuery= 'SELECT '+ @ColumnDetails+' FROM '+@TableName 
EXEC( @ExecuteQuery)

Algorithm to Automate SQL Server Collation change

Define Collation: Collation is a way of recognizing different languages, their ordering the characters and accents. It determines how the data is sorted and compared. There are following collation options provided by SQL Server to support it:

1. Case Sensitivity:
2. Accent Sensitivity:
3. Kana Sensitivity:
4. Width Sensitivity:

Collation can be specified at following levels in any SQL Server:

1. Server Level
2. Database Level
3. Column Level of a database table

Changing Server Level Collation:

There are two approaches to change Server Level Collation:

1. Documented approach (supported by Microsoft):
2. Undocumented approach (not supported by Microsoft):

In the undocumented approach of server collation change, following are the steps:

1. Detach all the databases of SQL Server
2. Take backup of SQL Jobs, logins and users if required.
3. Stop the service of SQL Server
4. Execute following command to change the collation:  sqlservr -m -T4022 -T3659 -s"SQL2017" –q "SQL_Latin1_General_CP1_CI_AI"
5. After execution of above command, after successfully collation change you would see following message:
6. “The default collation was successfully changed.”
7. After getting above message, user can stop further processing by pressing Ctrl+C button.
8. Start the Service of SQL Server, which was stopped in step 3.
9. Check the server collation now, it should be changed to SQL_Latin1_General_CP1_CI_AI
10. Attach the databases detached in step 1.
11. Check SQL Jobs, logins, users and other artifacts if they are there and behaving the same way as it was before the collation change operation.
    

Automating undocumented approach of collation change using powershell

Algorithm with following steps can be implemented:

1. Create a SQL Script to detach and attach all the databases of SQL Server. Execute the detach script using powershell.
2. Stop the service of SQL Server using below command:
3. STOP-SERVICE -NAME $SQLServiceName –Force
4. Get the location of SqlServr.exe file and execute command to change the collation:
5. $CollationChangeProcess=Start-Process -FilePath $SQLRootDirectory -ArgumentList "-c -m -T 4022 -T 3659 -s $JustServerInstanceName -q $NewCollationName" -NoNewWindow –passthru
6. Using loop check the event viewer application logs for SqlServr.exe continuously, to check when it generates following log message: The default collation was successfully changed.
7. $log=Get-WinEvent -FilterHashtable @{logname='application'; providername=$SQLServiceName; starttime = $StartDateTime} | Where-Object -Property Message -Match 'The default collation was successfully changed.'
8. Once the log message is generated, kill the process using below command:
9. Stop-Process -ID $CollationChangeProcess.ID
10. Start the service of SQL Server using below command:
11. START-SERVICE -NAME $SQLServiceName
12. Execute the attach script using powershell.

I have posted additional information here:
https://stackoverflow.com/questions/58337969/change-instance-level-collation-of-sql-server-using-powershell?noredirect=1#comment103033939_58337969