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

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

DBeaver – Generate SQL Insert Statements from Data

Script SQL Insert Statement from Data

Using SQL Server Management Studio ( SSMS), I entered quite a bit of data into a new table.

Not wanting to ever repeat that arduous process, I sought out tools that will allow me to script out the data.

 

Script Data

Yes, BCP will work, but I will be scripting out the data and will not be getting the SQL Statements.

 

Script SQL

DBeaver

Download and Install

Downloaded and Installed DBeaver.

BTW, here is the link.

Launched App

Installed Microsoft SQL Server JDBC Driver

Using built-in tooling added Microsoft’s SQL Server JDBC Driver.

Enter SQL

In DBeaver, launched new “SQL Editor”.

Entered the Query:


SELECT RoleID, RoleName

FROM   dbo.listofServerRoles tblSR

 

Result

Capture SQL

To capture the SQL, select all the records you will like scripted.

Then choose the menu items, “Generate SQL” and “Insert”.

Generated SQL

Shortcoming

There are some shortcomings in DBeaver’s implementation.

I will cover the shortcomings and workarounds in a later post.

SSMS – Error Message – “Property Owner is not available for Database”

Background

Trying to access the database property on one of our databases.

Error

Error Message

Syntax

Property Owner is not available for Database ‘[database]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

Sample

Property Owner is not available for Database ‘[AdminDB]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

 

Troubleshooting

Metadata

sp_helpdb

Syntax

use master
go

exec sp_helpdb
go

Output

Explanation

  1. Database
    • AdminDB
      • ~~UNKNOWN~~

sys.databases

Syntax

select 
		  tblSD.[database_id]
		, tblSD.[name]
		, tblSD.[owner_sid]
		, [owner]
			= SUSER_SNAME(tblSD.owner_sid)

from [master].[sys].[databases] tblSD
go

Output

Explanation

  1. Database
    • AdminDB
      • SUSER_SNAME(sys.databases.owner_sid) returns null

 

Remediation

Alter Authorization

Syntax


use [master]
go

ALTER AUTHORIZATION
DATABASE::[database]
TO [sa]
;

Sample


use master
go

BEGIN TRAN

	exec sp_helpdb

	ALTER AUTHORIZATION 
		ON DATABASE::[AdminDB] 
		TO [sa]
		;    

	exec sp_helpdb

ROLLBACK

Output

 

Reproducible

SSMS – v2017

The error does not occur in SSMS v2017.

SSMS – v2014

Noticed it in v2014 when trying to access the database property for an orphaned database.

 

References

  1. Microsoft
    • Docs > SQL > T-SQL > Statements
      • ALTER AUTHORIZATION (Transact-SQL)
        Link
    • Docs > SQL > T-SQL > Functions

SQL Server – Querying for OS Version

Background

As SQL Server now runs on Linux it is likely a good idea to be able to determine the underlying OS that a specific instance is sitting on.

 

Choices

Here are some choices for determining a targeted SQL Instance OS Version :-

  1. @@version
  2. sys.dm_os_host_info
    • SQL Server Versions
      • v2017 and above

 

System Function

@@version

Syntax


select @@version

Output

Tabulated
@@version OS/Client OS/Server
 Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) – 14.0.3015.40 (X64)
Dec 22 2017 16:13:22
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 2016 LTSB 10.0 <X64> (Build 14393: )
Windows 10
Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) – 12.0.5556.0 (X64)
Aug 17 2017 12:07:38
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)
Windows Server 2016
Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)
Windows 8.1 Windows Server 2012 R2
Microsoft SQL Server 2014 – 12.0.4100.1 (X64)
Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)
Windows Vista Windows Server 2008

 

Dynamic Management View

sys.dm_os_host_info

Syntax


SELECT 
          host_platform
	, host_distribution
	, host_release
	, host_service_pack_level
	, host_sku
	, os_language_version  

FROM sys.dm_os_host_info

;

Output

 

Summary

If you happen to be running SQL Server v2017, you will have not do to all the parsing involved with getting accurate data from @@version.

 

Addendum

Did a similar post here.

This was back in 2010.  And, that post is titled “MS SQL Server – Read OS Version“.

Windows NT 6.3

Unfortunately “Windows NT 6.3” is returned for the following OSes:

  1. Windows 2016
  2. Windows 2012

Prove

Windows Server 2016

SQL Server

master.dbo.xp_MSver
Image

Explanation
  1. Name :- WindowsVersion
  2. Internal Value :- 364118016
  3. Character Value :- 6.3 (14393)

 

WinOS

systeminfo
Image

Blogging

Blogging is an interesting option.

How much to reveal and how much to slay.

It all depends on what the original intent is.

Arguing for the sake of arguing is pointless.

 

References

  1. Microsoft
    • SQL Server
      • Docs > SQL > Relational databases > System dynamic management views
        • sys.dm_os_host_info ( Transact SQL )
          Link
      • Docs > SQL > T-SQL > Functions
        • @@version ( Transact SQL )
          Link
      • Docs / SQL / Relational databases / System stored procedures

SQL Server :- msdb.dbo.sp_enum_dtspackages

Background

SQL Server has many functions for reviewing metadata.

msdb.dbo.sp_enum_dtspackages

Introduction

The msdb.dbo.sp_enum_dtspackages is one of those functions.

Breaking down the name sp_enum_packages, this is what we get:

  1. sp ( Stored Procedure )
  2. enum ( Enumerate )
  3. dtspackages ( DTS Packages )

It is easy to tell that we can likely use this function to enumerate packages.

SQL

Syntax


msdb.dbo.sp_enum_dtspackages

Output

 

Undocumented

The msdb.dbo.sp_enum_dtspackages only works on these versions of SQL Server:

  1. v2005
  2. v2008
  3. v2008-R2

Summary

The whole point of doing this post is to draw attention to the risks of using undocumented functions.

Transact SQL – DateAdd and avoid overflow errors

Background

Transact SQL has adequate support for Date arithmetic exposed through dateadd, datediff, etc.

Interesting yesterday I ran into a stumbling block due to a relatively high value for the interval argument.

It graciously handles ints intervals.

But, choked on a bigint.

Error

Error Text

Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type int.

Error Image

Workaround

Overview

  1. The workaround proposed by Michael Valentine Jones goes like this
    • Rather than try to add the interval at one time, add succesively
      • Add Days
        • Divide the original increment by 86400 if adding seconds
          • 86400
            • 24 Hours * 60 minutes * 60 seconds
      • Add Seconds
        • Get what remains
          • Get module of the 86400
        • Apply what remained

Code

[dbo].[udfn_dateAddMilliSecondAvoidOverflow]


use master
go

if object_id('dbo.udfn_dateAddMilliSecondAvoidOverflow') is null
begin

	exec
	(
		'
			create function dbo.udfn_dateAddMilliSecondAvoidOverflow()
			returns datetime
			as
			begin
				return 0
			end

		'
	)
end
go

alter function [dbo].[udfn_dateAddMilliSecondAvoidOverflow]
(
	  @increment bigint
	, @base		 datetime

)
returns datetime
as
begin

	return
		(
			dateadd
			(
				  MILLISECOND
				, @increment % (86400 * 1000 )
				, dateadd
					(
						  day
						, @increment / ( 86400 * 1000)
						, @base
					)


			)
		)

end

go

grant execute on [dbo].[udfn_dateAddMilliSecondAvoidOverflow] to [public]
go
	

Sample Data set

 

Credit Roll

Crediting Michael Valentine Jones
Link