Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQl Server 2000 - Transaction within SP not commiting

Status
Not open for further replies.
NaoTriste

NaoTriste

Programmer
Dec 17, 2002
29
GB
Sql Server 2000 and Transactions - Must be male as it won't commit.
I have been using Sql Server for God knows how long and have this problem with transactions which has got me miffed !
Nothing extradordinary in my Sql.
I have a SP which contains various Insert & Update commands within a Transaction block and I am checking for errors after every statement. I know the Insert/Update code runs ok as when I take the transaction commands out it runs ok. When using the transaction block all the code executes and then it hangs and hangs and hangs waiting to be commited. Why does it not commit the transactions? Am I so hanged up on the transactions that I am missing an obvious error with the code flow ?

The code is , generically as follows :
Code: 
alter proc uspTEST as
declare @rc int, @LE_ErrCode int, @LE_ErrMsg nvarchar(4000)

[red][b]begin tran A[/b][/red]

=========================================================
insert into ........
            select ......from....
Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = '...error message....'
        	goto Err
        End
======================================================
 Update ...
	 where ...
Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = '...Error message...'
        	goto Err
        End

.
. More /InsertUpdate statements
.

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = '...Error message...'      	goto Err
        End

[red][b]commit tran [/b][/red]
return @rc
Err:
    
    [red][b]if @@trancount > 0 rollback tran[/b][/red]
    exec uspErrorProc
    return @rc






 
Sort by date Sort by votes
erm, shouldn't you be putting "commit tran a" and "rollback tran a"?

and what's the point of doing if @@trancount > 0?
why not just:

if @rc <> 0
rollback tran a
else
commit tran a

--------------------
Procrastinate Now!
 
Upvote 0 Downvote
I have actually name all the transactions commands but it is irrelevant as you don't need to use the name at all and I only use it for code readibility.
The code will only go to the Err section when @rc <> 0 therefore I know I want to rollback and that is why I do not use an If statement.
 
Upvote 0 Downvote
I think you missed his point. Once you have named the transaction Tran A then you must commit Tran A not Tran.

"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
...and I think you may have missed mine. In the real code I have given it a proper name and used it always, both on Begin, Commit & Rollback. Forget that I put Tran A in my example. Apologies if it was misleading .
 
Upvote 0 Downvote
>>I think you missed his point. Once you have named the transaction Tran A then you must commit Tran A not Tran.

that is not true, run the following code

Code: 
begin tran a
select getdate()
select @@trancount
commit
select @@trancount

Code: 
begin tran a
select getdate()
select @@trancount
commit work
select @@trancount

Code: 
begin tran a
select getdate()
select @@trancount
commit tran
select @@trancount

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Upvote 0 Downvote
Yes, basically Sql server ignores the transaction name on the commit statement,merely decrementing the Trancount by 1. In my case as I only have one Begin tran it will decrease the trancount to 0 and then commit (or not as it is my case !)

I must be missing something for sure but not in this section. the folowing code is within a label (Err) and I do want all the statements within it to be executed.
Code: 
Err
if @@trancount > 0 rollback tran
    exec uspErrorProc
    return @rc



 
Upvote 0 Downvote
Yes, I think it is best, really doing my head in and about to go live with it. Can see that instead of scheduling it in a job I am going to have to be the human scheduler and run the store proc manually every day !
It's kind of big though...

Code: 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



ALTER    proc uspImportSAPCodesTEST as
declare @rc int, @LE_ErrCode int, @LE_ErrMsg nvarchar(4000)

begin tran ImportSapCodes


--truncate table tblTreeviewTEST
--Compare the incoming codes with the Codes already existing in the 
--system and do one of the following:
--1.Insert SAP codes which exist on the Import File but Not on this system
--2.Flag as active thoses codes which are flagged as deleted but are now in the Import file
--3.Flag as Deleted those codes which are no longer on the Import file but are in the system as Active

/*
	  ---------------------------------------------
	  |  SAPIMPORT | TREEVIEW | FLAG | ACTION     |
	  =============================================
	  |            |          |      |            |
Scenario 1|     Yes    |     No   |  n/a |   Insert   |
	  =============================================  
	  |     Yes    |     Yes  |   0  |   No action| 
Scenario 2|     Yes    |     Yes  |   1  |    0       |
	  =============================================   
	  |      No    |     Yes  |   0  |    1       |
Scenario 3|      No    |     Yes  |   1  |   No action|                            
	  ---------------------------------------------
*/

/*
=============================================================================
				Scenario1
=============================================================================
 level 1
=============================================================================
*/

insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
	select distinct 0, SAP.Customer
	from tblSAPCodesImport as SAP
	left join vTreeviewTEST on SAP.Customer = L1Item
        where L1Item is null 
