Transact SQL – Get AD Domain Name

May 24, 2018May 24, 2018 Daniel Adeniji Microsoft, SQL Server, Technical, xp_loginconfig

Background

Here I am reviewing code and noticing that it fails just about 100% of the time.

Nothing to be proud of.

Code

Original Code

Here is the original code


declare @regHKEY varchar(100);
declare @regPath varchar(100);
declare @regItem varchar(100);

DECLARE @strDomainName NVARCHAR(600); 

set @regHKEY = 'HKEY_LOCAL_MACHINE';  
set @regPath = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon';
set @regItem = 'DefaultDomainName' ;
 
EXEC master.dbo.xp_regread 
          @regHKEY
        , @regPath 
        , @regItem
        , @strDomainName OUTPUT  
  
select [domain]	
		= @strDomainName

Output

Text


RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1

Image

Troubleshooting

xp_regread appears to be failing due to permission issue; specifically the permission of the account running the SQL Server Engine.

Workaround

Here are some likely workaround:

DEFAULT_DOMAIN
- Undocumented function
master..xp_loginconfig
- Deprecated Extended Stored Procedure

Code

DEFAULT_DOMAIN


SELECT [domain] = DEFAULT_DOMAIN()

xp_loginconfig


exec master..xp_loginconfig 'default domain'

Summary

Nothing really to brag about.

Between undocumented & deprecated functions and unknown security requirements doing our best is a bit out of reach.

References

xp_loginconfig (Transact-SQL)
- Docs / SQL / Relational databases / System stored procedures
  xp_loginconfig
  Link

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

May 22, 2018May 22, 2018 Daniel Adeniji Technical, SQL Server, Installation, Microsoft, Service pack Upgrade, Error Log, sys.messages, Collation, Upgrade, errorlog, v2016 - Certified Against, 200 msdb110_upgrade.sql

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

Uninstall SQL Server Service Pack
- If change was discovered post SQL Service Pack ( SP ) install, please remove SP
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
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

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
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
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

May 20, 2018May 21, 2018 Daniel Adeniji GROUP_CONCAT, Martin Smith, Microsoft, SQL Server, Stack Overflow, String_Agg, sys.index_columns, Technical, Transact SQL, v2017 - Certified Against

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

Microsoft
- String_Agg
  - Reference
    Link
- sys.index_columns
  - Reference
    Link
Stack Overflow
- Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  Link

SQL Server – Identify Objects with dependencies outside of current database

May 18, 2018May 18, 2018 Daniel Adeniji Technical, SQL Server, Microsoft, Object, v2008 - Certified Against, sys.sql_expression_dependencies, DevioBlog, Object Dependency

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.

SQL Server – Operator – Top – Top Expression (0)

May 17, 2018May 19, 2018 Daniel Adeniji Craig Freedman, Martin Smith, Microsoft, Operators ( Query Plan ), Query Plan, SQL Server, Technical, Top ( Query Plan - Operator ), v2005 - Certified Against, v2008/R2 - Certified Against, v2017 - Certified Against Top Expression :- (0)

Background

Earlier today I found myself pressing to make sure I had done right by a query.

Query Plan

Query Plan – 01

Here is the original query Plan

Image

Explanation

I know that I don’t really want a Hash Match
- Took care of the Hash Match by reducing the query from two tables to a single table
- There are a few novel ways to do so, and will cover that later

Query Plan – 02

Here is the query Plan once we got rid of the secondary table

Image

Top ?

I was stuck at the Top Operator for a very long time

Explanation

Rowcount
- Do I have a set rowcount somewhere
- Is my environment introducing a constraint for maximum number of records to “touch”
- Is my edition of SQL Server throttling performance
Top
- Do I have a top N clause somewhere

Operator – Top – Default

Overview

Here is what our Top Operators looks like when we do not have “set rowcount” set and we do not have an actual TOP Clause.

Image

Explanation

Actual Number of Rows :- 65
Output List :- sysschobjs.id & sysschobjs.nsid
Top Expression :- (0)

Operator – Top – “Set rowcount”

Overview

