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 

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

The query has been canceled because the estimated cost of this query (302),Contact your Administrator

SET QUERY_GOVERNOR_COST_LIMIT value

Add This Line in Store Procedure


Using SET QUERY_GOVERNOR_COST_LIMIT applies to the current connection only and lasts the duration of the current connection. Use the query governor cost limitoption of sp_configure to change the server-wide query governor cost limit value. For more information about configuring this option, see sp_configure and Setting Server Configuration Options.

Truncate Log FIle in SQl Server

dbcc shrinkfile(accord_log,2)
backup log accord with truncate_only
dbcc shrinkfile(accord_log,2)