Understanding Authentication Protocols

Authentication - It is a process of allowing or denying user access to network.

Network Authentication Protocols
NTLM  - NT Lan Manager
Kerberos

The above two protocols are commonly used in networks to authenticate users against the domain controller.

NT Lan Manager -- Is the authentication mechanism introduced in Windows NT. It is higher version of LM (Lan Manager), It uses a challenge response algorithm.
Whenever client request access to server or any resource then client sends username to server, the server generates and sends a challenge to the client;
then client encrypts that challenge using the user's password and sends response to the server. If the user is a local server then server validates response against
Security Account Manager, if the user is a domain user then server forward response to domain controller to validate and retrieve group policy of that user account,
and construct access token and establish session. In this methodology everytime client needs to validate to access network resources hence there will be load.

Kerberos
There are three heads of kerberos KDC (key distribution center), AS (Authentication Service) and TGS (Ticket Granting System).  The KDC installed as a part of domain controller.
It uses a ticket based approach. When a client computer requests access to a server or resources on it requests a TGT(Ticket Grant Ticket) from the AS in users domain.
While in process of authentication a ticket is issued to client from domain controller, once it received the ticket then client can get access the resources (if TGT is presents on the server)
on the network with out having authenticated again or we can call it as single-sign-on. It is a time bases the tickets can expire certain time. In order to use kerberos both the client and server must be on same domain. It requires registered SPN (Service Principal Name) are unique identifiers for services running on servers.

Login prompt multiple times and getting blank screen in SQL 2008 reporting services

Recently I faced the below issue

I got login prompt multiple times while opening SSRS page of another server and eventually getting blank screen.

workaround:

Ensure RsWindowsNTLM is specified in the file rsreportserver.config instead of RsWindowsNegotiate.

(or)

Change the report service account to network service from SSRS configuration and try from other system to login into reporting services.

Query to find number of rows in each table

select
 so.name as Table_Name,
 si.rows as Row_count
 from sysobjects as so
 inner join sysindexes as si
 on so.id = si.id
 where so.xtype = 'u'
 and si.indid < 2
 order by so.name



SELECT t.NAME AS TableName, i.name as indexName, sum(p.rows) as RowCounts, sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, (sum(a.data_pages) * 8) / 1024 as DataSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND i.OBJECT_ID > 255 AND i.index_id <= 1 GROUP BY t.NAME, i.object_id, i.index_id, i.name ORDER BY object_name(i.object_id)

How to clear recent servers list from SQL Server drop down list

In SQL Server 2005

1. Close SSMS
2. Type "%APPDATA%\Microsoft\Microsoft SQL Server\90\Tools\Shell\ at Run
3. Browse for MRU (Most Recent Users).dat and delete it.
4. Open SSMS



In SQL Server 2008

1.Close SSMS
2.Type "%APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\ at Run
3.In SQL 2008 mru.dat was renamed as Sqlstudio.bin, browse for that and delete
4. Open SSMS
 

Useful Commands for SQL Server Cluster

Below command is to view that SQL Server is a clustered or standalone

WITH prop_cte (cluster_prop)
AS
(SELECT SERVERPROPERTY('Isclustered') as cluster_prop)
SELECT CASE WHEN cluster_prop = 0 THEN 'Stand Alone' ELSE 'Clustered' end FROM prop_cte

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')  -- This displays on which cluster node SQL Server is running.

Select * from sys.dm_os_cluster_nodes  -- Displays nodes of the current SQL Server

 select * from sys.dm_io_cluster_shared_drives -- Displays cluster shared drives information from
   t-sql

How to change server collation in sql server ?

Steps

1. Backup all system databases
2. Script all logins and server roles
3. Copy SQL Server binaries into some folder.

Run the below command from command prompt.

4. Start /wait D:\MicrosoftSQL2005EE\setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=abcd1234 SQLCOLLATION=Latin1_General_CI_AS (New Collation Name)

 

qb - silent installation.

Instance Name - Name of the SQL Instance for which you want to update the collation.

RebuildDatabase - Hence we are rebuilding sql server databses with new collation settings.

SQLCollation -- Specify new collation name.

SAPWD -- SA password for server after rebuild .

 

It displays setup wizard, Click Yes to overwrite system databases and proceed.

After completion of setup wizard.

 

1. Open SSMS using new sa password

2. Attach all user databases

3. Execute the logins script

4. Execute the server roles scripts

 

Note -- This will rebuild all system databases.

 

 

 

 

can we drop multiple tables /objects without using dynamic sql

Can we drop multiple objects without using dynamic sql ?

Yes, It is possible. We can directly pass multiple table names using comma seperated.

drop table tabl1, tabl2, tabl3 and so on.

To do multiple inserts

Insert into tabl1 values (val1, val2), (val1, val2), (val1, val2)  and so on.

 

Shrink log for all databases

Below query helps to shrink transaction log for all databases

SELECT
      'USE [' + sd.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + smf.name + N''' )'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
    sys.master_files smf
    JOIN sys.databases sd
        ON smf.database_id = sd.database_id WHERE smf.file_id=2
and sd.database_id > 4;
 

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server Oracle

We may encounter the below error while setup linked server from SQL Server to Oracle
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server Oracle

Steps to troubleshoot

1. Verify the DNS using ODBC from Administrative tools by using Oracle Client Driver.
2. If DNS test connection is succeds then we need to setup linked server from SQL Server
3. SSMS - Server Objects - Linked Servers  -- New Linked Server
4. Linked Server -- OracleLinkServer
5. Select Other Data Source
6. Provider -- Select Microsoft OLEDB Provider for ODBC Drivers
7. Product Name -- Specify Oracle
8. Data Source -- Specify DSN which we created earlier
9. On Security (at left pane) Select Be made using the security context
10. Specify Oracle user name and password
11.On Server Options, make sure Data Access, RPC and RPC Out as True.
12. Finally hit OK

Hope that will fix the issue if oracle client components are installed on the machine.

To Query Linked Server

Select * from OpenQuery(linkedservername, 'query')

Select * from openquery(oraclelinkserver, 'select * from test')

Insert into openquery (oraclelinkserver,'select name, designation from test') values 'kalyan', 'dba'

Insert into openquery(oraclelinkeserver, 'select name, designation from test') select name, designation from test (sql server table)

delete from openquery(oraclelinkserver, 'delete from test where name =''kalyan''');

