SQL Server :- Error – “Cannot resolve the collation conflict between “Latin1_General_100_BIN2” and “Latin1_General_CI_AS” in the equal to operation”

Background

Error during SQL Server Upgrade; specifically upgrade from MS SQL Server 2016 SP1 to Sp2.

 

Error


2018-05-18 22:27:31.36 spid4s      Creating procedure sp_sqlagent_get_perf_counters...
2018-05-18 22:27:31.37 spid4s      Error: 468, Severity: 16, State: 9.
2018-05-18 22:27:31.37 spid4s      Cannot resolve the collation conflict between "Latin1_General_100_BIN2" and "Latin1_General_CI_AS" in the equal to operation.
2018-05-18 22:27:31.38 spid4s      Error: 912, Severity: 21, State: 2.
2018-05-18 22:27:31.38 spid4s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2018-05-18 22:27:31.38 spid4s      Error: 3417, Severity: 21, State: 3.
2018-05-18 22:27:31.38 spid4s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2018-05-18 22:27:31.38 spid4s      SQL Server shutdown has been initiated
2018-05-18 22:27:31.39 spid4s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This

Troubleshooting

Error Text

The key error entry reads:

Cannot resolve the collation conflict between “Latin1_General_100_BIN2” and “Latin1_General_CI_AS” in the equal to operation.

Review Collation

Compare SQL Instance Collation against System Database Collation

SQL


; with cteCollationSQLInstance
(
      [name]
    , [collation]
)
as
(
    select 
          [name] 
            = cast
                (
                    serverproperty('servername')
                    as sysname
                )

        , [collation]
            = cast
              (
                serverproperty('collation')
                   as sysname
              )

)

, cteCollationDatabaseSystem
(
      [dbid]
    , [name]
    , [collation]
)
as
(
    select 
              tblSD.[database_id]
            , tblSD.[name]
            , tblSD.collation_name

    from   sys.databases tblSD

    where  tblSD.[database_id] <= 4
)
select 
          [collationSqlInstance]	
            = cteCSI.[collation]

        , [dbid]
            = cteCDS.[dbid]

        , [database]
            = cteCDS.[name]

        , [collationDatabase]
            = cteCDS.[collation]

from   cteCollationDatabaseSystem cteCDS

cross apply cteCollationSQLInstance cteCSI

 

Output

Remediation

Collation

Revert System Databases Collation to Match SQL Instance Collation

Outline

  1. Uninstall SQL Server Service Pack
    • If change was discovered post SQL Service Pack ( SP ) install, please remove SP
  2. SQL Instance Service
    • Stop SQL Server Instance
    • Start SQL Server Instance from command line issuing change collation
    • Wait for collation to be changed
    • Stop SQL Server Instance
    • Restart SQL Server Instance through Services Applet
  3. Review Collation Settings

Steps

Uninstall SQL Server Service Pack

Using Control Panel, Programs & Features, please uninstall Service Pack

Change Collation
Script

Script – Sample


rem set SQL Server Binary Folder
set _FLD="E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\"
 
rem Collation to Binary
set "_COLLATION=Latin1_General_100_BIN2"

rem Start SQL Server and pass in argument -q for new collation 
%_FLD%\sqlservr -m -T4022 -q%_COLLATION% 

Review Collation
Script – Sample


; with cteCollationSQLInstance
(
      [name]
    , [collation]
)
as
(
    select 
          [name] 
            = cast
                (
                    serverproperty('servername')
                    as sysname
                )

        , [collation]
            = cast
              (
                serverproperty('collation')
                   as sysname
              )

)

, cteCollationDatabaseSystem
(
      [dbid]
    , [name]
    , [collation]
)
as
(
    select 
              tblSD.[database_id]
            , tblSD.[name]
            , tblSD.collation_name

    from   sys.databases tblSD

    where  tblSD.[database_id] <= 4
)
select 
          [collationSqlInstance]	
            = cteCSI.[collation]

        , [dbid]
            = cteCDS.[dbid]

        , [database]
            = cteCDS.[name]

        , [collationDatabase]
            = cteCDS.[collation]

from   cteCollationDatabaseSystem cteCDS

cross apply cteCollationSQLInstance cteCSI

Output

Summary

