I install my MSSQL 2012 by using every default of installation steps, collation is "SQL_Latin1_General_CP1_CI_AS" and my new created database is "Thai_CI_AS" (which owner of application specified with this, cannot change it by the way).
The program check the collation and shown the error that is mismatch of both collation.
Wed Oct 16 18:00:10 ICT 2013 : Insert data to table MONTHLY_SECURITY_TRADINGCrosscheck in my MS SQL Server Management Studio each database or run following SQL statement for checking:
: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Thai_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
select name, collation_name from sys.databases;
The result is shown as in red rectangle in above picture. As you see, there is type mismatch between "SQL_Latin1_General_CP1_CI_AS" and "Thai_CI_AS", so I must change the system's collation.
Then, open up the command prompt (in Start --> Run.. --> type 'cmd' and enter), navigate the location to "C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012"
Note: should backup everything in your MSSQL before doing this!!! (backup data, users, etc.)
Run following command in the command prompt :
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="<computer's_name>\<administrator>" / SAPWD= <SA_password> /SQLCOLLATION=<collation_to_change>Then the output from entered command should be nothing errors. If check log file, it should say 'PASS' and that is all. The example of log file look likes as below:
Overall summary:
Final result: Passed
Exit code (Decimal): 0
Start time: 2556-10-21 09:24:26
End time: 2013-10-21 09:25:29
Requested action: RebuildDatabase
Machine Properties:
Machine name: WIN-BPKOESH02D6
Machine processor count: 16
OS version: Windows Server 2008
OS service pack: Service Pack 2
OS region: ไทย
OS language: English (United States)
OS architecture: x86
Process architecture: 32 Bit
OS clustered: No
Product features discovered:
Product Instance Instance ID Feature Language Edition Version Clustered
SQL Server 2012 MSSQLSERVER MSSQL11.MSSQLSERVER Database Engine Services 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 MSSQLSERVER MSSQL11.MSSQLSERVER SQL Server Replication 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 MSSQLSERVER MSSQL11.MSSQLSERVER Full-Text and Semantic Extractions for Search 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 MSSQLSERVER MSSQL11.MSSQLSERVER Data Quality Services 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 MSSQLSERVER MSAS11.MSSQLSERVER Analysis Services 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 MSSQLSERVER MSRS11.MSSQLSERVER Reporting Services - Native 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 Management Tools - Basic 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 Management Tools - Complete 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 Client Tools Connectivity 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 Client Tools Backwards Compatibility 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 Client Tools SDK 1033 Enterprise Evaluation Edition 11.1.3000.0 No
SQL Server 2012 Integration Services 1033 Enterprise Evaluation Edition 11.1.3000.0 No
Package properties:
Description: Microsoft SQL Server 2012 Service Pack 1
ProductName: SQL Server 2012
Type: RTM
Version: 11
SPLevel: 0
Installation edition:
User Input Settings:
ACTION: RebuildDatabase
CONFIGURATIONFILE: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20131021_092412\ConfigurationFile.ini
ENU: false
HELP: false
IACCEPTSQLSERVERLICENSETERMS: false
INDICATEPROGRESS: false
INSTANCENAME: MSSQLSERVER
QUIET: true
QUIETSIMPLE: false
SAPWD: *****
SQLCOLLATION: THAI_CI_AS
SQLSYSADMINACCOUNTS: WIN-BPKOESH02D6\administrator
UIMODE: Normal
X86: false
Configuration file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20131021_092412\ConfigurationFile.ini
Detailed results:
Feature: SQL Server Replication
Status: Passed
Feature: Database Engine Services
Status: Passed
Feature: Reporting Services - Native
Status: Passed
Feature: Analysis Services
Status: Passed
Feature: SQL Browser
Status: Passed
Feature: SQL Writer
Status: Passed
Rules with failures:
Global rules:
There are no scenario-specific rules.
Rules report file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20131021_092412\SystemConfigurationCheck_Report.htm
Go back to MS SQL Server Management Studio, check the collation once again. They are changed!
As you see, my databases which not system databases were gone. I have to bring them back by right click at Databases in left panel, select "Attach..." Click "Add..", then select the datafile which is lost and it will be back as before rebuilding system database.
Anyway, the database user is still not correctly, I still cannot login to database engine by using SQL server authentication method. I have to remove that user out and re-create it.
Note: I found a website which has preparation steps which I think it is very useful, as below
http://www.mssqltips.com/sqlservertip/1531/rebuilding-the-sql-server-master-database-part-1-of-3/
Many thanks to http://sqlbuzz.wordpress.com/2011/08/20/how-to-rebuild-master-database-aka-rebuilding-sql-server-2008r2/ as well, it help me a lot to find out which path I must navigate to in command prompt.
I hope this may be helpful to anyone also. Good luck!