update openquery(oraclelinkserver,'select name from test where id=1') set name='kalyan kumar';

 

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON Microsoft SQL Server, Error: 15128

We normally faced this issue when SQL Login is set to "Enforce password policy” and “Enforce password expiration”, once set this policy and expiration can’t be changed using the below commands.

ALTER LOGIN kalyanlogin WITH CHECK_EXPIRATION = OFF,
         CHECK_POLICY = OFF;


SQLserver will return the following message
Msg 15128, Level 16, State 1, Line 1
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.


Resolution

Aleter Login kalyanlogin With Password="samepassword"
go
Aleter Login kalyanlogin With CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;


 

Checking Agent Job Status Using TSQL

The below post describes how to verify a job status using TSQL. There are two different ways to identify the job status other than SSMS

Below are the two stored procedures.
sp_help_job -- Using this stored procedure current_execution status column we can identify the job status.
In this stored procedure the is a parameter called @job_aspect which returns different set of data based on it is value

@job_aspect Parameter values are - JOB, STEPS, SCHEDULES, TARGETS, ALL

-- To view only Job steps of a given job, below is the syntax

USE msdb ;
GO
EXEC dbo.sp_help_job
@job_name = N'FullBackups - Every Night',
@job_aspect = 'STEPS' ;
GO


xp_sqlagent_enum_jobs (undocumented extended stored procedure).

xp_sqlagent_enum_jobs -- State column describes the state of the job
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions

Syntax to execute
EXEC xp_sqlagent_enum_jobs 1, ''

DTS Host Failed Error

Error message when you try to modify a SQL Server 2000 DTS package in SQL Server 2005: "The DTS host failed to load or save the package properly"

Method 1

Warning The following information contains steps to modify a file. Make sure that you back up the file before you modify it.
Open the SQLInstall:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE folder.
Note SQLInstall is a placeholder for the drive where SQL Server 2005 is installed.
Open the Sqlwb.exe.manifest file in Notepad or in another text editor.
Remove the following lines of code from the Sqlwb.exe.manifest file.
<dependency>
<dependentAssembly>
<assemblyIdentity type="win32" name="Microsoft.Windows.Common-Controls" version="6.0.0.0" processorArchitecture="x86" publicKeyToken="6595b64144ccf1df" language="*"></assemblyIdentity>
</dependentAssembly>
</dependency>
Save the changes, and then exit Notepad or the text editor.
Try to modify the SQL Server 2000 DTS package in SQL Server 2005


Method 2

Install SQL Server Enterprise Manager on the computer that is running SQL Server 2005. To do this, click Client Tools Only when you install SQL Server 2000.
Open the DTS package in SQL Server Enterprise Manager, and then save the DTS package. To do this, follow these steps:
Open SQL Server Enterprise Manager, and then connect to an instance of SQL Server 2000.
In SQL Server Enterprise Manager, expand the instance of SQL Server 2000, right-click the SQL Server Data Transformation Services folder, and then click Open Package.
Locate the .dts file, click Open, select the package, and then click OK to open the DTS package in SQL Server 2000 DTS Designer.
In SQL Server 2000 DTS Designer, click Save on the Package menu.
Modify the SQL Server 2000 DTS package in SQL Server 2005
Method 3
Uninstall Backward Compatibility components
Uninstall DTS Designer Components
Reistall it again

SQL Server Frequently Used Procedures and Functions

Use DataBase
Go
Sp_helpuser   -- Displays complete user details along with DB roles of the current database

Object_Name (‘Object_Id’) – Returns name of the object

DB_Name (‘DBID’) – Returns name of the database

Schema_Name(‘Schema_ID’) – Returns name of the schema

Select @@servername -- Returns ServerName

Sp_helpdb <database_name> -- Returns information about the given database.

Sp_helpserver – Displays current server details

SELECT SERVERPROPERTY('Edition') – Displays current SQL Server edition

