SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Rollback issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mk_garg20
Constraint Violating Yak Guru

Australia
343 Posts

Posted - 03/28/2007 :  00:32:06  Show Profile  Reply with Quote
Here is code
Last statement is insert into temp values('sss').
temp table does not exists.
Why XACT_ABORT is not rolling back all data manipulation in transaction?



CREATE  PROCEDURE dbo.usp_UI_MoveAccount
(
	@AccountID int,
	@SourceDeptID int,
	@DestDeptID int
)

AS
   
SET NOCOUNT ON
SET XACT_ABORT ON
set transaction isolation level read uncommitted

BEGIN TRAN

DECLARE  @tblAccount_KeywordCreative TABLE
(
	Ident int IDENTITY (1, 1) NOT NULL,
	KeywordCreativeID int,
	SortIndex int
)
INSERT INTO @tblAccount_KeywordCreative 
SELECT KeywordCreativeID,SortIndex
FROM tblAccount_KeywordCreative AKC
WHERE AKC.AccountID=@AccountID

print '@tblAccount_KeywordCreative created'

DECLARE  @tblListing TABLE
(
	ListingID int,
	AdGroupID int
)
INSERT INTO @tblListing 
SELECT ListingID,AdGroupID
FROM tblListing
WHERE AccountID=@AccountID

print '@tblListing created'
DECLARE  @tblAdGroupCreative TABLE
(
	AdGroupCreativeID int,
	KeywordCreativeID int	
)

IF EXISTS (Select * from @tblListing WHERE AdGroupID IS NOT NULL)
BEGIN
	INSERT INTO @tblAdGroupCreative
	Select AGC.AdGroupCreativeID,AGC.KeywordCreativeID
	FROM @tblListing l 
	inner join tblAdGroup_AdGroupCreative A_AC on A_AC.AdGroupID=l.AdGroupID
	inner join tblAdGroupCreative AGC on AGC.AdGroupCreativeID=A_AC.AdGroupCreativeID
	WHERE l.AdGroupID IS NOT NULL

END

print '@tblAdGroupCreative created'
DECLARE  @tblListingCreative TABLE
(
	ListingCreativeID int,
	KeywordCreativeID int	
)
INSERT INTO @tblListingCreative 
Select LC.ListingCreativeID,LC.KeywordCreativeID
FROM @tblListing l 
inner join tblListing_ListingCreative L_LC on L_LC.ListingID=l.ListingID
inner join tblListingCreative LC on LC.ListingCreativeID=L_LC.ListingCreativeID

print '@tblListingCreative created'

DELETE FROM tblAccount_KeywordCreative WHERE AccountID=@AccountID
print '@tblListingCreative deleted'

DECLARE  @curr_id int,@highest_id int,@next_id int
DECLARE @NewKeywordCreativeID int,@NewKeywordTemplateGroupID int,@CurrKeywordCreativeID int

SELECT  @curr_id=0
SELECT  @highest_id=MAX(Ident) FROM @tblAccount_KeywordCreative 
WHILE @curr_id <@highest_id
BEGIN
	SELECT TOP 1 @next_id=Ident
	FROM @tblAccount_KeywordCreative
	WHERE Ident>@curr_id
	ORDER BY Ident
