I want to know that sql can block System IP address ,if user is continue inserting data into database .today i observed this thing, then i changed IP address of System,then data from system getting start to insert into DB.
below is procedure which i am using for inserting.
ALTER PROCEDURE [dbo].[spInsertPorderder]
-- Add the parameters for the stored procedure here
@Codeitem int,
@OrderNo int,
@prdqty int,
@IDWoker int,
@EntryDate date,
@FID int,
--@PBsize varchar(50),
@Weight int,
@SecID int,
@Rmk varchar (50),
@ETime time(7),
@BrefNo varchar (50),
@IPAddress [varchar](50) ,
@Gweigth int,
@OID int output
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(
SELECT ETIME, IPAddress,EntryDate FROM Probale
WHERE CONVERT(DATETIME,ETIME) >= CONVERT(DATETIME,DATEADD(SECOND,-4,@ETime)) and IPAddress = @IPAddress and EntryDate=@EntryDate and Codeitem=@Codeitem
)
Begin
insert into Probale(Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(@Codeitem,@OrderNo,@prdqty,@IDWoker,@EntryDate,@FID,@Weight,@SecID,@Rmk,@ETime,@BrefNo,@IPAddress,@Gweigth )
set @OID=SCOPE_IDENTITY()
end
end
DDL
here data ,but if you added more then 300 rows ,then that issue will occur and use above provide Procedure for inserting .
In Data Just Prdno and ETime will change ,ETIME gap of 4 Second .
CREATE table #Probale (
Prdno int ,
OrderNo int ,
Codeitem int ,
prdqty int ,
EntryDate date ,
Weigth int ,
IDwokrer int ,
FID int ,
SecID int ,
Rmk varchar(50) ,
ETime time(7) ,
BrefNo varchar(50) ,
IPAddress varchar(50) ,
Gweigth int
)
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10001,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:09','As_01','192.168.10.111','100' )
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10002,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:15','As_01','192.168.10.111','100' )
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10003,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:21','As_01','192.168.10.111','100' )
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10004,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:25','As_01','192.168.10.111','100' )
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10005,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:29','As_01','192.168.10.111','100' )
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10006,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:34','As_01','192.168.10.111','100' )
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10007,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:42','As_01','192.168.10.111','100' )
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10010,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:49','As_01','192.168.10.111','100' )
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10011,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:54','As_01','192.168.10.111','100' )
insert into #Probale(Prdno,Codeitem,OrderNo,prdqty,IDWokrer,EntryDate,FID,Weigth,SecID,Rmk,ETime,BrefNo,IPAddress,Gweigth )
values(10012,141,1,15,1,'2021-06-18',1,1500,1,'al','08:08:59','As_01','192.168.10.111','100' )