Getting different results when using transactions

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 2000 Forums

SQL Server Development (2000)

Getting different results when using transactions

Author

Topic

vaidyanathanpc
Starting Member

24 Posts

Posted - 2002-03-15 : 03:33:54

Hi,
I need some help in using transactions. I'm running a transaction in ASP as shown in the code below which inserts data into two tables.

Dim conSQL
Dim conSQL1
Dim strSQL

Set conSQL = Server.CreateObject ("ADODB.Connection")
conSQL.Open Application("AppConnectString")

conSQL.BeginTrans

strSQL = "INSERT INTO " &_
" TAB1 " &_
"VALUES " &_
" (1,'TAB1')"
conSQL.Execute strSQL

strSQL = "INSERT INTO " &_
" TAB2 " &_
"VALUES " &_
" (1,1,'Tab2')"

conSQL.Execute strSQL

conSQL.CommitTrans

conSQL.Close

I'm doing an insert into TAB1 and TAB2 using transactions but the values provided are such that the second insert statement fails but the first one succeeds. i.e The insert into TAB1 does not give an error but insert into TAB2 gives an error and the entire transaction rolls back and no rows are inserted into TAB1 and TAB2. i.e when I do a select * from TAB1 and TAB2, no rows are retrieved. This is when I try to insert rows through ASP.

Now I'm executing the same transaction through SQl Server Query analyzer as follows.

BEGIN TRANSACTION @TranName

INSERT INTO TAB1 VALUES (1,'TAB1')
INSERT INTO TAB2 VALUES (1,1,'TAB2')

COMMIT TRANSACTION @TranName

Here too the values provided are such that the second insert statement fails but the first one succeeds. i.e The insert into TAB1 does not give an error but insert into TAB2 gives an error. But when the transaction is performed, the first insert commits data i.e data is inserted into TAB1 but no data is inserted into TAB2. i.e when I do a select * from TAB1 and TAB2, TAB1 retrieves one row but TAB2 retrieves zero rows.
How can this be possible? I am doing the same transaction using ASP and through SQL Server Query analyzer but the results I'm getting are different.

Thanks in advance
P.C. Vaidyanathan

Teroman
Posting Yak Master

115 Posts

Posted - 2002-03-15 : 06:31:07

I think that the difference is that ASP connection objects do an implicit rollback if an error occours on them, while normal SQL require you to write the error handling, so try

begin transaction

insert into TAB1 values(1, 'TAB1')
if @@error <> 0
begin
rollback transaction
return
end

insert into TAB2 values(1, 1, 'TAB2')
if @@error <> 0
begin
rollback transaction
return
end

commit transaction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources