SQL Server - Deleting the contents from log file

 You can shrink the log with the following Statements:

USE [MyDatabase]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([MyDatabase_log], 1)
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT
GO

This snippet can be found on various sites in the net, but be aware that it's not a good practice to kill the log like this because of recovery. After doing this, you can only recover your database to the last full or full+incremental backup.

How to split column with delimiter into two columns in SQL Server Split

We can use Case and  CHARINDEX to split column with delimiter into two columns in SQL Server

select 
    case when CHARINDEX('_',name)>0 
         then SUBSTRING(name,1,CHARINDEX('_',name)-1) 
         else name end firstname, 
    CASE WHEN CHARINDEX('_',name)>0 
         THEN SUBSTRING(name,CHARINDEX('_',name)+1,len(name))  
         ELSE NULL END as lastname
from emp

you can use CASE command to control is last name available.

MS SQL Server:

Query 1:

declare @t table (id int, name  varchar(50))

insert into @t (id,name) values( 1    ,'abc_rao')
insert into @t (id,name) values( 2    ,'nani')
insert into @t (id,name) values( 3    ,'hari_babu')
insert into @t (id,name) values( 4    ,'kalibabu')
insert into @t (id,name) values( 5    ,'ab_tan')

select 
    case when CHARINDEX('_',name)>0 
         then SUBSTRING(name,1,CHARINDEX('_',name)-1) 
         else name end firstname, 
    CASE WHEN CHARINDEX('_',name)>0 
         THEN SUBSTRING(name,CHARINDEX('_',name)+1,len(name))  
         ELSE NULL END as lastname
from @t

Results:

| FIRSTNAME | LASTNAME |
|-----------|----------|
|       abc |      rao |
|      nani |   (null) |
|      hari |     babu |
|  kalibabu |   (null) |
|        ab |      tan |

Error "A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe"

Error "A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe"

Solution:
This is login authenticate problem. Please check the following things:

• In the SQL Server Properties, please check that "SQL Server and Windows Authentication Mode" is enabled, if not enable, please enable and restart the SQL Server.
   
• Is Login have the permission to particular Database?
   
• This might be a firewall issue as you mentioned it only happens when security agent installed. Please make sure you have properly configured security agent to allow Database Engine Services accesses.
   

Sql-Server, find Duplicate Rows with Count

select Count(ID), Name, Email,Comment
FROM TableName     
group by Name, Email,Comment
having Count(ID)>1