There is so much we learnt through this exercise

  1. SQL Server Service Pack uninstall actually works
    • That it works on this occasion does not mean one should thread that road without very, very careful forethought, reason, and arguments
  2. Discovered unlisted SQL Server Error Message
    • Error 200
      • Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
      • sys.messages
        • Reviewed sys.messages and noticed 196, 197, 198, 199, 201, 202, 203, 204
        • Noticed that 200 is jumped over
  3. SQL Server Collation
    • Instance collation can be different than system’s database; specifically master database
      • Consider possible ramification

 

Listening

Listening to Sir Elton John

Sad Song
Link

SQL Server – DBCC Info

Background

A few posts back we spoke about “SQL Server – Mapping DBCC Commands to Dynamic Management Views ( DMV )“.

That post is here.

 

DBCC DBINFO

Introduction

DBCC DBINFO offers concise metadata at the database level.

 

SYNTAX

Its syntax is elementary and here it is “DBCC dbinfo with tableresults“.

 

Sample

ROWS

Display result as rows

Query


dbcc dbinfo with tableresults, no_infomsgs

Output

 

COLUMNS

Query



set nocount on
go

set XACT_ABORT on
go


declare @tblDBInfo TABLE
(
      [parentObject] sysname not null

    , [object]		 sysname not null

    , [field]		 sysname not null

    , [sequenceNbr]  smallint 
                     identity(1,1) not null

    , [value]        nvarchar(4000) not null

    , primary key
        (
              [parentObject] 
            , [object]		 
            , [field]		 
            , [sequenceNbr]
        )
)

declare @servername sysname
declare @dbid		int
declare @dbname		sysname

set @servername = cast(
                        serverproperty('servername')
                        as sysname
                      )

set @dbid = db_id()

set @dbname = db_name()

insert into @tblDBInfo
(
      [parentObject]
    , [object]		
    , [field]		
    , [value]       
)
exec ('dbcc dbinfo with tableresults, no_infomsgs')

select *

from   @tblDBInfo

order by 
        [field] asc

; with cteExclusion
(
      [dbid]
    , [Field] 
)
as
(
    select 
           2 as [dbid]
        , 'dbi_dbccLastKnownGood' as [Field]

)
, cteDBInfo
(
      [servername]
    , [dbid]
    , [Field]
    , [VALUE]
)
as
(

    select 
              @servername
            , @dbid
            , tblDBI.Field
            , tblDBI.[VALUE]

    from   @tblDBInfo tblDBI

    where  tblDBI.Field in
            (
                  'dbi_dbccLastKnownGood'
                , 'dbi_version'
                , 'dbi_createVersion'
                , 'dbi_LastLogBackupTime'
                , 'dbi_crdate'
                , 'dbi_dbname'
                , 'dbi_dbid'
                , 'dbi_cmptlevel'
            )


    and not exists
        (
            select 1
            from   cteExclusion cteEX
            where  cteEX.[dbid] = @dbid
            and    cteEX.Field = tblDBI.[Field]   
        )


)

, ctePivot
as
(
    select 
              cteDBI.[serverName]
            , cteDBI.[dbid]
            , cteDBI.[dbi_dbccLastKnownGood]
            , [dbi_version]
            , [dbi_createVersion]
            , [dbi_LastLogBackupTime]
            , [dbi_crdate]
            , [dbi_dbname]
            , [dbi_dbid]
            , [dbi_cmptlevel]	

    from   cteDBInfo cteDBI

    PIVOT
        (
            MAX(VALUE)
    
            FOR [Field] in
            (
                  [dbi_dbccLastKnownGood]
                , [dbi_version]
                , [dbi_createVersion]
                , [dbi_LastLogBackupTime]
                , [dbi_crdate]
                , [dbi_dbname]
                , [dbi_dbid]
                , [dbi_cmptlevel]
            )

        ) cteDBI

)

select 
          cteDBI.[servername]
        , cteDBI.[dbid]
        , cteDBI.[dbi_dbccLastKnownGood]
        , [dbi_version]
        , [dbi_createVersion]
        , [dbi_LastLogBackupTime]
        , [dbi_crdate]
        , [dbi_dbname]
        , [dbi_dbid]
        , [dbi_cmptlevel]

from   ctePivot cteDBI


Output

Summary

DBCC DBINFO has a wealth of information.

Nuggets include:

  1. Database Attributes
    • Compatibility level
    • Collation
    • Containment
    • Create Date
    • Database ID ( dbid )
    • Database name ( dbname )
    • MirrorId ( Mirrored database )
    • familyGUID ( Restored DB & Snapshot )
    • Safety ( Mirrored database )
    • Database Version
    • Database Create Version
  2. Operation Tracking
    • Last time dbcc checkdb was ran
    • Last time Transaction Log backup was taken