SELECT SERVERPROPERTY(‘ProductLevel') – Displays current SQL Server Product Level

SELECT SERVERPROPERTY(‘ProductVersion') – Displays current SQL Server Product Version

SELECT SERVERPROPERTY(‘ServerName') – Displays current SQL Server Product Version

SELECT 'kill ' + CAST(spid AS VARCHAR) + '' from sys.sysprocesses where db_name(dbid)
='msdb'  -- Kill connections for a particular database.

Alter Database database_name set single_user with rollback immediate – To keep database in a
single user mode.

Alter Database database_name set multi_user  -- To keep database in multi user mode.

Use <db_name>
go
Dbcc open_tran  -- Displays are there any open transactions in the current database.

Select * from sys.sysprocesses where open_tran>0 – displays open transactions from all the databases on the server.

Sp_who 2  -- Displays list of processes that are currently present in the server.

To rename SQL Server
Sp_dropserver ‘servername’
Sp_addserver ‘newname’,local
Restart the sql service
To know the dependencies of an object
SELECT * FROM sys.dm_sql_referenced_entities('dbo.uspGetManagerEmployees','object')

Sp_depends ‘stored_proc’ – It doesn’t give accurate information and it also deprecated.

 

Database diagram support objects cannot be installed

Database diagram support objects cannot be installed

To fix the issue
Use databasename
go
Alter Authorization on database::database_name to login_name

Alter Authorization on database::kalyandb to kalyan_ro

SQL Server Data Pages, Extents etc

The fundamental unit of data storage in SQL Server is pages. All the data that stored inside the SQL Server is on data pages. The size of data page is 8Kb. Data is stored in a data file with extension .mdf.  

A page is an 8kb (8192 bytes) and 132 bytes are used for management and 8060 is the actual data page

1. Page Header (96 bytes)
2. Page Data
3. RowOffset –(36 bytes) It contains address of all records that are stored in data page.

The disk space is logically divided into pages and the pages are numbered contiguously from 0 to n. Disk Input and output operations are performed at page level.

Pages are grouped together into extents. An extent is a collection of 8 physical contiguous pages. An extent size is 64 kb. Data rows cannot span across pages.

There are different types of pages

Data Page -- Data rows with all data, except text, ntext, image, varchar(max), varbinary(max), and xml data.
Index Page -- Index Entries
Text / Image -- Large object data types : text, ntext, image, varchar(max), xml etc.,
Variable length columns when data row exceeds 8k ie., varchar, nvarchar, varbinary and sql_variant
Global Allocation Map, Shared Allocation Map -- Information whether extents are allocated.
Page Free Space -- Information about page allocation and free space available on pages.
Index Allocation Map – Information about extents used by a table or index per allocation unit.
Bulk Changed Map – Information about extents modified by bulk operations since the last Backup log statement per allocation unit.
Differential Changed Map – Information about extents that have changed since the last backup database statement per allocation unit.

Rows cannot span across pages, the maximum amount of data stored in a row is 8060 bytes, when a total row size exceeds 8060 bytes, SQL Server will dynamically move large size columns into ROW_OVERFLOW_DATA allocation unit of other page and maintains a 24 byte pointer on a original page in the IN_ROW_DATA allocation unit. This is done whenever Insert or update operations increase size of row beyond 8060 bytes. SQL Server will dynamically move columns back to data pages when size decreases.

Extents
An extent is 8 physically contiguous pages. There are two types of extents a) Uniform Extent and b) Mixed Extents

Uniform Extents are owned by a single object.
Mixed Extents are owned and used by different objects.

GAM & SGAM allocation maps are used for extent allocation.

GAM (Global Allocation Map) – It uses 1 bit to tell whether an extent is free or not. If the bit is 1, then extent is free, if the bit is 0 then extent is allocated. Each GAM covers 64000 extents, almost 4 GB of data.

SGAM (Shared Global Allocation Map) – It uses 1 bit to tell whether a mixed extent and also have at least one free page, If the bit is 1 then it is a mixed extent and has one free page, if the bit is 0 then extent is not used as mixed extent or it is a mixed extent and all pages are being used. Each SGAM covers 64000 extents, almost 4 GB of data.

PFS (Page Free Space) – PFS records the allocation status of each page, whether the page has been allocated or the amount of free space left. The PFS has one byte for each page, recording whether the page is allocated and if so, whether it is empty or 1 to 50 percent full, 51 to 80 percent full or 96 to 100 percent full.

After an extent has been allocated to an object, the database engine uses PFS to record which pages in the extent are allocated or free. This information is used when the database engine has to allocate new page. DB Engine uses PFS to find a page with free space for newly inserted row.
A PFS is the first page after the file header in a data file (page number 1), and GAM (page number 2), and then SGAM (page number 3).


SQL Server uses two internal data structures to track extents that are modified by bulk copy operations and extents modified since last full backup.

DCM – Differential Changed Map – This tracks the extents that have changed since the last backup database statement. If the bit for an extent is 1 then it is modified, if it is 0 then not modified. This will reduces that number of pages that differential backup must scan. The length of time that a differential backup runs depends on the extents that are modified since last backup but not size of database.

BCM – Bulk Changed Map – This tracks the extents that are modified since the last backup log statement. If the bit is 1 then the extent is modified by bulk logged operation, if it is 0 then it is not modified.

Negative SPIDs in SQL Server

How to deal with negative SPIDs in SQL Server?

Some times we found negative SPIDs causing blocking other processes in SQL Server. The source of a negative SPID is -2. So how to kill it?

KILL -2
Msg 6101, Level 16, State 1, Line 1
Process ID -2 is not a valid process ID. Choose a number between 1 and 2048

Kill wont allow you to pass that negative SPID, So how can we do this?

Run the following query
select req_transactionUOW from master..syslockinfo where req_spid = -2

This will return a 24 character GUID (Unit of work ID UOW) number, something like ‘EDF78022-8990-5757-E147-FA85B21C3654’

Kill ‘EDF78022-8990-5757-E147-FA85B21C3654’
After that run sp_who2 that negative spid has disappeared.

What actually that negative SPID is?

 

That is a distributed transaction SPID. An orphan distributed transaction SPID that involves Microsoft Distributed Transaction coordinator. In a nutshell a distributed transaction that involves more than one database located on different servers.

 

In order to maintain transactional consistency these transactions needs to be coordinated, as the transaction involves two phase commit, first it needs to commit on one database and then commit the transaction on second database, if it commit at first database and fails to commit at second database then whole transaction is rolled back and no data gets committed by any database. The process is responsible for coordinating these transactions is Microsoft Distributed Transaction Coordinator or MSDTC

 

The Negative SPID problem arises for whatever reason MSDTC loses track on one of these transactions, then that transaction will marked as in-doubt. If this happens, SQL Server will lock the SPID bound to that transaction but SPID will be -2.

 

This means it is no longer an internal process of SQL Server, and SQL Server doesn’t know whether it can commit or rollback so it causes blocking by holding locks on the tables that transaction is accessing. Sometimes it happens when MSDTC is in hung state or terminated.

 

 

 

The server principal owns one or more endpoint(s) and cannot be dropped

I encountered the below error while removing one of the domain user and fixed after executing the below command

USE master
go
Alter Authorization on Endpoint::Mirroring to sa
After tranferring ownership you can able to remove login.

Triggers

A trigger is a special type of stored procedure that executes in response to action on a table like insert, update and delete. It is a database object bound to a table and executes automatically when event occurs. We can’t invoke triggers explicitly.


Types of Triggers
 These are classified into two categories
 After Triggers or For Triggers
 Instead of Triggers

After Triggers
These will run after completion of any DML event like Insert, Update and Delete on a table. These after triggers will work only with tables not with VIEWS. After Insert, After Update and After Delete.

Magic Tables
 There are two temporary tables INSERTED, DELETED created by database engine automatically   when Insert / Delete occurs on a table.

 Syntax
 Create trigger trigger_name on Table_Name After / For (Insert /Delete/Update) As
Begin
Statements
 End
Examples of After Insert , After Update & After Delete Triggers

CREATE TRIGGER trg_items ON items FOR INSERT as
 BEGIN
                 INSERT INTO Items_audit SELECT item_name, item_image, item_description FROM INSERTED

END

CREATE TRIGGER trg_updateitems ON items FOR UPDATE as
BEGIN
                 INSERT INTO Items_audit SELECT item_name, item_image, item_description FROM DELETED
 END 

Instead of Triggers 
These instead of triggers is used to perform any action before the event fires, we can create instead of triggers in tables and views, whereas we can create after triggers in views.

 Create trigger trigger_name on table / view instead of Insert / Update / Delete AS
 Begin
         Statements
 End

Example
 Create table tblInsert_Log (UserName varchar(250), description varchar(500), Action_Date datetime, SPID int)

Create trigger trg_trapInsert on Items instead of insert As
BEGIN
                 Insert into Items select Item_name,
                 Insert into tblInsert_Log VALUES (SUSER_NAME(),'Inserted New Record', GETDATE(), @@spid)
 END

DDL Triggers To Audit Server Level Events - SQL Server

In my earlier post I explained how to log DDL events, now we will see another example how to audit or log server level events.

CREATE TABLE DDL_ServerAudit_Log

(

       EventTime            DATETIME,

       LoginName            VARCHAR(250),
       UserName             VARCHAR(250),
       DatabaseName         VARCHAR(250),
       SchemaName           VARCHAR(250),
       ObjectName           VARCHAR(250),
       ObjectType           VARCHAR(250),
       DDLCommand           VARCHAR(MAX)
)

 

CREATE TRIGGER trg_AuditServerEvents ON ALL SERVER FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN, GRANT_SERVER  AS

DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO kalyandb..DDL_ServerAudit_Log VALUES
(       REPLACE(CONVERT(VARCHAR(50),@data.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),@data.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)

To Verify 

USE master

CREATE LOGIN test WITH PASSWORD='Admin@123'
SELECT * FROM kalyandb..ddl_serveraudit_log


 Disable Trigger

DISABLE TRIGGER ALL ON DATABASE
GO
DISABLE TRIGGER ALL ON ALL SERVER

View triggers
select name from sys.triggers

select definition from sys.sql_modules where object_id = select object_id from sys.triggers where name = 'trigger_name')

Deleting triggers
drop trigger trigger_name on database


 

DDL Triggers to Audit DDL Events - SQL Server

DDL Triggers are not only to restrict however we can even audit the DDL changes. In regular triggers we have two magic tables called INSERTED, DELETED which helps to track the changes when any event fires. But to track the DDL changes we have a function EVENTDATA() which helps to track all DDL changes.

Below is the example to track all the DDL changes in the current database.

CREATE TABLE DDL_Audit_Log
(
       EventTime            DATETIME,
       LoginName            VARCHAR(250),
       UserName             VARCHAR(250),
       DatabaseName         VARCHAR(250),
       SchemaName           VARCHAR(250),
       ObjectName           VARCHAR(250),
       ObjectType           VARCHAR(250),
       DDLCommand           VARCHAR(MAX)
)
ALTER TRIGGER Trg_LogDDLEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS
DECLARE @data XML
SET @data= EVENTDATA()
INSERT INTO DDL_Audit_Log VALUES
(       REPLACE(CONVERT(VARCHAR(50),@data.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),@data.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)

DDL Triggers - SQL Server

DDL Triggers are similar to regular triggers that fires in response to an event. Unlike DML Triggers they don’t fire in response to Update, Delete, and Insert statements on a table. They fire in response to DDL events like CREATE, ALTER, DROP etc.,
 

DDL Triggers helps to audit the database level changes or you can restrict to perform any kind of drop operation or to restrict to perform any kind of schema updates. DDL Triggers can fire in response to a DDL Event in the current database or in the current server.

 

Note : DDL Triggers do not fire in response to events that affect temporary tables.

 

The scope of the DDL Trigger is depends on the event, for example if we setup a Create Table trigger created to fire in response to a Create Table event that can be occurs in the database or in the server, if we take Create Login trigger then it will occur only at server level not database level.

 

Example 

USE kalyandb
GO
ALTER TRIGGER prevent_drop ON DATABASE FOR DROP_TABLE AS
 PRINT 'You must Disable the trigger "Prevent Drop" to drop this table'
 ROLLBACK
 

Viewing Triggers
 

SELECT * FROM SYS.triggers 

View Definition of a Trigger

SELECT DEFINITION FROM SYS.sql_modules WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.TRIGGERS WHERE NAME='PREVENT_DROP')
 

To Know the scope of trigger

 

SELECT type, name, parent_class_desc FROM sys.triggers

UNION

SELECT type, name, parent_class_desc FROM sys.server_triggers ;

  

Disable Loopback in Registry

How to disable loopback

1. Start - Run - Regedit - Ok

2. HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\CONTROL\LSA\

3. Right click - New - Dword - DisableLoopbackCheck - Enter

4. Double click on DisableLoopbackCheck - Click Modify

5. In value box - Type 1 - OK, Quit registry

Pass table name dynamically to a Cursor

DROP TABLE table1
DROP TABLE table2
DROP PROCEDURE DynamicTableSP

USE kalyandb
CREATE TABLE table1 (id INT IDENTITY (1,1), NAME VARCHAR(20))
INSERT INTO table1 VALUES ( 'Apple')
INSERT INTO table1 VALUES ( 'BenQ')

CREATE TABLE table2 (id INT IDENTITY (1,1), NAME VARCHAR(20))
INSERT INTO table2 VALUES ('Samsung')
INSERT INTO table2 VALUES ('Sony')


CREATE PROCEDURE DynamicTableSP (@tname varchar(20)) AS
BEGIN
DECLARE @table_name VARCHAR(10)
DECLARE @sql VARCHAR(500)
SET @table_name = @tname
SET @sql = ' DECLARE @name VARCHAR(20) DECLARE db_cursor CURSOR FOR SELECT name FROM ' +@table_name + ' OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor '
EXEC (@sql)
END

EXEC DynamicTableSP 'table1'
EXEC DynamicTableSP 'table2'

Copy files between two different domains

We can copy files between two different domains using remote desktop local resources.

For example we have two different domains like India, USA, Now we need to copy files from a server which is in India to USA domain.

1. Create a network drive with India domain share path (using map network drive option on my computer)
2. Run -- MSTSC (Open Remote Desktop)
3. Type Server Name (USA Domain)
4. Click on options
5. Click on Local Resources Tab
6. Under local drives and resources -- More
7. Select the drive which you want to access on remote machine, we can select mapped drives as 
    well as local drives
8. Connect to remote machine
9. In Mycomputer you will see new drive along with local drives.
 

RunAs Examples

RunAs command used to execute any executable using different user credentials.

This can be done in two ways 1) Using windows 2) Using Command Prompt