What if we add an actual set rowcount

Image

Explanation

Actual Number of Rows :- 2
Output List :- sysschobjs.id & sysschobjs.nsid
Top Expression :- (0)

Operator – Top – “Select TOP N”

Overview

Here is what we see when we add a “Top 1” Clause.

Image

Explanation

Actual Number of Rows :- 1
Output List :- sysschobjs.id & sysschobjs.nsid
Top Expression :- (1)

Other Things

Overview

I was stuck and so tried other things; such as

Newer version of SQL Server ; v2017 to be exact
Took out the insert into and performed a simple select

Could not reproduce…

Craig Freedman ( MSFT )

Finally goggled on the right terms and read what Craig Freedman has to say.

The particular post that I will be quoting is:

ROWCOUNT Top
Link

TOP Operator
- If you’ve looked at any insert, update, or delete plans, including those used in some of my posts, you’ve probably noticed that nearly all such plans include a top operator.
SET ROWCOUNT
- It is a ROWCOUNT top. It is used to implement SET ROWCOUNT functionality.
Why doesn’t SQL Server add a ROWCOUNT top to select statements?
- SQL Server implements SET ROWCOUNT for select statements by simply counting and returning the correct number of rows from the root of the plan. Although this strategy might work for a really trivial update plan such as the one above, it would not work for more complex update plans. For instance, if we add a unique index to our table, the update plan becomes substantially more complex
Placement
- By placing the ROWCOUNT top above the table scan, the optimizer can ensure that the server updates exactly the correct number of rows regardless of the complexity of the remainder of the plan.

Martin Smith

The good thing about blogging and allowing comments is that people can come back and provide helpful feedback.

Here is one from Martin Smith:

Martin Smith
December 29, 2012 at 8:15 am

In 2012 it looks like this operator is only added to plans run under “SET ROWCOUNT” of other than zero. As far as I can discern it is added in to the set_options used as a plan cache key.

SQL Server Versions

Here are the versions of SQL Server where you will be able to reproduce the Top (0) Operator preceding data effecting operators :-

2005
2008-R2

Dedication

Thankfully I have a far better grasp courtesy of two able men, Craig Freedman & Martin Smith.

SQL Server – OS Bitness – 32 or 64 bit ?

May 17, 2018May 17, 2018 Daniel Adeniji Microsoft, SQL Server, Technical, Win OS, xp_MSver xp_MSver

Background

A while ago I googled on how to determine whether I am running on a 32 or 64 bit OS and found contemplative suggestions.

But, bar to implementation was a bit higher than I was willing to pay.

SSMS

And, so I was delighted when a few days ago, I rediscovered how Microsoft’s own SQL Server Management Studio ( SSMS) approaches same.

They invoke master..xp_MSVer; an extended stored procedure.

Master..xp_MSVer

SQL

Result in Rows


set nocount on
go

set XACT_ABORT on
go

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

create table #SVer
(
       [Index]				int
    ,  [Name]				sysname
    ,  [Internal_Value]		int
    ,  [Value]				nvarchar(512)
)
insert #SVer 
(
       [Index]
    ,  [Name]
    ,  Internal_Value
    ,  [Value]
)
exec master.dbo.xp_msver
            
select 
        
          tblSV.[Name]
        , tblSV.[Value]

from #SVer tblSV

where [Name] in
(
      'WindowsVersion'
    , 'Platform'
)

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

Result in Columns



set nocount on
go

set XACT_ABORT on
go

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

create table #SVer
(
       [Index]				int
    ,  [Name]				sysname
    ,  [Internal_Value]		int
    ,  [Value]				nvarchar(512)
)
insert #SVer 
(
       [Index]
    ,  [Name]
    ,  Internal_Value
    ,  [Value]
)
exec master.dbo.xp_msver

; with cteSV
as
(
    select 
              tblSV.[Name]
            , tblSV.[Value]

    from #SVer tblSV

    where [Name] in
    (
          'WindowsVersion'
        , 'Platform'
    )

)
select *