print @next_id
	SELECT @NewKeywordCreativeID=dbo.ufn_CkeckKeywordCreativeExists(@DestDeptID, ProductTypeCode,
	LanguageCode, PRDTitle,PRDDescription, PRDDescription1, PRDDisplayUrl,
	CTYTitle, CTYDescription, CTYDescription1, CTYDisplayUrl,
	CNTTitle, CNTDescription, CNTDescription1,CNTDisplayUrl,
	GENTitle, GENDescription, GENDescription1, GENDisplayUrl)
	FROM @tblAccount_KeywordCreative t
	Inner Join tblKeywordCreative KC ON KC.KeywordCreativeID=t.KeywordCreativeID 
	WHERE t.Ident=@next_id

	IF @NewKeywordCreativeID=0 
	BEGIN
		INSERT INTO tblKeywordCreative
		SELECT @DestDeptID, ProductTypeCode, LanguageCode, KeywordCreativeName, PRDTitle, 
		PRDDescription, PRDDescription1, PRDDisplayUrl, CTYTitle, CTYDescription, 
		CTYDescription1, CTYDisplayUrl, CNTTitle, CNTDescription, CNTDescription1,
		CNTDisplayUrl, GENTitle, GENDescription, GENDescription1, GENDisplayUrl, 
		InsertDate, UpdateDate, UpdatedByUserID, TempDataImport_NonGoogleKeywordDetailID, 
		KC.TempDataImport_GoogleCountryID
		FROM @tblAccount_KeywordCreative t
		Inner Join tblKeywordCreative KC ON KC.KeywordCreativeID=t.KeywordCreativeID 
		WHERE t.Ident=@next_id
		
		SELECT @NewKeywordCreativeID=SCOPE_IDENTITY()
	END 


	IF NOT EXISTS (SELECT * FROM tblAccount_KeywordCreative WHERE AccountID=@AccountID and  KeywordCreativeID=@NewKeywordCreativeID)
	BEGIN
		INSERT INTO tblAccount_KeywordCreative 
		SELECT @AccountID,@NewKeywordCreativeID,SortIndex
		FROM @tblAccount_KeywordCreative t
		WHERE t.Ident=@next_id
	END

	SELECT @CurrKeywordCreativeID=KeywordCreativeID FROM @tblAccount_KeywordCreative t WHERE t.Ident=@next_id

	IF EXISTS (Select * from @tblAdGroupCreative)
	BEGIN
		UPDATE tblAdGroupCreative SET KeywordCreativeID=@NewKeywordCreativeID
		WHERE AdGroupCreativeID  IN (select AdGroupCreativeID FROM @tblAdGroupCreative WHERE KeywordCreativeID=@CurrKeywordCreativeID	)
	END

	IF EXISTS (Select * from @tblListingCreative)
	BEGIN
		UPDATE tblListingCreative SET KeywordCreativeID=@NewKeywordCreativeID
		WHERE ListingCreativeID IN (select ListingCreativeID FROM @tblListingCreative WHERE KeywordCreativeID= @CurrKeywordCreativeID)
	END
	SET @curr_id=@next_id

END 

DECLARE @S_KeywordTemplateGroupID int

SELECT @S_KeywordTemplateGroupID=KeywordTemplateGroupID 
FROM tblAccount 
WHERE  AccountID=@AccountID and DepartmentID=@SourceDeptID

IF EXISTS (Select * from tblKeywordTemplateGroup_KeywordTemplate WHERE KeywordTemplateGroupID=@S_KeywordTemplateGroupID )
BEGIN
	SELECT @NewKeywordTemplateGroupID=dbo.ufn_CheckKeywordTemplateGroupExists(@S_KeywordTemplateGroupID,@DestDeptID)
	IF @NewKeywordTemplateGroupID=0
	BEGIN
		INSERT INTO tblKeywordTemplateGroup
		SELECT 	@DestDeptID,KTG.KeywordTemplateGroupName,
		KTG.InsertDate,KTG.UpdateDate,KTG.UpdatedByUserID,
		KTG.TempDataImport_OldTemplateGroupID 
		FROM tblKeywordTemplateGroup KTG 
		WHERE KTG.KeywordTemplateGroupID=@S_KeywordTemplateGroupID
	
		SELECT @NewKeywordTemplateGroupID=SCOPE_IDENTITY()
		
		INSERT INTO tblKeywordTemplateGroup_KeywordTemplate
		SELECT  @NewKeywordTemplateGroupID,KTGKT.KeywordTemplateID,
			KTGKT.InsertDate,KTGKT.InsertedByUserID
		FROM tblKeywordTemplateGroup_KeywordTemplate KTGKT
		WHERE KeywordTemplateGroupID=@S_KeywordTemplateGroupID
	END
	
	UPDATE tblAccount SET DepartmentID=@DestDeptID,KeywordTemplateGroupID=@NewKeywordTemplateGroupID
	WHERE AccountID=@AccountID and DepartmentID=@SourceDeptID
END
ELSE
BEGIN
	UPDATE tblAccount SET DepartmentID=@DestDeptID
	WHERE AccountID=@AccountID and DepartmentID=@SourceDeptID
END

insert into temp values('sss')

COMMIT TRAN

GO





mk_garg

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 03/28/2007 :  13:13:21  Show Profile  Reply with Quote
A rollback will not affect table variables. But, I assume this is not
the issue you are having?

Since I hit the wrong button I will continue. SET XACT_ABORT ON is known to be the cause of a lot of head aches. I personaly do not use it because of the confusion it can cause. Check out this URL, it provides a little more information about XACT_ABORT:http://www.softwarevoices.com/archives/25-Set-XACT_Abort-ON-Not-quite-the-Panacea-we-thought-it-would-be.html

-Ryan

Edited by - Lamprey on 03/28/2007 13:21:32
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

Australia
343 Posts

Posted - 03/28/2007 :  18:36:30  Show Profile  Reply with Quote
Thanks Ryan!

mk_garg
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000