From Command Prompt

runas /user:Domain\LoginName \\Sharepath
runas /user:windomain\kalyan \\ftpshare\\suresh    -- Provide credentials of given folder to access it from command prompt.




 

Cell Pointer Missing in Excel 2007

To hide / enable cell pointer in excel 2007


1. Office Button / File -- Options
2. Select Advanced
3. Check the option as per the screen shot

 

For Each Loop Container in SSIS

It defines a repeating control flow in a package. Loop implementation in the for each loop container is similar to the ForEach loop concept in programming language.
Types of EnumeratorsForeach File Enumerator – This enumerate files in a folder. We can get list of files which has extension .jpg from any folder and its subfolders
Foreach Item Enumerator – Enumerate values in an item
Foreach ADO Enumerator – Enumerate rows in tables
Foreach ADO.Net Schema Rowset Enumerator – Enumerate a schema
Foreach from variable Enumerator – Enumerate the value in a variable
Foreach nodelist Enumerator – Enumerates nodes in an XML document
Foreach SMO Enumerator – Enumerate a SMO Object.



Foreach Loop Container Properties1. Enumerator – Select appropriate enumerator (select Foreach File Enumerator)
2. Folder – Select source folder from where you want to enumerate files
3. Files – Specify extension (*.xls)
4. Drag the script task inside the for each loop container
5. Bind the variable User::File_Name to ReadOnly Variable
6. Below code snippet displays list of files which are matched with .xls extension from the specified folder.


