Category Archives: Notes

All Articles

How to find out the SQL Server installation date?

Problem
How to find out the SQL Server installation date?

Solution:
To get the exact SQL Server installation date we have to check for the object which is created at the time of installation. NT Authority\System login is getting created at the time of SQL Server installation. You can check the SQL Server installation date by querying the sys.syslogins or sys.server_principals view against the login NT Authority\System name.

NT Authority\System login which has unrestricted access to all local system resources and it is a member of the Windows Administrators group on the local computer with the sysadmin fixed SQL Server role.NT Authority\System login get created at the time of installation of SQL Server.

First we will check the sys.syslogins or sys.server_principals views
sys.syslogins
This SQL Server 2000 system table is included as a view for backward compatibility which shows all logins, its metadata and access.

sys.server_principals
Contains a row for every server-level principal

We can query one of the views to get the installation date. If your SQL Server is English Language compatible you can directly query by login name or for the other languages we will use the neutral language (hexadecimal code) which is same on every instance.

-- work with only English language installations
SELECT  createdate as 'SQL Server Installation Date'
FROM    sys.syslogins 
where   name = 'NT AUTHORITY\SYSTEM'

--neutral language 
SELECT  createdate as 'SQL Server Installation Date'
FROM    sys.syslogins 
where   sid = 0x010100000000000512000000

--Using sys.server_principals 
SELECT create_date as 'SQL Server Installation Date'
FROM sys.server_principals 
WHERE name='NT AUTHORITY\SYSTEM'

--Sample CMDB Query
SELECT SERVERPROPERTY('productversion') as ProductVersion
      ,SERVERPROPERTY ('productlevel') as ProductLevel
      ,SERVERPROPERTY ('edition') as Edition
      ,SERVERPROPERTY ('MachineName') as MachineName
      ,SERVERPROPERTY ('LicenseType') as LicenseType
      ,SERVERPROPERTY ('NumLicenses') as NumLicenses
      ,create_date as 'SQL Server Installation Date'
FROM sys.server_principals 
WHERE name='NT AUTHORITY\SYSTEM'

Query to check the SQL Evaluation Version Expire Date
You can check the SQL Server evaluation version expire date as well using below query and enter the product key to activate the SQL Server license.

-- Evaluation version expire date
SELECT create_date as 'SQL Server Installation Date',
DATEADD(dd,180,create_date) as 'Expiration Date'
FROM sys.server_principals WHERE name='NT AUTHORITY\SYSTEM'

How to make sure that view metadata is updated?

Leave a reply

Problem: How to make sure that view metadata is updated?
Solution: You can use sp_refreshview system stored procedure to make sure that view meta data is updated. See below example for more information.

--creating demo table
create table demo
(
			id int identity (100,1),
			name varchar(10)
)

-- inserting sample values
insert into demo values ('Jugal'), ('Naresh')

-- creating view on table demo
create view vw_DEMO
as
select * from demo

-- select data from view
select * from vw_DEMO

-- adding new column to base table
alter table demo
add City varchar(10) null


-- select data from view, it is still returning ID and Name column
select * from vw_DEMO

-- Updating view sp_refreshview system stored procedure
sp_refreshview vw_Demo

-- selecting data again from view and it is returning all the columns
select * from vw_DEMO

You can create below procedure to refresh all the views on the database.

create procedure proc_RefreshViews
as
begin
    declare @vwName varchar (2000)

    declare objCursor cursor for
    select name from sys.objects where type = 'V'
    
    OPEN objCursor

    FETCH NEXT from objCursor INTO @vwName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    print  'Starting refresh of ' + @vwName
    exec ('sp_refreshview ''' + @vwName + '''')
    print  'completed refresh of ' + @vwName
    print  '--------------------------------'
    FETCH NEXT from objCursor INTO @vwName
    END

    CLOSE objCursor
    DEALLOCATE objCursor
end

How to connect SQL Server instance in case of SSPI context error?

1 Reply

In case of SPN (Service Principle Name) registration failed and you are getting the SSPI context error while connecting to SQL Server instanc you can use the Named Pipes protocol if enabled. To connect SQL Server instance using named pipes protocol you have to write NP against the instance name.

See below image for more information.