from (

        select 
                   [WindowsVersion]
                 , [Platform]

        from cteSV

        PIVOT
        (
            max(Value)

            for [Name] in
            (
                   [WindowsVersion]
                 , [Platform]
            )
        ) as tblA

    ) tblA

if object_id('tempdb..#SVer') is not null
begin
    drop table #SVer
end

Output

x32

x64

SQL Server – Temp Table Structure

May 15, 2018May 22, 2018 Daniel Adeniji Local Temporary Table, Microsoft, SQL Server, Table, Technical, TempDB, Temporary Table

Background

Created a temporary table on the fly.

But, before wrapping things up, I wanted to review the temp table’s structure.

SQL

sp_help

Syntax


exec [tempdb]..sp_help [table]

Sample


exec [tempdb]..sp_help [#cache]

Output

Generate Table Create Statement

dbo.itvf_getTempTableCreateStatement

Procedure


use [master]
go

/*

    drop function [dbo].[itvf_getTempTableCreateStatement]

*/
if object_id('[dbo].[itvf_getTempTableCreateStatement]') is null
begin

    exec
    ('
        CREATE FUNCTION [dbo].[itvf_getTempTableCreateStatement]
        (	
        )
        RETURNS TABLE 
        AS
        RETURN 
        (
            -- Add the SELECT statement with parameter references here
            SELECT [shell] = 1/0
        )
    ')

end
go

ALTER FUNCTION [dbo].[itvf_getTempTableCreateStatement]
(
    @object sysname	
)
RETURNS TABLE 
AS
RETURN 
(

    with cteColumnTypesWithColumnLength
    (
        [type]
    )
    as
    (
        select 'char'
        union all
        select 'nchar'
        union all
        select 'varchar'
        union all
        select 'nvarchar'

    )
    select 
              [columnID]
                = tblSC.[column_id]

            , [column]
                 = tblSC.[name]

            , [maxLength]
                 = tblSC.[max_length]

            , [userType] 
                = tblST.[name]

            , [isNullable]
                 = tblSC.[is_nullable]

            , [sqlClause] =

                case ( tblSC.[column_id] )
                    when 1 then '  '
                    else ', '
                end
                                
                +  quoteName(tblSC.[name])

                + ' '

                + tblST.[name]

                + case
                    when (
                            tblST.[name] in
                                (
                                    select [type] 
                                    from  cteColumnTypesWithColumnLength
                                )	 
                        )
                        then 
                            '('
                            + cast(tblSC.[max_length] as varchar(4))
                            + ')'

                    else ' '

                    end
                                
                + ' '

                + case tblSC.[is_nullable]

                    when 1 then ' null '
                    when 0 then ' not null '
                    else ' '

                  end

            , [colList] =

                case ( tblSC.[column_id] )
                    when 1 then '  '
                    else ', '
                end
                                
                +  quoteName(tblSC.[name])

    
    from   [tempdb].sys.objects tblSO

    inner join [tempdb].sys.columns tblSC

        on tblSO.object_id = tblSC.object_id

    inner join [tempdb].sys.types tblST

        on tblSC.[user_type_id] = tblST.[user_type_id]

    where (

            ( 
                tblSO.object_id 
                    = object_id
                        (
                            'tempdb..' + @object
                        ) 
            )

        )

)

go

Sample


use [tempdb]
go

set xact_abort on
go

set nocount on
go

declare @table sysname

set @table = '#cache'

if object_id('tempdb..#cache') is not null
begin

	drop table #cache

end

select top 10 *
into   #cache
from   sys.messages tblM

--exec [tempdb]..sp_help #cache

select *
from   [master].[dbo].[itvf_getTempTableCreateStatement]
		(
			@table
		)

if object_id('tempdb..#cache') is not null
begin

	drop table #cache

end
go

Output

Summary

Both sp_help and object_id work equally well with temp objects.

In the case of sp_help, please make sure that you reference ( temdpb) as in tempd..sp_help.

And, in the case of object_id please make sure that you are cross-referencing the system tables in tempdb.

And, that you use the full object’s name, tempdb..[objectname], when you attempt to get the object_id.