table is locking up.... - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

table is locking up....

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-05-28 : 00:33:53

I am using this but it's locking up clocing_status table. What is wrong? or what can I include or modify to preven this happening? Again thanks for looking into this and help.

begin transaction;
if exists
(
select cs.emp_id from clocking_status cs
where not exists
(
select cd.emp_id
from dbo.CLOCKING_DETAILS cd
where cs.emp_id=cd.emp_id
)
)
begin
insert into clocking_status
(emp_id,clocking_date,clocking_status)
select e.emp_id,'','' from employees e
where (e.active='1') and
(not exists (select cs.emp_id from clocking_status cs where e.emp_id=cs.emp_id)
)
end
begin
update clocking_status
set clocking_status=
(
SELECT min(CASE WHEN (cd.time_out IS NULL) THEN 'in' ELSE 'out' END) AS cStatus
FROM dbo.CLOCKING_DETAILS AS cd
where (cd.emp_id=clocking_status.emp_id)
GROUP BY cd.emp_id
),
clocking_date =
(
SELECT max(cd.clocking_date)
FROM dbo.CLOCKING_DETAILS AS cd
where (cd.emp_id=clocking_status.emp_id)
GROUP BY cd.emp_id
)
END
SELECT TOP (100) PERCENT e.emp_id, e.first_name AS [First Name], e.last_name AS [Last Name], dbo.CLOCKING_STATUS.clocking_status
FROM dbo.EMPLOYEES AS e INNER JOIN
dbo.CLOCKING_STATUS ON e.emp_id = dbo.CLOCKING_STATUS.emp_id
WHERE (e.active = '1')
ORDER BY e.emp_id

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-28 : 07:47:28

There is no COMMIT / ROLLBACK

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.