Set @rc = @@error
print @rc
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Customer'
        	goto Err
        End
print 'End of Scenario1  Level1'

/*
=============================================================================
 level 2
=============================================================================
*/

insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
	select distinct TV1.TV_Id, SAP.[Work Type]
	from tblSAPCodesImport as SAP
	left join vTreeviewTEST on SAP.Customer = L1Item and
                               SAP.[Work Type] = L2Item
        inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Customer]
        where L2Item is null 
   
Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Work Type'
        	goto Err
        End
print 'End of Scenario1  Level2'
/*
=============================================================================
 level 3
=============================================================================
*/

insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
	select distinct TV1.TV_Id, SAP.[Work Sub Type]
	from tblSAPCodesImport as SAP
	left join vTreeviewTEST on SAP.Customer = L1Item and
                               SAP.[Work Type] = L2Item and
                               SAP.[Work Sub Type] = L3Item
        inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Work Type]
        inner join tblTreeviewTEST TV2 on TV2.TV_Id = TV1.TV_TV_Id and
                                      TV2.tv_item = SAP.customer
        where  L3Item is null 
Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Work Sub Type'
        	goto Err
        End
print 'End of Scenario1  Level3'

/*
=============================================================================
 level 4
=============================================================================
*/

insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
	select distinct TV1.TV_Id, SAP.[Service Order / Network]
	from tblSAPCodesImport as SAP
	left join vTreeviewTEST on SAP.Customer = L1Item and
                               SAP.[Work Type] = L2Item and
                               SAP.[Work Sub Type] = L3Item and
                               SAP.[Service Order / Network] = L4Item 
        inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Work Sub Type]
        inner join tblTreeviewTEST TV2 on TV2.TV_Id = TV1.TV_TV_Id and
                                      TV2.tv_item = SAP.[Work Type]
        inner join tblTreeviewTEST TV3 on TV3.TV_Id = TV2.TV_TV_Id and
                                      TV3.tv_item = SAP.[Customer]
        where  L4Item is null 

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Service Order/Network'
        	goto Err
        End
print 'End of Scenario1  Level4'

/*
=============================================================================
 level 5
=============================================================================
*/

insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
	select distinct TV1.TV_Id, SAP.[Description]
	from tblSAPCodesImport as SAP
	left join vTreeviewTEST on SAP.Customer = L1Item and
                               SAP.[Work Type] = L2Item and
                               SAP.[Work Sub Type] = L3Item and
                               SAP.[Service Order / Network] = L4Item  and
                               SAP.[description] = L5Item
        inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Service Order / Network]
        inner join tblTreeviewTEST TV2 on TV2.TV_Id = TV1.TV_TV_Id and
                                      TV2.tv_item = SAP.[Work Sub Type]
        inner join tblTreeviewTEST TV3 on TV3.TV_Id = TV2.TV_TV_Id and
                                      TV3.tv_item = SAP.[Work Type]
        inner join tblTreeviewTEST TV4 on TV4.TV_Id = TV3.TV_TV_Id and
                                      TV4.tv_item = SAP.[Customer]
        where  L5Item is null 

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Description'
        	goto Err
        End
print 'End of Scenario1  Level5'

/*
=============================================================================
 level 6
=============================================================================
*/

insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
	select distinct TV1.TV_Id, SAP.[Code]
	from tblSAPCodesImport as SAP
	left join vTreeviewTEST on SAP.Customer = L1Item and
                               SAP.[Work Type] = L2Item and
                               SAP.[Work Sub Type] = L3Item and
                               SAP.[Service Order / Network] = L4Item  and
                               SAP.[description] = L5Item and
                               SAP.[Code] = L6Item
        inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Description]
        inner join tblTreeviewTEST TV2 on TV2.TV_Id = TV1.TV_TV_Id and
                                      TV2.tv_item = SAP.[Service Order / Network]
        inner join tblTreeviewTEST TV3 on TV3.TV_Id = TV2.TV_TV_Id and
                                      TV3.tv_item = SAP.[Work Sub Type]
        inner join tblTreeviewTEST TV4 on TV4.TV_Id = TV3.TV_TV_Id and
                                      TV4.tv_item = SAP.[Work Type]
        inner join tblTreeviewTEST TV5 on TV5.TV_Id = TV4.TV_TV_Id and
                                      TV5.tv_item = SAP.[Customer]
        where  L6Item is null 

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Code'
        	goto Err
        End
print 'End of Scenario1  Level6'


/*
=============================================================================
				Scenario 2
=============================================================================
 level 1
=============================================================================
*/

 Update tblTreeviewTEST
        set TV_ClosedF = 0
	from tblSAPCodesImport as SAP
	inner join vTreeviewTEST on SAP.Customer = L1Item
	inner join tblTreeviewTEST on 
	(TV_Id1 = TV_Id)
