question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked MelissaMa-msft commented

System IP is being Blocked by sql server

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' )


sql-server-generalsql-server-transact-sql
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm afraid that this question is not making much sense to me. You apparently have a column called IPaddress and a variable by that name too. For SQL Server those are just names. They could as well be called Sara and Jean. So I don't understand your talk about SQL Server blocking system IP-addresses?

Can you clarify?

0 Votes 0 ·

it is happening sir,
Please try yourself ,to insert upto 300 rows then it will not allow to insert new row

0 Votes 0 ·

I think I have successfully inserted a lot more than 300 rows today.

If you have a problem, please post a repro that demonstrates the problem. We can't guess blindly what you are doing.

0 Votes 0 ·
Show more comments
pituach avatar image pituach akhterhussain-3167 ·

Good day @akhterhussain-3167

You question is not clear to me as well

Can you please provide queries to create the table and insert the sample data which lead to your issue?

We need to reproduce the issue in order to understand it and solve it

Thanks

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @akhterhussain-3167,

and IPAddress = @IPAddress and EntryDate=@EntryDate and Codeitem=@Codeitem

Actually above part do not affect the result since they are fixed values every day.

It could be the same with below:

 declare @i int =1
 while @i<=310
 begin
            
 declare @ETime  time(7)=cast(GETDATE() as time(7))
 declare @IPAddress [varchar](50)='192.168.10.111'
 declare @EntryDate date=cast(getdate() as date)
 declare @Codeitem int=141
            
 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(141,1,15,1,'2021-06-18',1,1500,1,'al',@ETime,'As_01','192.168.10.111',100 )
 --set @OID=SCOPE_IDENTITY()
             
 set @i=@i+1
 end
 end

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @akhterhussain-3167,

I tried with above code in my environment and executed it for more than 20 minutes.

It could insert 310 rows successfully.

You could continue with the next steps with ASP.net part and find out any solution.

Thank you for understanding!

Best regards,
Melissa

1 Vote 1 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered akhterhussain-3167 commented

Hi @akhterhussain-3167,

Could you please provide the code how you executed your procedure?

Did the value of @IPAddress be always changing or be the same value?

Please also provide the DDL and sample data of your Probale table.

It could be better for us to reproduce your issue and help you further.

According to your limited information, you could have a try to check below things:

  • Check whether there is any restriction about the range of your IP address used or repeat times of same IP address?

  • Check whether there is any error message about IP address or enough space after many inserts in the error log.

  • Check the Windows Firewall setting.

  • Check whether there is any blocking/deadlock/trigger on the Probale table.

Besides, you could refer Restrict an SQL Server connection to a specific IP address and check whether it is a little helpful to you.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MelissaMa-msft

@IPAddress will not change ,
there is not any kind of restriction of IP in my network,I just added one check in procedure for inserting data interval into table of ETIME

 CONVERT(DATETIME,ETIME) >= CONVERT(DATETIME,DATEADD(SECOND,-4,@ETime)) 

then it is not allowing data into database to be inserted after 300 row inserted into table.

If i removed below check in Procedure of

   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
      )

then Data is getting insert into database.


0 Votes 0 ·

Hi @akhterhussain-3167,

Thanks for your update.

Could you please also provide the exec procedure code like below:

 exec [dbo].[spInsertPorderder]  141,1,15,.....

Or how did you set the value of @ETime while executing your procedure?

Best regards,
Melissa



0 Votes 0 ·

Hi @MelissaMa-msft

i am using asp.net web form C# for executing Procedure ,below is button behind code .


Which is attached
106882-button-code.txt


0 Votes 0 ·
button-code.txt (3.6 KiB)
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @akhterhussain-3167,

I checked with my colleague from ASP.net team and he mentioned that the code was working with no error.

I also tried to make a test in my own environment with below code and it worked after inserting 310 rows.

 declare @i int =1
 while @i<=310
 begin
    
 declare @ETime  time(7)=cast(GETDATE() as time(7))
    
 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(141,1,15,1,'2021-06-18',1,1500,1,'al',@ETime,'As_01','192.168.10.111',100 )
 --set @OID=SCOPE_IDENTITY()
     
 set @i=@i+1
 end
 end

Then it could be somewhere else who caused this issue.

You could have a try to print CONVERT(DATETIME,ETIME) and CONVERT(DATETIME,DATEADD(SECOND,-4,@ETime)) to the log and check them later in the log which you might find the difference.

You could also try to enhance the code below in another way but it might be not much helpful:

 CONVERT(time(7),ETIME) >= CONVERT(time(7),DATEADD(SECOND,-4,@ETime))
    
  DATEDIFF(SECOND,CONVERT(time(7),ETIME),CONVERT(time(7),@ETime))>=4

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MelissaMa-msft

Thanks for Reply or humble support, could you ask from asp.net team that ,why does it happen of Duplicate entry on one Button Click ,what could be the issue ,i am totally frustrated to handle this duplicate entry ,which happened some time .

0 Votes 0 ·

Hi @akhterhussain-3167,

Duplicate entry

Did you mean that click button once, SP executes twice?

Best regards,
Melissa
0 Votes 0 ·

Yes Exactly,this happening ,i also used if else condition ,but it bypass and execute SP .but it happened some time

0 Votes 0 ·
Show more comments

Hi @MelissaMa-msft ,

IP address is committed in last your provided query ,please check below query again with IP Address.

  declare @i int =1
  while @i<=310
  begin
        
  declare @ETime  time(7)=cast(GETDATE() as time(7))
        
  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(141,1,15,1,'2021-06-18',1,1500,1,'al',@ETime,'As_01','192.168.10.111',100 )
  --set @OID=SCOPE_IDENTITY()
         
  set @i=@i+1
  end
  end


0 Votes 0 ·