Script: Delete back up file

--script to delete file from sql server instance1

Declare @DeleteDate varchar(50)
Declare @DeleteExecuteSQL varchar(1000)
Set @DeleteDate = cast(DATEADD(day,-3,GetDate()) as varchar(50))
Set   @DeleteExecuteSQL =
'EXECUTE master.sys.xp_delete_file 0,N''V:\MSSQL$instance1\BackUp\Daily\Client000\' + @@servername + '\User'',N''bak'',N' + quotename(@DeleteDate,'''') +  ',1'

print @DeleteExecuteSQL
--Execute (@DeleteExecuteSQL)

Deleting duplicate rows

A very cool technique to find and delete duplicate rows and just keep one unique row uinsg CTE ( common table expression) in SQL 2008

Initially the table had data

updateId  ptno
1                95689
2                95689
3                95689
4               91458
5               91000

Result desired
1               95689
4               91458
5               91000

(3 unique rows)

SELECT UpdateID,Ptno INTO #TempPtno From  dataTable1
WHERE uploaded = 0
AND deleted=0
AND flag=0 ;

/* Delete Duplicate records */
WITH CTE ( UpdateId,Ptno, DuplicateCount)
AS
(
SELECT UpdateId,Ptno,
ROW_NUMBER() OVER(PARTITION BY Ptno ORDER BY ptno) AS DuplicateCount
FROM #TempPtno
)
DELETE
FROM CTE
WHERE DuplicateCount > 1



**If there is a semicolon missing at the end of the statement before WITH CTE the following ing error will be thrown
Msg 319, Level 15, State 1, Procedure  procName , Line number
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.