sqlservr.exe – Error – “Multiple instances of SQL server are installed on this computer. Renter the command, specifying the -s parameter with the name”

Background

Trying to change collation from binary to something easier to work such as case-insensitive.

 

Error Message

Textual

Multiple instances of SQL server are installed on this computer. Renter the command, specifying the -s parameter with the name

 

Remediation

Specification

Specify the Instance Name by utilizing MSSQLSERVER for the default instance; for named instance, please use the named instance name.

 

Code


set "_FLD=E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn"
set "_APP=sqlservr.exe"
 
set "_COLLATION=Latin1_General_CI_AS"
 
set "_INSTANCE=MSSQLSERVER"
 
"%_FLD%\%_APP%" -s %_INSTANCE% -m -T4022 -q%_COLLATION%

 

Collations

Collation Meaning
Latin1_General_CI_AS Latin Case Insensitive
Latin1_General_BIN2 Binary

 

SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps

Preface

In an earlier post we spoke about discovering that we can change our SQL Server Instance by restarting SQL Server with the /q option.

That post is here.

 

Real life experience

If truth be told, our first use-case was a recently installed SQL Server instance without any user databases.

In this post we will cover our experience with another newly built SQL Instance, but on which we will be restoring user database as part of a robust response to a server crash.

Our platform team raised up a new VM Instance, assigned server names and IP Addresses to it, and added it to our Active Directory team.

We installed SQL Server and applied the latest Service Packs.

During SQL Server install, upon being asked for the new server’s collation, we cheated and entered the Collation of another server from the same user community.

We went on to restore databases from the Production DB.  And, we knew that we had to re-align the SQL Server Logins.

We assessed each restored databases and ran a script that transverses the contextual database sysusers table and link each user to the login.

BTW, the SP applied against each user is the sp_change_users_login.

Applying that script unearth our misguidedness.

 

Error

Error Image

cannotresolvecollationconflict

 

Error Textual


Msg 468, Level 16, State 9L

Cannot resolve the collation conflict between Latin1_General_BIN and Latin1_General_100_BIN2 in the equal to operation. 

 

Little Traps

Knew right away that I had chosen the wrong collation during the install.

And, will have to transition over to the right collation.

There are a couple of little traps that one needs to look out for.

 

Read Only Databases

If we have read only databases on the SQL Instance, we can not continue.

Here is the error message.

Error

Error Image

readonlydb-20170103-1037pm

Error Text


