How to Delete Records from Oracle Table

How to remove or delete records in Oracle

Order by Clause in Oracle Table:

Order by Clause in Oracle Table:

How to Insert,Update & Select Records Table in Oracle

How to Insert and Update Records Table in Oracle

Create Table from Existing Table

Create Table from Existing Table:

How to Create and Modify a Table in Oracle

How to Create and Modify a Table in Oracle:

FTP through SQL Server

/***********************************************************************************

DESCRIPTION
1. copy all backup files from a source folder into remote FTP server
2. move all backup files from a source folder into another local folder
3. Tested in SQL Server 2005

AUTHOR
Siddique

DATE
30/11/2007

************************************************************************************/
CREATE PROCEDURE [dbo].[P_FtpPutFile_ReportServer]
(
@FTPServer varchar(128)
,@FTPUser nvarchar(128)
,@FTPPWD nvarchar(128)
,@FTPPath nvarchar(128)
,@SourcePath nvarchar(128)
,@SourceFile nvarchar(128)
,@workdir nvarchar(128)
,@DestPath nvarchar(128)
,@binaryFile bit = NULL
)
AS
BEGIN
SET NOCOUNT ON;

IF(@binaryFile IS NULL)
begin
SET @binaryFile = 1
end

DECLARE @cmd varchar(1000) -- command to execute
DECLARE @workfilename varchar(20) -- file to store ftp command batch

