Pbm with transaction

Hi,
I got a problem while testing the BEGIN TRAN.. COMMIT TRAN. I have created
3 tables and 3 sps for inserting values into the table.
Now I ran a query to call the 3 sps inside a Begi.. commit tranaction which
is given below (of course there is WAITFOR DELAY statement in between the
first to sp calls), but before executing completely i disaqbled the network.
When i reconnect it and checked the values in the tables, i found the
entries. i wonder how this happened before committing the operation. Can
anyone help me. The code is given below
regards Lara
CREATE TABLE [TableOne] (
[a] [int] IDENTITY (1, 1) NOT NULL ,
[atext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [TableThree] (
[c] [int] IDENTITY (1, 1) NOT NULL ,
[ctext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [TableTwo] (
[b] [int] IDENTITY (1, 1) NOT NULL ,
[btext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE InsertA
@.x varchar(32)
as
insert into TableOne select @.x
GO
CREATE PROCEDURE InsertB
@.x varchar(32)
as
BEGIN
--WAITFOR DELAY '000:00:59'
INSERT INTO TableTwo select @.x
END
GO
CREATE PROCEDURE InsertC
@.x varchar(32)
AS
INSERT INTO TableThree select @.x
GO
BEGIN TRAN
EXEC insertA 'A'
WAITFOR DELAY '000:00:59'
EXEC insertB 'B'
EXEC insertC 'C'
COMMIT TRANI assume you found data in all three tables? In this case, the entire batch
was sent to SQL Server where it executed to completion event though the
client was unplugged from the network. It's just that SQL Server couldn't
notify the client after the transaction completed successfully.
Hope this helps.
Dan Guzman
SQL Server MVP
"Lara" <aneeshattingal@.hotpop.com> wrote in message
news:OEZYoCmQFHA.2384@.tk2msftngp13.phx.gbl...
> Hi,
> I got a problem while testing the BEGIN TRAN.. COMMIT TRAN. I have
> created
> 3 tables and 3 sps for inserting values into the table.
> Now I ran a query to call the 3 sps inside a Begi.. commit tranaction
> which
> is given below (of course there is WAITFOR DELAY statement in between the
> first to sp calls), but before executing completely i disaqbled the
> network.
> When i reconnect it and checked the values in the tables, i found the
> entries. i wonder how this happened before committing the operation. Can
> anyone help me. The code is given below
> regards Lara
>
> CREATE TABLE [TableOne] (
> [a] [int] IDENTITY (1, 1) NOT NULL ,
> [atext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TableThree] (
> [c] [int] IDENTITY (1, 1) NOT NULL ,
> [ctext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TableTwo] (
> [b] [int] IDENTITY (1, 1) NOT NULL ,
> [btext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE InsertA
> @.x varchar(32)
> as
> insert into TableOne select @.x
> GO
> CREATE PROCEDURE InsertB
> @.x varchar(32)
> as
> BEGIN
> --WAITFOR DELAY '000:00:59'
> INSERT INTO TableTwo select @.x
> END
> GO
> CREATE PROCEDURE InsertC
> @.x varchar(32)
> AS
> INSERT INTO TableThree select @.x
> GO
> BEGIN TRAN
> EXEC insertA 'A'
> WAITFOR DELAY '000:00:59'
> EXEC insertB 'B'
> EXEC insertC 'C'
> COMMIT TRAN
>