MsgBox(Dts.Variables("File_Name").Value)
Dts.TaskResult = ScriptResults.Success

SSIS : Test connection failed because of an error in initializing provider. Unrecognized database format

Test connection failed because of an error in initializing provider. Unrecognized database format 'C:\Testdata.xlsx'.

 

If you encounter excel connection error then we need to type Excel 12.0 on Extended properties as below

 

 

Variables in SSIS

Variables
We can define variables in SSIS using variables window with specific scope. We can define variables at different scope levels and with different datatype, for example defining variable at package level, defining variable at tool level (specific to that particular tool for example variable at for loop level, variable at Execute SQL Task level etc.,)
Below are few examples to create variables

BIDS – View – Other Windows – Variables – It displays variables window and it consists the following icons
1. Add variable
2. Delete Variable
3. Show System Variables
4. Show All Variables
We can declare a variable with required data type and we can initialize value at the time of declaration itself.

To make use of variable we need to bind variable to tool, for example if you are working with script task below is the example.

Create two variables varInt (Int) = 123, varString (string) = “hello” at package scope level. Whenever if you create a variable then it should be bind with any control, otherwise we can’t access that variable.

Example using Script Task
1. Drag the script task to control flow window
2. Right Click – Edit – Edit Script
Public Sub Main()
' Add your code here
MsgBox(Dts.Variables.Count) -- This code returns zero because no binding done
Dts.TaskResult = ScriptResults.Success
End Sub
To bind the variables to script task
1) Right click on script task – Edit
2) ReadOnlyVariable – Click on Ellipse and browse the variables which you want to use in the script.
3) Insert the below snippet to get the variable names
        For i = 0 To Dts.Variables.Count - 1
            MsgBox(Dts.Variables(i).Name.ToString())
        Next
 

How to Rollup days to months and years in Excel Pivot

If we have multiple dates, we can rollup them into months and years in excel pivot but we need to make sure it satisfies the below things

1. Format should be same for all dates to verify this, we need to apply filter for date column and then click on dropdown, generally excel will group rows by years and months, in the first image the few values are grouped to years few are not, that means there is an issue with the format, first correct it and verify, the second image doesn’t displays any extra records means you can go a head with 2nd step.

2. Insert – Pivot
3. Drag the date column into columns or rows section
4. Right click on any date --- select group – hold ctrl and select months and years
5. Move the column to columns section or rows section as per your requirement.

How to change script language in SSIS

From 2008 onwards we can implement c# script along with vb script in SSIS.

To change language for script task.
BIDS -- Tools - Options
Business Intelligence Designers - Integration Service Designers -- General
Script Language -- Choose the script language from here.

Working with variables in SSIS

Variables

We can define variables in SSIS using variables window with specific scope. We can define variables at different scope levels and with different datatype, for example defining variable at package level, defining variable at tool level (specific to that particular tool for example variable at for loop level, variable at Execute SQL Task level etc.,)

Below are few examples to create variables

BIDS – View – Other Windows – Variables – It displays variables window and it consists the following icons

1. Add variable
2. Delete Variable
3. Show System Variables
4. Show All Variables

We can declare a variable with required data type and we can initialize value at the time of declaration itself.

To make use of variable we need to bind variable to tool, for example if you are working with script task below is the example.
Create two variables varInt (Int) = 123, varString (string) = “hello” at package scope level. When ever if you create a variable then it should be bind with any control, otherwise we can’t access that variable.

Example using Script Task

1. Drag the script task to control flow window
2. Right Click – Edit – Edit Script

Public Sub Main()
' Add your code here
MsgBox(Dts.Variables.Count) -- This code returns zero because no binding done
Dts.TaskResult = ScriptResults.Success
End Sub

To bind the variables to script task
1) Right click on script task – Edit
2) ReadOnlyVariable – Click on Ellipse and browse the variables which you want to use in the script. 
3) Insert the below snippet to get the variable names
       For i = 0 To Dts.Variables.Count - 1
                MsgBox(Dts.Variables(i).Name.ToString())
       Next

Restore database with restart

Restore database with restart option is available since SQL Server 2005 onwards. This option is very helpful while restoring very large databases. If a restore was failed due to some reason then using Restart
option we can start restore from the failed point which helps to save time in VLDB.

