Share Coding

Tutorials, Problems, Stuffs …

Category Archives: MSSQL

Change Server Collation in MSSQL server 2017

** please create a Full Database Backup if necessary

Target: change server collation from “SQL_Latin1_General_CP1_CI_AS” to “Chinese_Hong_Kong_Stroke_90_CI_AS”

1. Check current collation

SELECT SERVERPROPERTY(N’Collation’)

1

2. Stop SQL server services

3.png

3. open a command prompt with administrative privileges, go to the BINN directory of Microsoft SQL Server and run sqlservr.exe as follow:

C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Binn
sqlservr -m -T4022 -T3659 -s”SQLEXPRESS” -q”Chinese_Hong_Kong_Stroke_90_CI_AS”

-m starts an instance of SQL Server in single-user mode

-t starts an instance of SQL Server with a specified trace flag

-s”<instance name>”

-q”<New Collation Name>”

ref: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options?view=sql-server-2017

2.png

4. Restart SQL server

5

5. Check for the change in Server Collation

6

 

Advertisements

SQL Server Linked Servers and Create View

Server Objects > New > Linked Server…

1

Input server name (or IP):

2

Security > Without using security context or input the remote login name and password:

3

If the view is created from “right-click > New Views”, an error received when:

Select * from [10.8.8.212].[DBName].[dbo].[TableName]
Error in FROM clause: near '10.8'. 
Unable to parse query text.

The bracket [] will be auto removed by MS Management studio:

4

You may create the view successfully by using SQL:

CREATE VIEW [dbo].[DB_VIEW]
AS
SELECT *
FROM [10.8.8.212].[DBName].[dbo].[TableName]
GO