aiDeeva.com

Author: Jugal Shah

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

    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?

    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.

  • How to install IIS on Windows 7?

    IIS is mandatory for the Virtual Server installation, in this article I will guide you how you can install the IIS on windows 7 or windows vista.

     

    Step 1: click Start, and then click Control Panel

     

     

    Step 2: In the Control Panel, click Programs then Click Turn Windows features on or off and from Windows Features dialog box Select Internet Information Services to choose the default features for installation.

  • What is “Null”? How much space “Null” value takes in SQL Server?

    Null is neither zero nor empty string. Null is not a value at all. Most importantly for our discussion, one null value does not equal any other null value. In the RDBMS, Null simply means a value that is not known.

    NULL value can occupy in the database based on the coulumn data type and width.

    Fixed length data type NULL value takes the space as width of filed. (Char (5) – NULL value take 5 bytes)
    Variable length data type NULL value takes 2 bytes. (varChar (5) – NULL value take 2 bytes)
    Integer data type null value takes 4 bytes space

    You can use the sparse columns to save the space of NULL values. http://technet.microsoft.com/en-us/library/cc280604.aspx