If a restore operation fails then database will be in restoring mode, to bring it to online then we cam use restore with replace or we can use restore with restart

Restore with Replace -- It performs restore from starting onwards.

Restore with Restart -- It performs restore from failed point onwards. (we can save time in this option)

Restore database kalyandb from disk = 'c:\fullbackups\kalyandb.bak' with restart

The database principal owns a schema in the database, and cannot be dropped - Drop User

I got the below error while removing user from a database.

The database principal owns a schema in the database, and cannot be dropped

There is a orphan user in my database, no schemas or no objects were defined by that user. No login is associated with it, even though some times we could n't delete it.

For suppose you have a user with name "kalyan" owns db_owner schema, to drop the user first you need to move that schema to another user. If you dont have another user to move schema then use the following syntax to do that.

use database name
Alter Authorization On Schema::db_owner TO db_owner

If you have datareader schema then use datareader in place of db_owner. Now drop the user it will allow you to drop the user from the database.

The job failed. The owner (sa) of job does not have server access.

The job failed.  The owner (sa) of job does not have server access.

If you change any job owner to domain user or any sql server internal user, you need to restart the SQL Agent for executing jobs without any interruption.

How to convert redgate backup into native backup format

Redgate has a utility to convert compression backup into Microsoft native backup format.

SQBConverter is the utility using which we can convert the .sqb file into .bak file

Copy the below files from any redgate installed machine and do conversion using SQBConverterGUI on target machine to convert sqb file into bak file.

RgSqbConvHelper.dll
SQBConverter.exe
SQBConverterGUI

Creating an instance of the COM component with CLSID

We may get the below error when we are trying to edit the job steps created earlier.

Creating an instance of the COM component with CLSID {B380D7F6-CAEF-4A56-B9BB-FOF3CD976AA2} from the IClassFactory failed due to the following error: a101d110. (Microsoft.SqlServer.ManagedDTS)

To fix the issue we need to re-register the dts.dll

From command prompt
c:> regsvr32.exe dts.dll

Hint : Browse dts.dll in Microsoft SQL Server\version number\DTS\binn

SQL Agent Job Notifications

If we have a mission critical application then all Agent jobs which support that application is also that much critical, if any SQL Job fails then immediately we need to notify to a particular person or a group. Using SQL Server Operators we can achieve this.

Below is the procedure

Initially we need to configure database mail to send notifications from SQL Agent Jobs.
Refer - http://calyansql.blogspot.in/2012/10/configure-database-mail-sending-status.html

Steps
1. Configure Database Mail
2. SSMS –SQL Server Agent – Operators – Right Click – New Operator
Name – Notification Operator
Email name – test@yahoo.com
Pager e-mail name – test@yahoo.com
3. Under Jobs – Select job for which you want to configure notifications
4. Job – Properties – Notifications
 – Check Email – Select Operator from dropdown list and condition
 – Check Email – Select Operator from dropdown list and condition
5. SQL Server Agent – Properties
6. Select Alert System – Check Enable mail profile -- choose appropriate profile name from the mail profile dropdown list
7. Check Enable fail-safe operator – select operator name and check Email, Pager under Notify using section.
8. Click OK
   

Top Jobs -- SQL Agent Standard Reports

This standard report helps to identify the following things very quickly

20 Most Frequently Executed Jobs   -- Provides information about frequently executed jobs.
20 Most Frequently Failing Jobs  -- Provides information about frequently failed jobs, which helps to fix the job failures quickly.
20 Slowest Jobs  -- Provides information about the jobs which takes long time to execute, which helps to identify the performance.

Implement SQL Server Agent Job Output Files

For SQL Server Agent Jobs we can setup text files to store the output of the job.

If we want to see the output of any log we normally use the below procedure for verification.

SSMS -- SQL Server Agent -- Job Name -- Properties -- Steps -- Edit --  Advanced

Click on View beside the log to table check box, It displays output of the job that is executed recently.

If there is any critical job we need to maintain daily job output in a separate file with date and time stamp, we can achieve with using SQL Server Tokens

Suppose we have job called DB_Integrity_Verification, follow the below steps to create a job output file using the below procedure.

SSMS -- SQL Server Agent -- DB_Integrity_Verification -- Steps -- Edit -- Advanced

In Output file Text Box paste the below syntax

C:\JobLogs\DB_Integrity_Verification_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

Click on OK and close the job.

If you execute the job it creates a text file with date and time stamp in the C:\JobLogs folder, make sure JobLogs folder exists on C: Drive.

Configure Database Mail in SQL Server 2008

1. SQL Server SSMS -- Management -- Database Mail Right Click -- Select Configure database mail
2. Click on Next Button
3. Select Setup Database Mail by performing the following tasks -- Next
4. Profile Name -- Kalyan_Mail_Profile
    Description -- This profile is to test SQL Server Job Notifications -- Add
5. Provide valid email address and smtp address along with port number - Ok
6. Click n Next Button
7. Select the profile which you recently added -- Next -- Next -- Finish

With the above steps database will be configured in SQL Server, You can test this by using below steps


1. SQL Server SSMS -- Management -- Database Mail -- Right Click -- Send Test Mail
2. Ensure mail profile name is correct
3. Specify email Id
4. Hit Send Test Mail Button

Database Mail Catalog views

select * from msdb..sysmail_allitems
select * from msdb..sysmail_sentitems
select * from msdb..sysmail_unsentitems
select * from msdb..sysmail_faileditems
select * from msdb..sysmail_mailattachments
select * from msdb..sysmail_event_log
select * from msdb..sysmail_profile
select * from msdb..sysmail_account

A database snapshot cannot be created because it failed to start

A database snapshot cannot be created because it failed to start

a) When you receive that error message you can run the checkdb on all databases and try what are trying to invoke.

Security Related Stored Procedures

1. Sp_addlogin – Create a new login in SQL Server Instance
Syntax : sp_addlogin login_name, password, [def db], [def language]
Ex : sp_addlogin ‘kalyan’, ‘kumar’, master

2. Sp_adduser – Creates a new user and mapped to login in the current database.
Syntax : sp_adduser ‘login_name’,’user_name_in_db’
Ex : use kalyandb;GO; sp_adduser ‘kalyan’,’kalyan’

