question

kpavanmst avatar image
kpavanmst asked

SQL Server Stored Procedure Causing timeout with WAIT FOR

Hello, We have a requirement to check the status of the application, this application receives the input file at any time and starts process them. We have two tables called, BatchMaster, BatchDetails The actual process starts by inserting a new record in BatchMaster subsequently the detail records are inserted in BatchDetials table. These two tables are referenced by BatchID. When the application starts the process the Status column for all details in BatchDetails table are set to 0 When finished it's updated to 1. This means when there is no issue, the Status value for all details associated with BatchId are updated from 0 to 1. This whole process will be finished in 1 minute. Now we need to generate an alert when the application interrupted due to some issues happened in the background (may be db connection or network issue), in other words if any one of the Status value is not update from 0 to 1 after one minute, we need to generate an alert to support team. I have created a SQL server trigger to meet this, a trigger will call the stored procedure when any record inserted in BatchMaster table. The stored procedure will actually check for the status and alerts if required. Before it check for the actual status, I used WAIT FOR '00:00:60'command as I know application will take 1 minute time to finish it's job. Unfortunately, I am getting time out issue from application if I use WAIT FOR '00:00:60' in my stored procedure. If I remove this command, application is working as usual. Please advise on how to wait for a certain period of time pairing with trigger before checking for the Status. Fyi, I copied my code here. I have the below trigger, CREATE TRIGGER [dbo].[tr_BatchStatus] ON [dbo].[BatchMaster] FOR INSERT AS exec CheckBatchStatus The stored procedure is as below. CREATE PROC [dbo].[CheckBatchStatus] AS BEGIN DECLARE @r_status BIT DECLARE @BatchStatus_table TABLE ( id smallint Primary Key IDENTITY(1,1) ,rc_STATUS BIT ) DECLARE @i INT DECLARE @numrows INT ----wait for 50 seconds WAITFOR DELAY '00:00:50' -- populate BatchStatus table INSERT @BatchStatus_table SELECT dbo.BatchDetail.Status FROM BatchDetail where BatchId in (SELECT TOP 1 Id FROM BatchMaster) SET @i = 1 SET @numrows = (SELECT COUNT(*) FROM @BatchStatus_table) IF @numrows > 0 -- enumerate the table WHILE (@i <= (SELECT MAX(id) FROM @BatchStatus_table)) BEGIN -- get the next record status SET @r_status = (SELECT rc_STATUS FROM @BatchStatus_table WHERE id = @i) if @r_status <> 1 begin exec master..xp_cmdshell 'echo OUTPUT FILE NOT GENERATED FOR THE RECENT INPUT >> C:\ BatchStatus.txt' BREAK end else SET @i = @i + 1 END END Thanks, KP.
sqlserver2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Don't do it like this. You have added the 60s wait synchronously into the transaction that inserts the row into BatchMaster - it's no wonder that you are getting timeouts. It sounds like you don't have control over the process to know when it's failing, hence the reason to arbitrarily wait 60s and then query a table. Might be better to have a separate process (maybe Agent Job?) that checks the BatchMaster for incomplete runs that started over 60s ago? Alternatively, change the process to give you better control?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.