Share Coding

Tutorials, Problems, Stuffs …

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

Mapping data between 2 excel sheets

There are 2 excel sheets, left one is the full list of records and the right one is the filtered list I need to retrieve the ID from sheet 1 by given Ref No.

a.png

formula:

=INDEX(‘Full list‘!$A1:$A13507,MATCH(A3,’Full list‘!$B1:$B13507,0),0)

Result:

b.png

 

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

Error “Copying file obj\Debug\build.force…” and Warning “Source file ‘obj\Debug\\TemporaryGeneratedFile…” in VS2013

Error 5 Copying file obj\Debug\build.force to obj\Debug\Package\PackageTmp\obj\Debug\build.force failed. Could not find file 'obj\Debug\build.force'.
Warning	3 Source file 'obj\Debug\\TemporaryGeneratedFile_036C0B5B-1481-4323-8D20-8F5ADCB23D92.cs' specified multiple times	
Warning	4 Source file 'obj\Debug\\TemporaryGeneratedFile_5937a670-0e60-4077-877b-f7221da3dda1.cs' specified multiple times	
Warning	2 Source file 'obj\Debug\\TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs' specified multiple times

I encountered above messages in VS2013. I tried to clean solution, rebuild project and also run VS as administrator, but those doesn’t work for me.

My solution is open xxxxxx.csproj with a notepad and delete the lines from csproj file:

<Content Include="obj\Debug\build.force" />
<Compile Include="obj\Debug\TemporaryGeneratedFile_036C0B5B-1481-4323-8D20-8F5ADCB23D92.cs" />
<Compile Include="obj\Debug\TemporaryGeneratedFile_5937a670-0e60-4077-877b-f7221da3dda1.cs" />
<Compile Include="obj\Debug\TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs" />

Entity Framework Update table without primary Key (II)

The property ‘xxx’ is part of the object’s key information and cannot be modified

UserRight dboUserRight = DBModel.UserRights.FirstOrDefault(x => x.UserID == id 
                                                             && x.PageID == pid); 
dboUserRight.CanWrite = newValue; 
DBModel.SaveChanges();

Method 1: Replace old record

UserRight updatedUserRight = new UserRight();
updatedUserRight.CanWrite = newValue;
updatedUserRight.OtherProperty = xxxx;
DBModel.UserRights.Add(updatedUserRight);
DBModel.UserRights.Remove(dboUserRight);
DBModel.SaveChanges();