Thursday, October 30, 2014

How to copy non-clustered indexes to subscriber in snapshot replication



In snapshot replication by default Non-cluster indexes are not copied to subscriber below is the work around for copying Non-cluster indexes to subscriber in Snapshot replication.

Step 1:
  - Login into the Publisher server
  -  Expand the Replication folder and Local Publications
  -  Right click on the publisher and select properties





Step 2:
                In Publication Properties window select Articles Tab à Select the Article Properties
               

Step 3:
  -  In Popup select Set Properties of All Table articles to copy all objects Non-Cluster indexes
  -  Select Set Properties of Highlighted table article to change the option to copy Non-cluster indexes of specific table


Step 4: 

                In Properties for All Tables Articles window change the below highlighted property from False to True.
               

In next run of snapshot will copy all noncluster indexes to subscriber.

Saturday, October 25, 2014

Validate all views in the database


There is an sp that validates the metadata and the   same sp we can use to validate

set nocount on
select ' print ''' +name +'''
exec sp_refreshview ['+name+']' + CHAR(10)+ '
go '
from sys.views


Execute  the above script it will give script to run sp_refreshview on all views. Run the scripts generated using above sql statement I will give error link below screen


Tuesday, October 21, 2014

SQL Server and Build Numbers


Build chart lists all of the known Service Packs (SP)

RTM
SP1
SP2
SP3
SP4
SQL Server 2014
12.0.2000.8 12.00.2000.8




SQL Server 2012
11.0.2100.60 11.00.2100.60
11.0.3000.0
or 11.1.3000.0
11.0.5058.0
or 11.2.5058.0


SQL Server 2008 R2
10.50.1600.1
10.50.2500.0/
10.51.2500.0
10.50.4000.0/ 10.52.4000.0
10.50.6000.34/ 10.53.6000.34

SQL Server 2008
10.0.1600.22 10.00.1600.22
10.0.2531.0 10.00.2531.0
or 10.1.2531.0
10.0.4000.0 10.00.4000.0/
10.2.4000.0
10.0.5500.0 10.00.5500.0/ 10.3.5500.0
10.0.6000.29 10.00.6000.29/ 10.4.6000.29

SQL Server 2005
9.0.1399.06 9.00.1399.06
9.0.2047/9.00.2047
9.0.3042/ 9.00.3042
9.0.4035/ 9.00.4035
9.0.5000/ 9.00.5000
SQL Server 2000
8.0.194 8.00.194
8.0.384 8.00.384
8.0.532 8.00.532
8.0.760/ 8.00.760
8.0.2039 8.00.2039
SQL Server 7.0
7.0.623 7.00.623
7.0.699 7.00.699
7.0.842 7.00.842
7.0.961 7.00.961

Thursday, October 16, 2014

DBCC CHECKIDENT




-- Create Table Script to test CHECKIDENT

CREATE TABLE DBCC_ReSeed
( ID INT IDENTITY(1,1) NOT NULL,
DateAdded DATETIME NOT NULL )

-- Inserting values to DBCC_ReSeed table
INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())
INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())
INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())
INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())
INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

SELECT * FROM DBCC_ReSeed

--- Check the current value which produces the below output… 
DBCC CHECKIDENT ('DBCC_ReSeed', NORESEED)

---  Output like :  Checking identity information: current identity value '5', current column value '5'.
--- Now reset the identity value to 20 so that the next time when we insert data into DBCC_RdSeed table , the value will be 21…

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, 20)

----- Output like :   Checking identity information: current identity value '5', current column value '20'


--  Add another row and check identity value. The row inserted will have a value of 21… 

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed



-- Delete data in table and reset to start from 1

DELETE FROM DBCC_ReSeed

DBCC CHECKIDENT('DBCC_ReSeed')
-- After deleting data identity value didn’t reset and the value will be remain same  
---  Output like :  Checking identity information: current identity value '20', current column value '20'.


DBCC CHECKIDENT('DBCC_ReSeed', RESEED, -1)

---  Output like :  Checking identity information: current identity value '21', current column value '-1'.


INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());
INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed