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

Vidocq Society

Wikipedia

Link

The Vidocq Society is a members-only crime-solving club that meets on the third Thursday of every month in Philadelphia, Pennsylvania.

The Vidocq Society is named for Eugène François Vidocq, the ground-breaking 19th century French detective who helped police by using the psychology of the criminal to solve “cold case” homicides. Vidocq was a former criminal himself, and used his knowledge of the criminal mind to look at murder from the psychological perspective of the perpetrator. At meetings, Vidocq Society Members (VSMs) listen to local law enforcement officials from around the world who bring in cold cases for review.

VSMs are forensic professionals; current and former FBI profilers, homicide investigators, scientists, psychologists, prosecutors and coroners who use their experience to provide justice for investigations that have gone cold. Members are selected by committee invitation only, pay a $100 annual fee, and commit to attend at least one meeting per year.

The Society was formed in 1990 by William Fleisher, Richard Walter, and Frank Bender. It solved its first case in 1991, clearing an innocent man of involvement in the murder of Huey Cox in Little Rock, Arkansas.

Vidocq will only consider cases that meet certain requirements: they must be unsolved deaths more than two years old, the victims cannot have been involved in criminal activity such as prostitution or drug dealing, and the case must be formally presented to them by the appropriate law enforcement agency. The Society does not charge for its services, and pays for the travel expenses of the law enforcement agents who come to present cases.

The Society was chronicled in a 2002 episode of The New Detectives entitled “Collective Justice”, and was also a plot point in the finale of the 2007–08 season of Law and Order: Special Victims Unit.

In 2010 it became the subject of a book, The Murder Room by Michael Capuzzo. In A Question of Guilt, a book in the Nancy Drew and Hardy Boys Super Mystery series, Nancy Drew and Frank and Joe Hardy, working on opposite sides of the same case, approach the Vidocq Society for help.

 

Artifacts

  1. Audio
    • YouTube
      • The Vidocq Society ( Closing Segment )
        Link
    • NPR
      • Fresh Air
        • The Vidocq Society: Solving Murders Over Lunch
          Link

Quotes

  1. Our only Client is the truth
  2. Types of Killers
    • Anger Retaliatory
    • Power Reassurance
    • Power Assertive
    • Anger Excitation
  3. Pathology
    • Further Pathology
    • Downward Growth
    • Coach

The New Detectives

RadiListing

  1. WikiVisually
    • List of The New Detectives episodes [show article only]
      Link

 

Videos

  1. Collective Justice
    • Profile
      • Season 7 – Episode 18
      • Philadelphia’s Vidocq Society, named after an 18th century French detective, is one of the world’s most unusual crime-solving organizations – a members-only club made up of forensic professionals. These prominent sleuths solve a murder case from a missing shoe and successfully prove homicide without a body.
    • Videos
      • YouTube
    • Persons
      • Scott Dunn
        • Jim Dunn ( Father )
      • Alisha Hamilton
      • Tim Smith
  2. Hidden Obsessions
    • Profile
      • Season 9, Episode 1
      • Murder convictions of James Lawson and Calvin Parker.
    • Videos
      • YouTube
  3. The New Detectives: Season 7 – Ep 13 “Drawing Conclusions”
    • Profile
      • Season 7, Episode 13
    • Videos
      • YouTube
    • Participants
      • Segment #1
        • Thomas Donahue
        • Carry Scott
        • Darlene
        • John Winslow
  4. The New Detectives: Season 7 – Ep 14 – Stranger than fiction
    • Profile
      • Season 7, Episode 14
    • Videos
      • YouTube
    • Participants
      • Segment #1
        • George
      • Segment #2
        • Michael Fletcher
        • Leanne Fletcher
        • Susan Chrzanowski ( Judge )
        • Tom Henderson ( Crime Writer )
    • Story
      • Time
        • Dial M for Misconduct
          Link
  5. The New Detectives: Season 9 – Ep 2 – Blind Trust
    • Profile
      • Season 9, Episode 2
      • Segments
        • Segment #1
          • Often, a killer will build the victim’s trust before he or she attacks. In this episode, New Mexico police search for a missing teenager who disappeared from a local skating rink. Authorities suspect the two friends she was last seen with.
    • Videos
      • YouTube
    • Participants
      • Segment #1
        • Carly Martinez
      • Segment #2
    • Locations
      • Segment
        • New Mexico
    •  Story
      • Segments
        • Segment #1
          • ABC – KVIA
            • Family spreads message 20 years after Carly Martinez’ murder
              Link
  6. The New Detectives: Season 9 – Episode 10 – Murderous Attraction
    • Profile
      • Season 9, Episode 10
      • Segments
        • Segment #1
          • The most sacred of vows are sometimes not even enough to stop people who are determined to get what they want using any means necessary. In this episode, a nurse suspects a patient’s husband of attempted murder by poisoning
        • Segment 2
          • A man stages his depressed wife’s murder as a suicide.
    • Videos
      • YouTube
  7. Crimes of Passion
    • Videos
      • YouTube
    • Story
      • Segments
        • Segment #1
          • ESPN
            • Gone to Glory
              Link
    • Participants
      • Segment
        • Segment #1
          • Marlin Banks
          • Dennis
          • Stewart
          • Jaime Myer
  8. Marked for Death
    • Videos
      • YouTube
    • Participants
      • Calvin Stallworth
  9. Trial By Fire
    • Profile
      • Season 8, Episode 15
      • At a crime scene, anything left behind or moved out of place is considered a clue. But a fire can extinguish everything in its path, challenging forensic investigators at every turn.
    • Videos
      • YouTube
    • Participants
      • Segment #1
        • Adam Tallbridge
        • Todd Carpenter
        • Josh and Nathaniel ( Lathan ) Lindell
      • Segment #2
        • Victim
          • Becky Saunders
          • Rita Talbert
        • Others
          • Michael Calpret
    • Story
      • Segment 2
        • Murderpedia
          • Edwin Bernard KAPRAT III
            Link
    • Location
      • Lacross
  10. Deadly Dealings
    • Profile
      • Season 4, Episode 7
    • Videos
      • YouTube
    • Participants
      • Segment #1
      • Segment #2
        • Victim
          • Gerald & Vera Woodman
            • Sons
              • Neil Woodman
              • Stewart Woodman
            • Company
              • Manchester Products
        • Homick
          • Steve Homick
          • Robert Homick
    • Story
      • Segment #2
        • Wikipedia
          • Murders of Gerald and Vera Woodman – Wikipedia
            Link
    • Location
      • Los Angeles, CA
  11. Misplaced Loyalty
    • Videos
      • YouTube
    • Profile
      • Season 9, Episode 5

 