-- 	 TV_Id2 = TV_Id or
-- 	 TV_Id3 = TV_Id or
-- 	 TV_Id4 = TV_Id or
-- 	 TV_Id5 = TV_Id or
-- 	 TV_Id6 = TV_Id )
	 where TV_ClosedF1 = 1
Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Customer'
        	goto Err
        End

print 'End of Scenario2  Level1'

/*
=============================================================================
 level 2
=============================================================================
*/

Update tblTreeviewTEST
        set TV_ClosedF = 0
	from tblSAPCodesImport as SAP
	inner join vTreeviewTEST on SAP.Customer = L1Item and
                               SAP.[Work Type] = L2Item
        inner join tblTreeviewTEST on 
	(TV_Id2 = TV_Id )
-- 	 TV_Id3 = TV_Id or
-- 	 TV_Id4 = TV_Id or
-- 	 TV_Id5 = TV_Id or
-- 	 TV_Id6 = TV_Id )
	 where TV_ClosedF2 = 1
set @rc = @@Error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Work Type'
        	goto Err
        End
print 'End of Scenario2  Level2'

/*
=============================================================================
 level 3
=============================================================================
*/

Update tblTreeviewTEST
        set TV_ClosedF = 0
	from tblSAPCodesImport as SAP
	inner join vTreeviewTEST on SAP.Customer = L1Item and
                                SAP.[Work Type] = L2Item and
                                SAP.[Work Sub Type] = L3Item
        inner join tblTreeviewTEST on 
	(TV_Id3 = TV_Id )
-- 	 TV_Id4 = TV_Id or
-- 	 TV_Id5 = TV_Id or
-- 	 TV_Id6 = TV_Id )
	 where TV_ClosedF3 = 1

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Work Sub Type'
        	goto Err
        End
print 'End of Scenario2  Level3'

/*
=============================================================================
 level 4
=============================================================================
*/

Update tblTreeviewTEST
        set TV_ClosedF = 0
	from tblSAPCodesImport as SAP
	inner join vTreeviewTEST on SAP.Customer = L1Item and
                                SAP.[Work Type] = L2Item and
                                SAP.[Work Sub Type] = L3Item and
                                SAp.[Service Order / Network] = L4Item
        inner join tblTreeviewTEST on 
	(TV_Id4 = TV_Id )
-- 	 TV_Id5 = TV_Id or
-- 	 TV_Id6 = TV_Id )
	 where TV_ClosedF4 = 1

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Service Order/Network'
        	goto Err
        End
print 'End of Scenario2  Level4'

/*
=============================================================================
 level 5
=============================================================================
*/

Update tblTreeviewTEST
        set TV_ClosedF = 0
	from tblSAPCodesImport as SAP
	inner join vTreeviewTEST on SAP.Customer = L1Item and
                                SAP.[Work Type] = L2Item and
                                SAP.[Work Sub Type] = L3Item and
                                SAp.[Service Order / Network] = L4Item and
                                SAP.[Description] = L5Item
        inner join tblTreeviewTEST on 
	(TV_Id5 = TV_Id )
-- 	 TV_Id6 = TV_Id )
	 where TV_ClosedF5 = 1

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging description '
        	goto Err
        End
print 'End of Scenario2  Level5'

/*
=============================================================================
 level 6
=============================================================================
*/

Update tblTreeviewTEST
        set TV_ClosedF = 0
	from tblSAPCodesImport as SAP
	inner join vTreeviewTEST on SAP.Customer = L1Item and
                                SAP.[Work Type] = L2Item and
                                SAP.[Work Sub Type] = L3Item and
                                SAp.[Service Order / Network] = L4Item and
                                SAP.[Description] = L5Item and
                                SAP.[Code] = L6Item
        inner join tblTreeviewTEST on 
	(TV_Id6 = TV_Id )
	 where TV_ClosedF6 = 1

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Code'
        	goto Err
        End
print 'End of Scenario2  Level6'

/*
=============================================================================
				Scenario 3
=============================================================================
 level 1
=============================================================================
*/

 Update tblTreeviewTEST
          set TV_ClosedF = 1
	  from tblSAPCodesImport as SAP
	  right join vTreeviewTEST on SAP.Customer = L1Item
	  inner join tblTreeviewTEST on 
	  (TV_Id1 = TV_Id or
	   TV_Id2 = TV_Id or
	   TV_Id3 = TV_Id or
	   TV_Id4 = TV_Id or
	   TV_Id5 = TV_Id or
	   TV_Id6 = TV_Id )
	   where customer is null and
	         TV_ClosedF1 = 0
Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Customer'
        	goto Err
        End