2017-01-03 22:33:48.55 spid34s     [INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [22]. Starting offline checkpoint worker thread on a hidden SOS scheduler.

2017-01-03 22:33:48.55 spid34s     CHECKDB for database 'ppsivr_20161214' finished without errors on 2016-12-28 03:04:32.197 (local time). This is an informational message only; no user action is required.

2017-01-03 22:33:48.55 spid8s      Warning ******************

2017-01-03 22:33:48.55 spid8s      Attempting to change default collation to Latin1_General_100_BIN2.

2017-01-03 22:33:48.55 spid8s      Error: 5804, Severity: 16, State: 1.

2017-01-03 22:33:48.55 spid8s      Character set, sort order, or collation cannot be changed at the server level because at least one database is not writable.
                                   Make the database writable, and retry the operation.

2017-01-03 22:33:48.55 spid8s      Error: 3416, Severity: 20, State: 1.

2017-01-03 22:33:48.55 spid8s      The server contains read-only files that must be made writable before the server can be recollated.

2017-01-03 22:33:48.55 spid8s      SQL Server shutdown has been initiated

2017-01-03 22:33:48.56 spid8s      SQL Trace was stopped due to server shutdown.
                                   Trace ID = '1'. This is an informational message only; no user action is required.

 

 

In Memory Databases

If we have in-memory databases on the SQL Instance, we also can not continue.

Here is the error message.

Error

Error Image

memoryoptimizeddb_20170103_1112pm

 

Error Text


.
2017-01-03 23:07:47.03 spid8s       index restored for rbpivr1.gen_edi_stats_loc_time.
2017-01-03 23:07:47.09 spid8s       index restored for rbpivr1.br_logon.
2017-01-03 23:07:47.79 spid8s       index restored for rbpivr1.it_prp.
2017-01-03 23:10:04.93 spid8s       index restored for rbpivr1.event_log_bkp_chs.
2017-01-03 23:10:06.57 spid8s       index restored for rbpivr1.it_retire_election.
2017-01-03 23:10:06.61 spid8s       index restored for rbpivr1.gen_ua_appgroup.
2017-01-03 23:10:07.46 spid8s      Error: 41317, Severity: 16, State: 4.
2017-01-03 23:10:07.46 spid8s      A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user dat
abase or databases model and msdb, and it cannot write to master.
2017-01-03 23:10:09.54 spid8s      Error: 3434, Severity: 20, State: 1.
2017-01-03 23:10:09.54 spid8s      Cannot change sort order or locale. An unexpected failure occurred while trying to reindex the server to a new collation. SQL
 Server is shutting down. Restart SQL Server to continue with the sort order unchanged. Diagnose and correct previous errors and then retry the operation.
2017-01-03 23:10:09.54 spid8s      SQL Server shutdown has been initiated
2017-01-03 23:10:09.55 spid8s      SQL Trace was stopped due to server shutdown.
 Trace ID = '1'. This is an informational message only; no user action is required.


 

Validate

Code

Validate that in-memory tables exists on the cited database, please issue a query against the sys.tables table and look for cases where the is_memory_optimized is 1.


use [--database=--]
go


select 
		  tblSS.[name]
		, tblSO.[name]
		, tblSO.is_memory_optimized
from   sys.tables tblSO

inner join sys.schemas tblSS

		on tblSO.[schema_id] = tblSS.[schema_id]

where  tblSO.[type] = 'U'

and   tblSO.is_memory_optimized = 1

 

Output

validateinmemorytablesexist

 

 

Remediate

Make a list of the identified databases, their and log files, detach the databases, change the collation, restart the SQL Instance, re-attach the detached databases.

Please keep in mind that you really need to write down the database names and make a good list of the datafiles, as you will need that information when trying to re-attach the databases.

 

SQL Server – Changing Sql Instance Collation – via sqlservr/-q

Background

Reviewing SQL Server Alwayson requirements and as should be expected Collation Uniformity is needed (at the SQL Instance level).

Keep in mind not just at the database level, but at Instance level.

Curious

Curious as to the means to change Collation.

Familiar with doing so via re-running setup.exe.

UnDocumented Method

Was fascinated that there appears to be an undocumented route.  Let us see how dark that road is….

 

Steps

Outline

  1. Stop the SQL Server Instance
  2. Connect to the box using the Account SQL Server is running under or try runas
  3. Launch Cmd.exe in Administrator mode
  4. Start SQL instance using “ -m -T4022 -T3659  -q[COLLATION]

 

Actual Steps

Stop the SQL Instance

 

Ensure you are SQL Server Account

Ensure that you are running as the SQL Server Account.

To do so try the methods listed below:

  1. Logoff and Logon
  2. Issue “runas /user <username>

 

Launch Shell in Administrator Mode

Launch cmd.exe in Administrator Mode

 

Start SQL Server with needed parameters

Parameters

You need the following parameters

  1. Trace Flags
    • T4022
      • Skip Start-up procedures
    • T3659
      • Writes all log to errorlog
  2. Command Line Arguments
    • -q
      • -q and collation

 

Sample Code

Change to United Kingdom [UK]

Code

set _FLD="D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\"

rem United Kingdom
set "_COLLATION=Latin1_General_CI_AS"

%_FLD%\sqlservr -m -T4022 -q%_COLLATION% 
Output

indexrestored-defaultcollationwaschanged-0516pm

Explanation
  1. -q “Latin1_General_CI_AS”
    • Targeted Collation is mentioned at command line
  2. Attempting to change default collation to <target collation>
    • Attempting to change default collation to Latin1_General_CI_AS
  3. Index Restored ….
    • Index Restored for various tables
  4. Default Collation was successfully changed

 

sqlservr.exe options

Again, this is undocumented.

If one goes into the SQL Server Binary folder and issue “sqlservr.exe /?“, one will not see the -q option.

Output

commandlinearguments

See -q is not listed.

 

Crediting

Crediting Douglas P. Castilho
Changing SQL Server Collation After Installation
Link

 

Additional Reading

  1. Pieter Vanhove
    • -m Startup Parameter issues
      Link

 

References

  1. Database Engine Service Startup Options

 

Addendum

Here are some follow-up posts:

  1. SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps
    Date Added :- 2017-Jan-8th
    Link