3. Sp_addrolemember – Adds login to database role in the current database
Syntax : sp_addrolemember ‘role_name’,’login_name’
Ex : sp_addrolemember ‘db_owner’, ‘kalyan’

4. Sp_changedbowner – changes owner of the current database
Syntax : sp_changedbowner ‘login name’
Ex : sp_changedbowner ‘kalyan’

5. Sp_defaultdb – changes the default database for any login
Syntax : sp_defaultdb login_name, database
Ex : sp_defaultdb ‘kalyan’,’master’

6. Sp_droprolemember – Removes server role of the specified login
Syntax sp_droprolemember role_name, login_name
Ex : sp_droprolemember ‘db_owner’, ‘kalyan’

7. Sp_dropuser – Removes user from the current database
Syntax : sp_dropuser ‘user_name
Ex: sp_dropuser ‘kalyan’

8. Sp_droplogin / drop login – Removes login from SQL Server Instance
Syntax : sp_droplogin loginname
Ex: sp_droplogin ‘kalyan’ (or) drop login kalyan

Execute As in TSQL

Execute As

By default if you login into SQL Server a session gets started when login and session ends at logoff. What ever the transactions / operations we performed those are in specific to permissions against the login.

Using this EXECUTE AS option we can change the context of the session switched from current login to different login.

For Example you login into SQL Instance using “sa” login.

EXECUTE AS USER = 'TestUser'
SELECT USER_NAME()

SELECT * FROM category

SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); -- List all the permissions for the TestUser has on the current database.
       
REVERT – Switch back to original login session ie., (“sa”) in this case.

SELECT USER_NAME(), SUSER_SNAME()

Note : Best practice is specify a login / user name which has least privileges.

Grant execute on all stored procedures in SQL Server

Prior SQL 2005 there is no specific methods to grant execute permission on all stored procedures at one step. We need to give permission one by one or need to a script to do it.

SQL 2005 has provision to grant execute permission at database level. That means if we issue the below statement, it will grant execute permission on all existing stored procedures and scalar functions and for newly created ones.

Grant Execute to ‘Domain\User’
Grant Execute to ‘SQL User’
Grant Execute to ‘Windows Group’

This can be done in several ways. We can create a separate role and we can assign that role to SQL Logins / Windows users / Windows Groups

Create Role DB_ProcsExecutor
Grant Execute to DB_ProcsExecutor
Using sp_addrolemember we can assign role to any login as below
sp_addrolemember 'DB_ProcsExecutor', 'TestUser'

Display AD Users information using TSQL

xp_logininfo returns information about windows users and windows groups.



EXEC xp_logininfo 'Group_Name', 'members'

EXEC xp_logininfo 'Login_Name', 'all'


EXEC xp_logininfo 'BUILTIN\Administrators' , 'members'

EXEC xp_logininfo 'Domain\ADGroup', 'members'

EXEC xp_logininfo 'kalyan','all'

Schema Change History Report -- SQL Server Standard Reports

The schema change history report displays the changes made using DDL Commands. It tracks changes using default trace. If default trace is enabled on the server then we can run this report and view what are all the objects altered, deleted, created using this report.

To verify default trace option
sp_configure 'default trace enabled'

If the run value is 1 then it is enabled. To enable this option
sp_configure 'default trace enabled', 1
Reconfigure with override

This report has 6 columns to display history information

Databae Name – Displays name of the database on which changes happened.
Object Name – Displays name of the object on which changes made.
Type – Display type of object (ex : user defined object )
DDL Operation – Displays which action performed(Create / Alter / Drop)
Time – Display when DDL action was performed
Login Name – Displays the account name used to make changes.

Screen Capture functionality in Word 2010

Screen Capture functionality in Word 2010

While preparing any document with screenshots typicall we use printscreen key to capture the active window and then we paste it on win-word and will do some kind of massaging. In Ms-Word 2010 there is a cool feature in Insert Menu – Insert Screenshot

When you select that option it shows all the active windows from where you can choose which one you want to capture into document.

ROBOCOPY - Examples

Robocopy is a command line file copy utility which comes with windows tool kit. It become part of operating system from windows vista. Unlike other commands like copy, Xcopy, Robocopy has various advantages, i) it is designed for reliable copy of entire folders of any size (large size) ii) it copies files over network and gives resume copy feature when there is any disruption in network.

Syntax
robocopy source destination [switches]

Example

C:\kalyan>robocopy "\\Softwares\OperatingSystems\WindowsXP" C:\temp" /E /Z /Purge
/log:C:\robocopy.log

Explanation

\\Softwares\OperatingSystems\WindowsXP -- Source folder
C:\Temp -- Destination Folder
/E  -- Copy folders even it is empty
/Z  -- Copy files in restartable mode
/PURGE -- Removes all folders or files in destination folder if those are not exists in source location
/log:filename -- Writes log in the specified location with complete details like what files copied, what files / folders removed, how much time taken, tranfer rate etc.,

Sparse Columns in SQL Server

Sparse Columns in SQL Server

Sparse column is introduced in SQL Server 2008, which is designed to store null values. If you store any null value in sparse column it doesn’t occupy any space on the database. If you store any non null value in sparse column it takes 4 bytes extra space. For example if you store bigint in database usually it requires 8 bytes if you store this value in sparse column it will occupy 12 bytes.

CREATE TABLE Students_Sparse (Id int IDENTITY(1,1), NAME VARCHAR(30), Address1 VARCHAR(30), Address2 VARCHAR(30), ADDRESS3 VARCHAR(30) SPARSE)

CREATE TABLE Students (Id int IDENTITY(1,1), NAME VARCHAR(30), Address1 VARCHAR(30), Address2 VARCHAR(30), ADDRESS3 VARCHAR(30))

INSERT INTO students_sparse VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

INSERT INTO students VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

-- If you insert non null values in to sparse column the size of table will be huge then an ordinary column table

sp_spaceused Students_Sparse

go

sp_spaceused Students

TRUNCATE TABLE Students_Sparse

TRUNCATE TABLE Students

