Tuesday, January 17, 2012

How to Resolve TCP/IP Connection Named Pipe problem in sql server with Windows 7

Note : Some Times when we connect our system Sql server to another sql server then it gives error of Named Pipe as given below:



Solution : Follow given blow steps with screen shots.

My Compuret -> Manage

 All Status should be Enabled accept VIA Protocol

   All Status should be Enabled accept VIA Protocol

Check Properties of TCP/IP  
 TCP/IP Port should be 1433

Now Go To Control Panel and Choose Firewal Option

  Choose Advance Settings Option

Select 
1.  Inbound Rule Option from left and then 
2. Choose New Rule Optioin

 select Port Option
 Choose TCP Option and Enter 1433 port no (Which is Sql server default port No.)
 Choose Next
 Choose Next

Enter Rule Name: Sql then Click Finish
 Now You will find that Rule which You have made will be shown in List. As selected given below:

 Now connect our SQL SERVER 2005 it will definitely connect 

Monday, January 2, 2012

Time Sorting if AM and PM Charaters are Exist

select data,data_id from master_data where master_id = 9 and active = 1
orderby(substring(data,charindex('',data)+1,len(data))),data

Output :

07-12 AM     
09-10 AM     
01-03 PM     
03-05 PM    
05-07 PM   

How to delete duplicate records from a Table (just copy paste it)

Note: it some times happen whenever a table 
do not have primary and it 
consist Duplicate Records
 
--Create Table
 
create table dupemp
 (
name varchar(20),salary int,deptno int
)

select * from dupemp

--Insert Records

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('inthiyaaz',2000,10)

insert into dupemp(name,salary,deptno) values('Khaja',3000,20)

insert into dupemp(name,salary,deptno) values('Khaja',3000,20)

insert into dupemp(name,salary,deptno) values('Naseeb',3000,20)

--Main Query to find and delete 
Duplicate Records

with CTE as(select row_number() over(partition by name order by name) row,name,salary,deptno from dupemp )
delete from CTE where row>1