Quotes

  1. Collective Justice ( Season 7 – Episode 18 )
    • The Vidocq society says “It’s only Client is Truth
  2. NPR
    • The Vidocq Society: Solving Murders Over Lunch
      • Anger Retaliatory Type
        Link
        Richard Walter: “In terms of interviewing an anger-retaliatory type, one of the things that must be understood by the detectives is, contrary to popular opinion, the perpetrator does not feel any guilt for committing the killing. When they leave the killing site, they many times have a sense of well-being and a sense of relief and a sense of charm because they’ve just had 50 pounds of emotional baggage taken off their shoulders. Police don’t expect that.”

 

Stories

  1. The Vidocq Society
    • NPR
      • The Vidocq Society: Solving Murders Over Lunch
        Link

 

Transact SQL – STRING_AGG

Background

In our post on MySQL – Information_schema.statistics we spoke glowingly of the GROUP_CONCAT Function.

I really could not find a way around using it in MySQL.

I ran into some difficulties using it and as with problems one just googles for workarounds.

BTW, the MySQL post is here.

SQL Server

Introduction

It seems that in version 2017, MSFT played catch up.

And, added a similar function.

String_Agg

Code


select
        [object]
            = quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , [indexID]
            = tblSI.[index_id]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

        , [columnList]
            = 
                STRING_AGG 
                (
                      concat
                      (
                          tblSC.[name]
                        , ''
                      )	

                    , ', '
                ) 
                WITHIN GROUP 
                (
                    ORDER BY 
                        tblSIC.[key_ordinal]
                )
            

from   sys.schemas tblSS

inner join sys.objects tblSO

        on   tblSS.[schema_id] = tblSO.[schema_id]
 
inner join sys.indexes tblSI

        on   tblSO.[object_id] = tblSI.[object_id]

inner join sys.index_columns tblSIC

        on   tblSI.[object_id] = tblSIC.[object_id]
        and  tblSI.[index_id]  = tblSIC.[index_id]

inner join sys.columns tblSC

        on   tblSIC.[object_id] = tblSC.[object_id]
        and  tblSIC.column_id = tblSC.column_id

/*
    Skip MS Shipped Objects
*/
where tblSO.is_ms_shipped = 0

/*
    Exclude Included Columns
    Only Include actual Key Columns
*/
and   tblSIC.[key_ordinal] > 0

group by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]

        , tblSI.[type_desc]

order by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]



Output

 

Crediting

Crediting Dan M for asking the question.

And, Martin Smith for ably.

Simulating group_concat MySQL function in Microsoft SQL Server 2005?
Link

 

References

  1. Microsoft
    • String_Agg
    • sys.index_columns
  2. Stack Overflow
    • Simulating group_concat MySQL function in Microsoft SQL Server 2005?
      Link

MySQL – information_schema.statistics

Background

As a quick follow-up to our discussion of metadata on tables, let us discuss indexes.

BTW, the discussion on tables is here.

INFORMATION_SCHEMA.STATISTICS

Code


select

		tblStat.TABLE_NAME as 'table'

	  , tblStat.INDEX_NAME as 'index'

	  , case tblStat.NON_UNIQUE
			   when 0 then 'No'
			   when 1 then 'Yes'
			   else '?'
		end as 'Unique'


	  , GROUP_CONCAT( COLUMN_NAME order by SEQ_IN_INDEX ) as colList


	  , tblStat.CARDINALITY


from  INFORMATION_SCHEMA.STATISTICS tblStat


where  tblStat.TABLE_SCHEMA = 'drupal'


group by

		 tblStat.TABLE_SCHEMA
	   , tblStat.TABLE_NAME
	   , tblStat.INDEX_NAME


order by

		 tblStat.TABLE_SCHEMA
	  ,  tblStat.TABLE_NAME
	  ,  tblStat.INDEX_NAME


LIMIT 25

;


Output

 

Comparison with Other Databases

  1. GROUP_CONCAT
    • The group_concat is very, very powerful and has few equal in other database platforms
    • Function Documentation

 

SHOW INDEX

To get a bit more information, specifically cardinality ( Number of unique values in each column combination ), we can issue “show index” against the table.

Code

Syntax


show index from [table] from [database];

Sample


show index from search_index from drupal;

Output

References

  1. Reference
    • INFORMATION_SCHEMA Tables
      • INFORMATION_SCHEMA STATISTICS Table
    • Show Index
    •  Group_Function
      • Group By Function
        Link
    • Group_Concat

MySQL – information_schema.tables

Background

With SQL being a lingua-franca for quering data it should not be a far stretch to note that the various governing bodies have agreed on standards on how to expose dictionary.

 

Mysql

For instance, if we want to see which tables are in a database we can launch our favorite GUI SQL Editor or just access MySQL’s Client query tool eponymous named mysql.

 

information_schema.tables

SQL

Sample


select 
       TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
     , TABLE_TYPE, ENGINE
     , TABLE_ROWS, AVG_ROW_LENGTH 

from information_schema.tables 

where TABLE_TYPE not in ("SYSTEM VIEW")  

LIMIT 10


 

Output

Explanation

  1. Database
    • def
  2. Schema
    • audit_repository
  3. Table
  4. Table Type
    • Available Options
      • SYSTEM VIEW
      • BASE TABLE
  5. Engine
    • Available Options
      • Memory – System Tables
      • InnoDB
      • MyISAM
  6. TABLE ROWS
    • Number of records in table
  7. AVG ROW LENGTH
    • Based on column data type and used size allocation

 

Comparison with Other Databases

SQL Server

  1. Able to use double-quotes in query
    • We are able to use double-quotes (“) to delimit column values
  2. Limit N
    • In Transact SQL we use top N.
    • On the other hand, in MySQL we use LIMIT N

 

show tables

Objective

List tables and views that fits specified name format.

SQL

Syntax


show full tables from [database] like [name] where TABLE_TYPE in [BASE_TABLE | VIEW ];

Sample


show full tables from drupal like 'role%';

 

Output

 

describe

Objective

Display table’s structure.

SQL

Syntax


describe [table];

Sample


describe drupal.role;

Output

 

References

  1. MySQL
    • Show Tables
      • Show Tables Syntax
        Link
    • Show Commands
      • Extensions to SHOW Statements
        Link

SQL Server – Identify Objects with dependencies outside of current database

Background

Needed a quick way to identify referenced objects that are not contained in my current database either to having being dropped, located in a different database, or located on a linked server.

 

DevioBlog

DevioBlog has a good concise query.

A write-up is available here.

And, here it is in it’s entirety.


select o.name, d.referenced_entity_name, *
from sys.sql_expression_dependencies  d

inner join sys.objects o 
       on d.referencing_id = o.object_id

where referenced_id is null


 

Code

 


/*

    sys.sql_expression_dependencies (Transact-SQL)
    v2008
    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-2017

    ID of the referenced entity. 


    a) The value of this column is never NULL for schema-bound references. 

    b) The value of this column is always NULL for cross-server and cross-database references.

    c) NULL for references within the database if the ID cannot be determined. 

        Objects dropped

    
    d) For non-schema-bound references, the ID cannot be resolved in the following cases:

        The referenced entity does not exist in the database.

*/
select 
          
        [object]
        = quoteName
            (
                isNull
                (
                    tblSS.[name]
                    , ''
                )
            )

            + '.'

            + quoteName
            (
                isNull
                (
                    tblSO.name
                    , ''
                )
            )

        , [objectType]
            = tblSO.[type_desc]

        , [referencedDatabase]
            = tblSED.referenced_database_name

        , [referencedObject]
            = quoteName
                (
                    isNull
                        (
                            tblSED.referenced_schema_name
                            , ''
                        )
                )
                + '.'
                + quoteName
                    (
                        isNull(tblSED.referenced_entity_name, '')
                    )

        , [classofReferencedObject]
            = tblSED.referenced_class_desc

from sys.objects tblSO

inner join sys.schemas tblSS

        on tblSO.schema_id = tblSS.schema_id

inner join sys.sql_expression_dependencies  tblSED

        on tblSO.object_id = tblSED.referencing_id

where (

        (
            ( tblSED.[referenced_id] is null )
        )

    )

Summary

Thank goodness information about referenced objects are cataloged in the sys.sql_expression_dependencies system table.

If the referenced object is not in the contextual database, the referenced_id is logged as null.

Also as part of clean-up effort the referenced_id column is nulled out whenever an object is dropped.