SET @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
SET @FTPServer = REPLACE(REPLACE(REPLACE(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
SET @FTPUser = REPLACE(REPLACE(REPLACE(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
SET @FTPPWD = REPLACE(REPLACE(REPLACE(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
SET @FTPPath = REPLACE(REPLACE(REPLACE(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

-- insert FTP servername to @workfilename
SET @cmd = 'echo open ' + @FTPServer + ' > ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- insert FTP username to @workfilename
SET @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- insert FTP password to @workfilename
SET @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

IF @binaryFile = 1
begin
-- set the file transfer type to binary
SET @cmd = 'echo bin' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
end

-- change the working directory on the remote computer
SET @cmd = 'echo cd ' + @FTPPath + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- copy all files of a type @SourceFile from @SourcePath into @FTPPath
SET @cmd = 'echo mput ' + @SourcePath + @SourceFile + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- end the FTP session with the remote computer and exit ftp
SET @cmd = 'echo quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- execute the @workfilename (i - turn off interactive prompting for the mput command)
SET @cmd = 'ftp -i -s:' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd--, no_output

-- move all files of a type @SourceFile from @SourcePath into @DestPath (Y - suppress prompting to overwrite)
SET @cmd = 'move /Y ' + @SourcePath + @SourceFile + ' ' + @DestPath
EXEC master..xp_cmdshell @cmd, no_output
END

ARCHIVE FILES - ZIP FILES - MOVE IT TO FTP

OBJECTIVE IS TO ARCHIVE FILES, ZIP IT, FTP TO REMOTE AND THEN MOVE IT TO DIFFERENT FOLDER:
1. ARCHIVE FILES MEANS: MOVE THE FILES FROM SOURCE TO BACKUP FOLDER. THEREFORE, THE SOURCE FOLDER PATH DON'T HAVE THE FILES AFTER MOVING
2. ZIP USING 7Za (http://sourceforge.net/projects/sevenzip/)
2. FTP to Remote Server
3. Once FTP, Move it to Final Deletion Folder. Deletion folder means final stage for deletion of files based on the age, as we have backup those files to FTP.
Step I:
Move files older than 6 days from Any Server (In this Demo, we are moving mails. Moving means copy into the destination folder and delete from source folder) and Zip Files
robocopy "C:\Program Files\Mail Enable\Postoffices\company\mailroot\Wilkinson.Jose\Inbox" "C:\backup-may\Wilkinson.Jose" *.MAI /s /MOV /minage:6
Cd C:\backup-may\Wilkinson.Jose
7za a -tzip -mx3 C:\backup-may\Wilkinson.Jose.zip
del C:\backup-may\Wilkinson.Jose\*.MAI
cd..
Cd C:\backup-may\George.King
7za a -tzip -mx3 C:\backup-may\George.King.zip
del C:\backup-may\George.King\*.MAI
Step 11:
Create one text file and name it as 'ftpcmd.txt'
Inside the file, you need to write your ftp login credentials.
1) Open FTP IP address
2) Username for FTP
3) Password for FTP
4) Type bin to set the file transfer type to binary
5) change the working directory on the remote computer
6) copy all files of a type @SourceFile from @SourcePath into @FTPPath
7) End the FTP session with the remote computer and exit ftp
open 10.0.0.122
username
password
bin
cd /SCN
mput D:\mailbackup\*.rar
quit
Step III:
Copy files to FTP and Move to Different Folder
Once the above file 'ftcmd.txt' is ready, we have to create one batch file with any name that you are going to schedule.
D:
Cd D:\Imp\FTP
ftp -i -s:D:\Imp\FTP\ftpcmd.txt
move /y D:\mailbackup\*.rar D:\mailbackup\moved
exit

Statistics Update - SQL SERVER

TO CREATE STATISTICS SCRIPT FOR ALL OUR REQUIRED DATABASES:

CREATE PROCEDURE SPUpdateStats

AS

Set Nocount on
Declare db Cursor For
Select distinct(Databasename) as Name FROM T_TableReindexing(nolock)
--Select name from master.dbo.sysdatabases where name in ('dbmonitor') and name
--not in ('master','TempDB', 'msdb', 'model')

Declare @dbname varchar(60)
Declare @execmd nvarchar(150)

Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
begin
if @dbname is null
Begin
Print 'null Value'
Return
end
else
Begin
PRINT '###########################################################################'
PRINT 'Update Statistics in ' + @dbname
SELECT @execmd = 'USE ' + @dbname + ' EXEC sp_updatestats'
Print (@execmd)
PRINT ''
End
Fetch Next from db into @dbname
end
Close db
Deallocate db

Backup Status - SQL Server

Step I:

Create Table to see the status of particular database backup.

USE [dbmonitor]
GO

/****** Object: Table [dbo].[backupdb] Script Date: 06/18/2009 14:40:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[backupdb](
[name] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Step II:
Insert your required monitoring database name in to the above table
Step III:
Create procedure P_Backupstatus
as
Begin
Declare @servername varchar(60)
Set @servername=@@servername
--Print @servername
SELECT machine_name as [Machine Name],Database_Name as [Database Name],
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as [Last Backup],
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as [Days Since Last]
FROM MSDB.dbo.BackupSet
WHERE Type = 'd' and machine_name=@servername and database_name in (select name from dbmonitor.dbo.backupdb) --and (Database_Name='dbmonitor')
GROUP BY machine_name,Database_Name
ORDER BY 3 DESC
End

Step IV:

You can see the backup status.

Create Index Script or Execute Indexing Script Using Cursor

Objective: To create indexing script automatically and then apply Or Execute directly through this Procedure.
Step I:
First Create Table for inserting table name in to this table for our Indexing purpose.

USE [dbmonitor]
GO

/****** Object: Table [dbo].[T_TableReindexing] Script Date: 06/18/2009 14:12:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_TableReindexing](
[DatabaseName] [varchar](255) NULL,
[Owner] [varchar](255) NULL,
[TableName] [varchar](255) NULL,
[Status] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[T_TableReindexing] ADD DEFAULT ((1)) FOR [Status]
GO

Table Structure for storing Database, owner, Tablename, Status.

How to Insert Data into the above mentioned table
Step II:
Populate the table into [T_TableReindexing]
Insert into T_TableReindexing (DatabaseName,Owner,TableName)
Select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.TABLES Where table_type='Base Table'

Step III:
This is the procedure to have all scripts for Indexing. You can also execute directly by allowing execution in Exec (@cmd) in this procedure, which is already commented.
CREATE PROCEDURE P_ReIndexDatabaseV1
AS
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT [SDB].[name] FROM master.dbo.sysdatabases AS [SDB]
WHERE [SDB].[name] collate database_default IN (SELECT DISTINCT [RI].[DatabaseName] FROM T_TableReindexing AS [RI](nolock))
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES INNER JOIN T_TableReindexing AS [RI](nolock) ON table_catalog collate database_default = [RI].[DatabaseName] AND [TABLE_NAME] collate database_default =[RI].[TableName] AND [RI].[STATUS]=1'
EXEC (@cmd)
Print (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
-- SQL 2000 command
--DBCC DBREINDEX(@Table,' ',@fillfactor)
-- SQL 2005 command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', ONLINE = ON )' PRINT @cmd
--EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Delete Files Older than Some Days

Delete Files older than 5 days: Here in this we are deleting files older than 5 days with .bak extension from the directive D:\delete: Tested in Windows 2003 Server.

C:\Documents and Settings\Administrator>forfiles /P D:\DELETE\ /S /D -5 /M *.bak /C "cmd /C del @path /Q"

Before execution of the script, you may see file temp.bak older than 5 days. The script execution date is 18th June 2009.

After execution, the file temp.bak was deleted. But still the file backup.bak exists. Because the file backup.bak was created on the day of the execution. That 18th June 2009. Therefore, this demo proves that this script works for deleting files older than some days.

How to Add Two And OR Conditions in the SQL Query

How to Add Two And OR Conditions in the SQL Query:

Create View v_currentreport
as
select [Date], [Revenue]from V_DataCollection(nolock)
where
(
((DATEPART(hh, getdate()) < 1) AND convert(varchar, date, 103) = convert(varchar,getdate()-1,103)) -- This condition will get data of the previous day if the present time is between 00 AM (ie, 12 AM) and 01 AM
OR
((DATEPART(hh, getdate()) >= 1) AND convert(varchar, date, 103) = convert(varchar,getdate(),103)) -- -- This condition will get data of the same day if the present time is between 01 AM (ie, 01 AM) and 29:59:59 PM
)

Optimisation in Procedure using Union All Instead of Outer Join

Existing Procedure of Outer Join can also be rewritten in Union All. We have experienced optimisation in this way.
Original Procedure using Joins
CREATE Procedure [dbo].[RevenueReport_V2] (@month int, @year int)
as
Begin
select
isnull(isnull(x.AgentID,y.AgentID),z.AgentID) AgentID,
(select company from DatabaseName.dbo.T_AgentDetails(nolock) where AgentID=isnull(isnull(x.AgentID,y.AgentID),z.AgentID)) Company,
isnull(isnull(x.month,y.month),z.month) [Month],
isnull(isnull(x.year,y.year),z.year) [Year],
isnull(z.Clicks,0) TotalClicks,
isNull(x.RecordsCollected, 0)RecordsCollected,
isNull(y.Delivered,0)Delivered,
isNull(x.Rejected,0) Rejected,
isNull(y.Returned,0) Returned,
isNull(y.ReturnedCredit,0) ReturnedCredit,
isNull(y.PayToAgent,0) PayToAgent,
isNUll(y.Revenue,0) Revenue,
isNull(y.NetRevenue,0) NetRevenue

from

(
select
count(*) RecordsCollected,
AgentID,
Sum(Case when status > 3 then 1 else 0 end) Rejected,
@year [Year],
@month [Month],
(select ProductName from T_Products(nolock) where AgentID=T_ProductCollection.AgentID) ProductName
from T_ProductCollection (nolock)
where AgentID <> 1 and Year(date) = @year and Month(date) = @month
group by AgentID,month(date), year(date)

)x

full outer join

(
select
AgentID,
count(*) Delivered,
Sum(case When RecordPrice <> 0 then RecordPrice else 0 end ) Revenue,
Sum(case When RecordPrice > 0 then RecordPrice else 0 end ) + Sum( case When RecordPrice <> 0 then AgentPrice else 0 end ) PayToAgent,
@year [Year],
@month [Month],
(select ProductName from T_Products(nolock) where AgentID=t_clientleads.AgentID) ProductName
from t_clientleads (nolock)
where AgentID <> 1 and Month(srdate) = @month and Year(srdate) = @year
group by AgentID, month(srdate), year(srdate)
)y

on y.AgentID = x.AgentID
full outer join

(
select
Month(Date) [Month],
Year(Date) [Year],
count(*) Clicks,
AgentID,
(select ProductName from T_Products(nolock) where AgentID=TrackingDB.dbo.T_ProductTracking.AgentID) ProductName
from TrackingDB.DBO.T_ProductTracking(nolock)
where AgentID <> 1 and Year(date) = @year and Month(date) = @month
group by
AgentID,
Month(Date),
Year(Date) )Z on z.AgentID = y.AgentID
and z.AgentID = x.AgentID
End

Rewritten Using Union All:
create PROCEDURE [dbo].[RevenueReport_v3]
(@month int, @year int)
AS
BEGIN
With AgentReport (AgentID, [Month],[Year],[RecordsCollected],[Delivered],[Rejected],[Returned],[ReturnedCredit], [PayToAgent],[Revenue],[TotalClicks]) AS
(
SELECT
AgentID,
MONTH(date) as [Month],
YEAR(date) as [Year],
Count_BIG(*) As [RecordsCollected],
0 AS [Delivered],
sum(case when status>3 then 1 else 0 end) As Rejected,
0 AS [Returned],
0 AS [ReturnedCredit],
0 AS [PayToAgent],
0 AS [Revenue],
0 AS [TotalClicks]
from dbo.T_ProductCollection (nolock)
where AgentID<>1 and Month(date) = @month and Year(date) = @year
--datediff(dd, date,isnull(@StartDate,getdate())) <= 0 and datediff(dd, date, isnull(@EndDate,getdate())) >= 0
Group by AgentID,MONTH(date),Year(date)
UNION ALL
SELECT
AgentID,
MONTH(srdate) as [Month],
YEAR(srdate) as [Year],
0 As [RecordsCollected],
Count_BIG(*) [Delivered],
0 AS Rejected,
sum(case when RecordPrice <> 0 then AgentPrice else 0 end ) [PayToAgent],
sum(case when RecordPrice > 0 then RecordPrice else 0 end ) [Revenue],
0 AS [TotalClicks]
from DBO.T_ProductDelivery (nolock)
where AgentID<>1 and Month(srdate) = @month and Year(srdate) = @year
--datediff(dd, srdate,isnull(@StartDate,getdate())) <= 0 and datediff(dd, srdate, isnull(@EndDate,getdate())) >= 0
Group by AgentID,MONTH(srdate),Year(srdate)
UNION ALL
SELECT
AgentID,
MONTH(date) as [Month],
YEAR(date) as [Year],
0 AS [RecordsCollected],
0 AS [Delivered],
0 AS Rejected,
0 AS [Returned],
0 AS [ReturnedCredit],
0 AS [PayToAgent],
0 AS [Revenue],
count(*) [TotalClicks]
FROM [TrackingDB].[dbo].[T_ProductTracking](nolock)
where AgentID<>1 and Month(date) = @month and Year(date) = @year
--datediff(dd, date,isnull(@StartDate,getdate())) <= 0 and datediff(dd, date, isnull(@EndDate,getdate())) >= 0
Group by AgentID,MONTH(date),Year(date)
)

SELECT
[Rpt].[Month],
[Rpt].[Year],
[Rpt].[AgentID],
[Aff].[ProductName],
SUM([Rpt].[RecordsCollected]) AS [RecordsCollected],
SUM([RPT].[Delivered]) AS [Delivered],
SUM([RPT].[Rejected]) AS [Rejected],
SUM([RPT].[Returned]) AS [Returned],
SUM([RPT].[ReturnedCredit]) AS [ReturnedCredit],
SUM([RPT].[PayToAgent]) AS [PayToAgent],
SUM([RPT].[Revenue]) AS [Revenue],
(SUM([RPT].[Revenue])-SUM([RPT].[ReturnedCredit])) as NetRevenue,
SUM([RPT].[TotalClicks]) AS [TotalClicks]

FROM AgentReport AS [Rpt]
LEFT JOIN DatabaseName.dbo.T_AgentDetails As [Aff](nolock) ON [Aff].AgentID = [Rpt].[AgentID]
--LEFT JOIN T_Products As [Product](nolock) ON [Product].ProductID =[Rpt].ProductID
GROUP BY [Rpt].[AgentID],[Aff].[company],[Rpt].[Month],[Rpt].[Year]
End

Find Records in SQL SERVER Table and Do some Execution

Find Records in SQL SERVER Table and Do some Execution
Example I:
Try to find records or data in table with table column field condition, if exists, do something, like below:
IF (exists(select * from T_CurrentSummary(nolock) where revenue=0))
Begin
Execute DatabaseName.[dbo].[T_Reinitialise]
End
Example II:
Try to find records or data in table, if not, do something, like below:
If ( NOT EXISTS(Select top 1 * from T_CurrentSummary(nolock)))
Begin
Execute DatabaseName.[dbo].[T_Reinitialise]
WAITFOR DELAY '00:00:01'
Print 'Hi Reinitilaised'
End
Example III:
Try to find records or data in table, if not, do not do anything, like below:
select * from T_CurrentSummary(nolock)
If @@rowcount =0 Return -- The execution will return without doing further.

Delete Duplicate Records from SQL Server Table

In the below case, if you want to see and delete the today records of product id 2 from the table t_duplicaterecords. You have to follow the below query. Here srid is the unique in table. RecordID may contain duplicate. The purpose here for us is to remove the duplicate records based on duplicate RecordID.
We can keep the minimum srid and delete the maximum srid for the duplicate records.
use databasename
go
Select * from databasename.dbo.t_duplicaterecords where datediff(dd,srdate,getdate())=0 and productid=2 and srid not in ( select min(srid) from databasename.dbo.t_duplicaterecords(nolock) where datediff(dd,srdate,getdate())=0 and productid=2 group by RecordID )

Below is the Procedure to delete duplicate records based on productid, startdate and enddate of that product ID.
Create Procedure P_DeleteDuplicate_t_duplicaterecords (@Productid int, @StartDate Datetime,@EndDate Datetime) as
Begin
Declare @cnt int
set @cnt = 1
Declare @srid table (Srid int Unique not null)
while (1=1)
Begin
Insert into @srid Select Top 5000 srid from databasename.dbo.t_duplicaterecords (nolock) where datediff(dd, SoldDate,@StartDate) <= 0 and datediff(dd, SoldDate, @EndDate) >= 0 and Productid=@Productid and ourprice>=0 and srid not in (select min(srid) from t_duplicaterecords(nolock) where datediff(dd, SoldDate,@StartDate) <= 0 and datediff(dd, SoldDate, @EndDate) >= 0 and Productid=@Productid and ourprice>=0
group by RecordID )
If @@rowcount =0 Return
Begin Try
Begin Transaction T1
insert into clashbackup.dbo.t_duplicaterecordsbackup (SRID,SoldDate,ClientID,Productid,BatchID,ourprice,RecordID,RecordSellerID,RecordSellerPrice,Email)
Select SRID,SoldDate,ClientID,Productid,BatchID,ourprice,RecordID,RecordSellerID,RecordSellerPrice,Email from databasename.dbo.t_duplicaterecords(nolock) where srid in (Select srid from @srid)
Delete from databasename.dbo.t_duplicaterecords where srid in (Select srid from @srid)
Commit Transaction T1
Delete from @srid
End Try
Begin Catch
If @@trancount>0
RollBack Transaction
End Catch
set @cnt = @cnt + 1
print @cnt
--WAITFOR DELAY '00:00:01'
End
End
If you want to remove whole table duplicates based on two column unique:
select srid,* from databasename.dbo.T_Duplicaterecords(nolock) where srid not in (select max(leadid) from databasename.dbo.T_Duplicaterecords(nolock) group by email,productid)
Once verification correct using above query, you can delete
delete from databasename.dbo.T_Duplicaterecords(nolock) where srid not in (select max(leadid) from databasename.dbo.T_Duplicaterecords(nolock) group by email,productid)
To see how many records duplicate:
select max(id),count(*) as [Counting] from T_Duplicaterecords(nolock)
group by productid,email
Having count(*)>1
To update duplicate records status in table:
UPDATE dbo.T_DuplicateRecords
SET RecordStatus = @newStatusBad FROM dbo.T_DuplicateRecords T1
,(
SELECT MIN(srid) AS Minsrid
,ProductID
,emailShort
,telephone FROM dbo.T_DuplicateRecords
WHERE RecordStatus IS NULL -- only records that still have status unknown
AND telephone IS NOT NULL -- exclude records with telephone NULL
GROUP BY ProductID ,emailShort ,telephone HAVING COUNT(*) > 1 ) derived
WHERE T1.srid > derived.Minsrid AND T1.RecordStatus IS NULL -- only records that still have status unknown
AND T1.telephone IS NOT NULL -- exclude records with telephone NULL
AND T1.ProductID = derived.ProductID AND T1.emailShort = derived.emailShort AND T1.telephone = derived.telephone;

Delete the duplicate records which is having some status. Take the min id and max id from the table and delete the records which is having status = 0. In this case status=0 must be deleted from the duplicate records.
SELECT MIN(ID), MAX(ID),Prodcutid,FilterID, COUNT(Prodcutid) FROM dbo.T_DuplicateRecords(nolock) GROUP BY Prodcutid, FilterID HAVING COUNT(Prodcutid) > 1 ORDER BY Prodcutid, FilterID, COUNT(Prodcutid) )
Take min(id) and max(id) from above query and delete the records where status=0
DELETE dbo.T_DuplicateRecords WHERE ID IN ( 171,417,241,734,170 ,4141,4142,4143,4144 ,4117,4118,4119,4120 ,4111,4112,4113,4114 ,4438,4439,4440,4441 ,4444,4235,4236,4237 ,4238,4494,4495,4496 ,4497,4536,4537,4538 ,4539,4543,4544,4545 ,4546,4557,4558,4559 ,4560,4563 ) AND STATUS=0

Delete Files using Command Prompt

Delete Files from Specific Folder in Command Prompt:
Delete files in command prompt
erase /Q d:\mails\drop\*.*

Time Out in ASP.Net Application

Time Out Solution if the application timeout within your preferred time limit:
Connection Timeout:
Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer;Connect Timeout=30";
Command Timeout :
If you give the value as 0, that is (e.Command.CommandTimeout = 0) the application timeout is infinitive
Eg.
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
e.Command.CommandTimeout = 60
End Sub
Eg:
Protected Sub GetData(ByVal queryName As String)
Dim myconn As New SqlConnection(ConfigurationManager.ConnectionStrings("myconn").ConnectionString)Dim mycommand As New SqlCommand("r_GetCustomQueries", myconn)mycommand.CommandType = CommandType.StoredProcedure
myconn.Open()
mycommand.commandtimeout=600