print 'End of Scenario3  Level1'

/*
=============================================================================
 level 2
=============================================================================
*/

Update tblTreeviewTEST
          set TV_ClosedF = 1
	  from tblSAPCodesImport as SAP 
	  right join vTreeviewTEST on SAP.Customer = L1Item and
                                  SAP.[Work Type] = L2Item 
	  inner join tblTreeviewTEST on 
	  (TV_Id2 = TV_Id or
	   TV_Id3 = TV_Id or
	   TV_Id4 = TV_Id or
	   TV_Id5 = TV_Id or
	   TV_Id6 = TV_Id )
	   where customer is null and
                 [work type] is null and
	         TV_ClosedF2 = 0

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Work ype'
        	goto Err
        End
print 'End of Scenario3  Level2'

/*
=============================================================================
 level 3
=============================================================================
*/

Update tblTreeviewTEST
          set TV_ClosedF = 1
	  from tblSAPCodesImport as SAP 
	  right join vTreeviewTEST on SAP.Customer = L1Item and
                                  SAP.[Work Type] = L2Item and
                                  SAP.[Work Sub Type] = L3Item 
	  inner join tblTreeviewTEST on 
          (TV_Id3 = TV_Id or
           TV_Id4 = TV_Id or
	   TV_Id5 = TV_Id or
	   TV_Id6 = TV_Id )
	   where customer is null and
                 [work type] is null and
                 [Work Sub Type] is null and
                 TV_ClosedF3 = 0

set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Work SubType'
        	goto Err
        End
print 'End of Scenario3  Level3'

/*
=============================================================================
 level 4
=============================================================================
*/

Update tblTreeviewTEST
          set TV_ClosedF = 1
	  from tblSAPCodesImport as SAP 
	  right join vTreeviewTEST on SAP.Customer = L1Item and
                                  SAP.[Work Type] = L2Item and
                                  SAP.[Work Sub Type] = L3Item and
                                  SAP.[Service Order / Network] = L4Item
	  inner join tblTreeviewTEST on 
	  (TV_Id4 = TV_Id or
	   TV_Id5 = TV_Id or
	   TV_Id6 = TV_Id )
	   where customer is null and
                 [work type] is null and
                 [Work Sub Type] is null and
                 [Service Order / Network] is null and
	         TV_ClosedF4 = 0

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Service Order/Newtork'
        	goto Err
        End

print 'End of Scenario3  Level4'

/*
=============================================================================
 level 5
=============================================================================
*/

Update tblTreeviewTEST
          set TV_ClosedF = 1
	  from tblSAPCodesImport as SAP 
	  right join vTreeviewTEST on SAP.Customer = L1Item and
                                  SAP.[Work Type] = L2Item and
                                  SAP.[Work Sub Type] = L3Item and
                                  SAP.[Service Order / Network] = L4Item and
                                  SAP.[Description] = L5Item
	  inner join tblTreeviewTEST on 
	  (TV_Id5 = TV_Id or
	   TV_Id6 = TV_Id )
	   where customer is null and
                 [work type] is null and
                 [Work Sub Type] is null and
                 [Service Order / Network] is null and
                 [Description] is null and
	         TV_ClosedF5 = 0

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Dsecription'
        	goto Err
        End
print 'End of Scenario3  Level5'

/*
=============================================================================
 level 6
=============================================================================
*/

Update tblTreeviewTEST
          set TV_ClosedF = 1
	  from tblSAPCodesImport as SAP 
	  right join vTreeviewTEST on SAP.Customer = L1Item and
                                  SAP.[Work Type] = L2Item and
                                  SAP.[Work Sub Type] = L3Item and
                                  SAP.[Service Order / Network] = L4Item and
                                  SAP.[Description] = L5Item and
                                  SAP.[Code] = L6Item
	  inner join tblTreeviewTEST on 
	  (TV_Id6 = TV_Id )
	   where customer is null and
                 [work type] is null and
                 [Work Sub Type] is null and
                 [Service Order / Network] is null and
                 [Description] is null and
                 [Code] is null and
	         TV_ClosedF6 = 0

Set @rc = @@error
If @rc <> 0  
	Begin
        	set @LE_ErrCode = @rc
		set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Code'
        	goto Err
        End
print 'End of Scenario3  Level6'


commit tran ImportSapCodes

return @rc

Err:
    
    if @@trancount > 0 rollback tran ImportSapCodes
    exec uspInsertLogError @LE_ErrCode, @LE_ErrMsg
    print 'There has been an error !'
    return @rc
     



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
362
Volvere
V
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
337
pjw001
pjw001
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
468
mikrom
mikrom
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
232
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
205
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top