How do I update new record before it is committed? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How do I update new record before it is committed?

Author

Topic

gouri
Starting Member

7 Posts

Posted - 2008-06-09 : 01:08:10

I have a queue in service broker, which gets messages with a stored procedure which is invoked from VS application. The messages are properly stored in queue. I want to receive these messages one after the other and put it into a table. I am fetching the messages in a while loop which breaks when messages are over.

The first record is inserted properly. In the next iteration of the loop I want to append the next message data in that same record. This will continue for approximately 10 minutes after which a new record will be created. When I attempt to append the data in first record that record is not being read even though I have used isolation level read uncommitted.

What is it that I am missing to achieve the desired results?

Gouri Sohoni

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-09 : 02:47:55

Please post the code by which you are trying to achieve the desired result.

gouri
Starting Member

7 Posts

Posted - 2008-06-09 : 06:13:44

Adding the code
--There is a queue named ReceiverQueue
--table structure is bedno char(10),pid char(10),
--starttime datetime, hrtype char(10), v text

while (1=1)
BEGIN
DECLARE @message XML
DECLARE @PKey nchar(10)
DECLARE @BKey nchar(10)
DECLARE @StartTime datetime
DECLARE @HKey nchar(10)
DECLARE @vKey nvarchar(12)
begin transaction;
waitfor (RECEIVE TOP(1) @message=CONVERT(xml, message_body) FROM ReceiverQueue),
timeout 100;
if (@@ROWCOUNT =0)
begin
commit
break
end
select @message

--separate all tags from data
SET @PKey= @message.value ('(v/@PID)[1]','nchar(10)')
SET @BKey=@message.value('(v/@BedID)[1]','nchar(10)')
SET @StartTime=GETDATE()
SET @HKey =@message.value('(v/@HR)[1]','nchar(10)')
SET @vKey=@message.value('(/v)[1]','nvarchar(12)')
SELECT @PKey,@BKey ,@StartTime,@HKey,@vKey
if ((SELECT COUNT(*) FROM dbo.Table1 WHERE pid=@PKey and bedno=@BKey)=0 )
BEGIN
set transaction isolation level read uncommitted

BEgin transaction t1
-- add new record
INSERT INTO dbo.Table1 (bedno,pid,starttime,hrtype,v)
VALUES (@BKey,@PKey,@StartTime,@HKey,@vKey)
commit transaction t1

END
ELSE
-- find out if current time and previous time have a difference of 10 min
--upto 10 min data should go to same record
--patient id same, bed name same and difference upto 10 min
if (SELECT COUNT(*) FROM dbo.Table1 WHERE
(DATEDIFF(mi,starttime,@StartTime) <= 10 AND DATEDIFF(mi,starttime,@StartTime) > 0 AND pid=@PKey
and bedno=@BKey))=1
BEGIN
set transaction isolation level read uncommitted

begin transaction t2
--now add v tag
declare @l int
select @l=datalength(Table1.v) from table1 where
(DATEDIFF(mi,starttime,@StartTime) <= 10 AND DATEDIFF(mi,starttime,@StartTime) > 0 AND pid=@PKey
and bedno=@BKey)

DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(v)
FROM dbo.Table1
WHERE
(DATEDIFF(mi,starttime,@StartTime) <= 10 AND DATEDIFF(mi,starttime,@StartTime) > 0 AND pid=@PKey
and bedno=@BKey)

UPDATETEXT dbo.Table1.v @ptr @l 0 @vKey
commit transaction t2

END
ELSE IF
-- in else the new record need to be added
(SELECT COUNT(*) FROM dbo.Table1 WHERE
(DATEDIFF(mi,starttime,@StartTime) > 10 AND pid=@PKey and bedno=@BKey))>1
BEGIN
set transaction isolation level read uncommitted

begin transaction t3

INSERT INTO dbo.Table1 (bedno,pid,starttime,hrtype,v)
VALUES (@BKey,@PKey,@StartTime,@HKey,@vKey)
commit transaction t3

END
END

thanks

gouri
Starting Member

7 Posts

Posted - 2008-06-10 : 01:28:14

Anyone who could help me out with this..Please.

Thanks

gouri
Starting Member

7 Posts

Posted - 2008-06-11 : 01:13:24

can someone help me out please

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources