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
>

Password Audit

Hello All,
I am in the process of auditing a SQL server and was checking for NULL
passwords. I ran the following query select name, password from
master..sysxlogins where password is NULL.
It returned the expected windows accounts with NULL passwords, but it also
returned two unknown accounts with NULL for the SID, Name, and Password, and
a status of 192.
Has anyone ever seen this before, and if so, what they might be?
Thanks
DanHi,
Have you tried with C2 Audit ? Password Audit from www.sqlsecurity.com ?
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com