begin/end begin tran/commit in while

begin/end begin tran/commit in while

Post by Kermit Lowry, II » Sat, 29 Apr 2000 04:00:00



 Initially I had the following code:

set rowcount 1000
go
select count(*) from table
go

begin
insert PAT_APPT_HIST
select * from ncdw..patahist98

delete ncdw..patahist98
end
go

which worked fine, but I decided to make the insert and delete one
transaction, producing the following code:

while 1 = 1
begin
begin tran
insert PAT_APPT_HIST
select * from ncdw..patahist98

delete ncdw..patahist98
commit tran
end
go

If you'll notice, I had to change the while to an infinite loop because
after putting in the begin/commit tran lines the while loop would only
go through once even though the ( # rows affected ) still showed 10000.

Any clue would be appreciated.

-- Kermit Lowry, III
----------------
"Only you can prevent forest fires!" -Smoky
at kp.org use kermit.lowry

This input does not necessarily reflect the opinion of my employer.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

begin/end begin tran/commit in while

Post by Bret Halfor » Sat, 29 Apr 2000 04:00:00



>  Initially I had the following code:

> set rowcount 1000
> go
> select count(*) from table
> go

> begin
> insert PAT_APPT_HIST
> select * from ncdw..patahist98

> delete ncdw..patahist98
> end
> go

> which worked fine, but I decided to make the insert and delete one
> transaction, producing the following code:

> while 1 = 1
> begin
> begin tran
> insert PAT_APPT_HIST
> select * from ncdw..patahist98

> delete ncdw..patahist98
> commit tran
> end
> go

> If you'll notice, I had to change the while to an infinite loop because
> after putting in the begin/commit tran lines the while loop would only
> go through once even though the ( # rows affected ) still showed 10000.

> Any clue would be appreciated.

 Kermit,

The problem is that the "commit tran" command does not directly affect

You could write it this way:




begin
begin tran
insert PAT_APPT_HIST
select * from ncdw..patahist98

delete ncdw..patahist98

commit tran
end
go

--
Bret Halford
Sybase Technical Support
3665 Discovery Drive
Boulder, CO 80303

 
 
 

1. Help: simultaneous calls of a SP containing BEGIN TRAN....COMMIT TRAN

Hi,

I have a stored procedure sp_A that contains BEGIN TRAN....COMMIT TRAN.

What I understand is that the tables inside the BEGIN TRAN....COMMIT TRAN
block will be locked completely during the execution of sp_A (my
understanding could be wrong).
That means no other process can write to those tables used in this block
until sp_A
finishes.

In such case, if there are multiple processes call sp_A during
simultaneously, what
will happen? Since sp_A can only run for one process at a time, will the
others calls
be put in a queue in SQL Server or get a error message?

Plus, will the following process lock a single row or the whole table?
   UPDATE Book
   SET bookName='SQL Server Programming'
   WHERE bookID=123
And, will it be rollback automatically if it fails?

Thank you

hb

2. Catastrophic Failure

3. Begin Tran-Commit Tran

4. HELP!!! Delphi 2.0 OLE DLL registration for use with VB object browser

5. BEGIN TRAN and COMMIT TRAN from a three tier system

6. WI/IL SR Database Administrator+Sybase+SQL+Unix

7. begin tran / commit tran

8. SQL*NET

9. begin tran and commit tran

10. Begin, Commit tran - tran count not matched

11. Help: multiple calls and BEGIN TRAN....COMMIT TRAN

12. Do I need BEGIN/COMMIT TRAN here?

13. Programmatic BEGIN TRAN, ROLLBACK and COMMIT