Problem updating records while SP is running.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem updating records while SP is running.

Author

Topic

mumairkhan
Starting Member

1 Post

Posted - 2011-01-11 : 06:35:19

a stored procedure which is executed every second by an SQL Agent's Job

declare @DataReceived bit
declare @JobCompleted bit

select top 1 @JobCompleted= JobCompleted, @DataReceived= Datareceived from tbl_JobFlag

-- never go inside if check unless this SP has completed its previous execution
if (@JobCompleted = 'true' and @DataReceived = 'true')
begin
update tbl_JobFlag set JobCompleted = 'false', DataReceived = 'false'

-- here procedure performs some heavy routines that takes 5 seconds to 30 minutes, depends on amount of data received

end

--when these fields are set JobCompleted = 'false', DataReceived = 'false' the values are never updated unless the SP finishes its execution. how to update these fields with in SP execution ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 10:37:09

sorry not quite clear. you're telling false values are not reflecting in table unless sp finishes execution? are you using explicit transaction inside?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources