SQL Server – OS Bitness – 32 or 64 bit ?

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

 

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.