Below example shows if you leave the sparse column by entering null values the column doesn’t occupy any space and there will be a huge change in space

INSERT INTO students_sparse VALUES ('anand','Hyderabad','AP',null)

go 1000

INSERT INTO students VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

-- If you insert non null values in to sparse column the size of table will be huge then an ordinary column table

sp_spaceused Students_Sparse

go

sp_spaceused Students

Filtered Index in SQL Server

Filtered Index is a new feature in SQL Server 2008, it is an optimized non-clustered index created on a subset of data. The definition of the index will have where clause in it. It provides huge performance improvement when we query a subset of data from a large table. These filtered indexes are relatively small when comparing to normal Indexes and queries will be less expensive in terms of I/O.

Note: We can’t create a filter index on complex WHERE clause queries and it doesn’t allow LIKE in where clause, we can use simple operators. Filtered Indexes can be rebuild online.

CREATE INDEX idx_hostName ON Total_Hosts(ServerName) WHERE Active = 1

SELECT si.index_id, si.name, si.type_desc, si.filter_definition FROM sys.indexes si, sys.tables st

WHERE si.object_id = st.object_id AND st.name= ‘Total_Hosts’

SQL Server Agent with Express Edition

If you install SQL Server Express Edition, it will install only Database Engine Services.

In configuration manager it shows SQL Server and SQL Server Agent services, but SQL Server agent in disable state. Why it installed SQL Server Agent is because if you perform upgrade from Express Edition to any other edition it enables the service after validating the installation and also no need to replace all files at the time of upgrade, because of that it installs SQL Server Agent service along with Express Edition.

Express Edition comes in different versions.

Express Edition with SSMS

Database Engine, Import and Export Data (No SSMS)

Express Edition with Advanced Features

Database Engine, SSMS, Reporting Services Configuration Manager, BIDS, Import and Export Data.

SQL Server MetaData Functions

fn_helpcollations() -- Lists all the collations supported by SQL Server
select * from sys.fn_helpcollations()

fn_servershareddrives() -- Lists all shared drives used by cluster
select * from fn_servershareddrives()

fn_virtualfilestats(DatabaseId, FileId) -- Displays I/O statistics for data and log files.
select * from fn_virtualfilestats(1,1)
select * from fn_virtualfilestats(DB_ID('kalyandb'),1)
select * from fn_virtualfilestats(DB_ID('kalyandb'),2)
select * from fn_virtualfilestats(Null,Null)

 fn_virtualserverfilemodes() -- Displays clustered instance nodes

Recommended SQL Server files to be in anti-virus exclusions

Below SQL Server related files can be included in exclusion list of Anti-Virus, because the files may locked while scanning or sometimes anti-virus may prevent accessing them or we may face performance degradation while accessing them.

.Mdf -- SQL Server Primary Data File

.Ldf  -- SQL Server Transaction Log File

.Trn  -- Transaction Log Backup

.Bak -- Full Backup

.Diff  -- Differential Backup

.Ndf  -- SQL Server Secondar Data File

.Sql   -- SQL Script Files



Fix : Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]

When I am trying to open SQL Server 2012 configuration manager i got the above error, To fix this

1. search for the file "sqlmgmproviderxpsp2up.mof"  (usually it is located under shared folder under version folder).
2. After locating the file, open command prompt and run the below command
mofcomp C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof
Output
Microsoft (R) MOF Compiler Version 6.1.7600.16385
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmp
roviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
Done!
3. After it is stored in the repository, close the SSMS if it already open
4. Now open SQL Configuration manager, it will work.

SQL Server Details

Below query displays the sql server name and product details along with CPU and RAM details
SELECT SERVERPROPERTY('ServerName') AS [SQLServer],

SERVERPROPERTY('ProductVersion') AS [VersionBuild],SERVERPROPERTY('ProductLevel') AS [Product],SERVERPROPERTY ('Edition') AS [Edition],SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly],SERVERPROPERTY('IsClustered') AS [IsClustered],[cpu_count] AS [CPUs],round(cast([physical_memory_in_bytes]/1048576 as real)/1024,2) AS [RAM (GB)]FROM [sys].[dm_os_sys_info]

Retrieving Resultset Using XML

We can generate XML documents by using SQL Server FOR XML. There are 4 methods we can include with FOR XML are AUTO, EXPLICIT, PATH, RAW.

FOR XML AUTO - Displays nested XML tree with each column representing as a single element.

Sample Data

Examples

SELECT * FROM TestIdentity WHERE id = 1 FOR XML AUTO <TestIdentity Id="1" NAME="Kalyan" Phone="9090909092" City="Hyderabad" />

SELECT * FROM TestIdentity AS Students WHERE id = 1 FOR XML AUTO  <Students Id="1" NAME="Kalyan" Phone="9090909092" City="Hyderabad" />
SELECT
* FROM TestIdentity AS Students WHERE id in (1,2) FOR XML AUTO,ELEMENTS
<Students><
Id>1</Id><
NAME>Kalyan</NAME><
Phone>9090909092</Phone><
City>Hyderabad</City></
Students><
Students><
Id>2</Id><
NAME>Kishore</NAME><
Phone>8080808082</Phone><
City>Secunderabad</City></
Students>
SELECT * FROM TestIdentity WHERE id IN(1,2) FOR XML AUTO, ROOT('students')
<students><
Students Id="1" NAME="Kalyan" Phone="9090909092" City="Hyderabad" /><
Students Id="2" NAME="Kishore" Phone="8080808082" City="Secunderabad" /></
students>
SELECT CASE WHEN LEN(NAMES)>0 THEN LEFT(NAMES, LEN(NAMES)-1) ELSE '' END AS NAMES_CSV
FROM (SELECT T.NAME + ',' FROM TestIdentity T FOR XML PATH('')) TMP_NAMES (NAMES)
Kalyan,Kishore,Krishna,Raju,Ramesh,Shiva,Mallik,Naveen,Madhu,Srinu,Harish,Ramkumar

Using the below query we can create as csv

SELECT City, STUFF((SELECT ',' + T.NAME FROM TestIdentity T WHERE T.City = Ti.City FOR XML PATH('')),1,1,'')
FROM TestIdentity Ti GROUP BY City