Solved

All my updates are Rollback When i alter the column...

Posted on 2009-04-10
4
422 Views
Last Modified: 2012-05-06
Hi Experts,
I wanted to alter the one of my table colum to not null. So first i update the null values to valid values. Once again i confirmed no other null values in my table.

After that  i was  trying to alter the column to NOT NULL, But following error was occur,

Server: Msg 5074, Level 16, State 8, Line 1
The statistics '_dta_stat_1643152899_4_2_12_7_11_14_6_3' is dependent on column 'PPAD_GROUP_ID'.
Server: Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1643152899_2_14_5_4_9_12_3_7_6' is dependent on column 'PPAD_GROUP_ID'.
Server: Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1643152899_1_2_14_5_4_9_12_3_7_6' is dependent on column 'PPAD_GROUP_ID'.
Server: Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1643152899_13_6_7_3_12_9_4_5_8_14_2' is dependent on column 'PPAD_GROUP_ID'.
Server: Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1643152899_14_5_4_9_12_3_7_6_13_2_1_8_11' is dependent on column 'PPAD_GROUP_ID'.
Server: Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1643152899_7_4_9_13_12_11_14_16_3_21_22_1_6' is dependent on column 'PPAD_GROUP_ID'.
Server: Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1643152899_6_1_4_2_12_7_11_14_3_9_13_16_21_22' is dependent on column 'PPAD_GROUP_ID'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN PPAD_GROUP_ID failed because one or more objects access this column.

After this error, my all update was rollback. How this happened?
/*Update GroupId in adjudicated claims table based on benefitplan id*/
/*Update 1*/
SELECT DISTINCT PPCP_CLAIMS_ID,PPCP_VERSION_ID,PPSS_GROUP_ID,PPSS_DIVISION_ID INTO #TEMPCLAIMS FROM T_PPCP_CLAIMS_PROCEDURE 
inner join T_PPAD_ADJUDICATED_CLAIM_DETAILS on PPAD_PK_CLAIM_ID =PPCP_CLAIMS_ID AND PPAD_PK_VERSION_ID = PPCP_VERSION_ID
inner join T_PPSS_SUBSCRIBER_DTLS on PPSS_PK_SUBSCRIBER_ID = PPAD_SUBSCRIBER_BENEFICIARY_ID
WHERE EXISTS(
SELECT * FROM T_PPAD_ADJUDICATED_CLAIM_DETAILS WHERE PPAD_GROUP_ID IS NULL
AND PPAD_PK_CLAIM_ID =PPCP_CLAIMS_ID AND PPAD_PK_VERSION_ID = PPCP_VERSION_ID
)
AND PPCP_BENEFIT_PLAN_ID IS NOT NULL
ORDER BY  PPCP_CLAIMS_ID
 
 
UPDATE T_PPAD_ADJUDICATED_CLAIM_DETAILS
SET PPAD_GROUP_ID = PPSS_GROUP_ID,
PPAD_DIVISION_ID = PPSS_DIVISION_ID
FROM #TEMPCLAIMS
INNER JOIN T_PPAD_ADJUDICATED_CLAIM_DETAILS ON PPAD_PK_CLAIM_ID =PPCP_CLAIMS_ID AND PPAD_PK_VERSION_ID = PPCP_VERSION_ID
 
 
/*Update 2*/
DROP TABLE #TEMPCLAIMS
 
SELECT PPCP_CLAIMS_PROCEDURE_ID,PPCL_MEMBER_BENEFICIARY_ID,PPCP_CLAIMS_ID,PPCP_VERSION_ID,PPCP_DATE_OF_SERVICE INTO #TEMPCLAIMS FROM T_PPCP_CLAIMS_PROCEDURE
INNER JOIN T_PPAD_ADJUDICATED_CLAIM_DETAILS ON PPAD_PK_CLAIM_ID =PPCP_CLAIMS_ID AND PPAD_PK_VERSION_ID = PPCP_VERSION_ID
INNER JOIN T_PPCL_CLAIMS ON  PPAD_PK_CLAIM_ID =PPCL_PK_CLAIM_ID AND PPAD_PK_VERSION_ID = PPCL_PK_VERSION_ID
WHERE PPAD_GROUP_ID IS NULL
 
 
SET NOCOUNT ON
 
DECLARE @ClaimID int
DECLARE @VersionID char(2)
DECLARE @ClaimProcedureID INT
DECLARE @MemberId VARCHAR(12)
DECLARE @i_DivisionId INT
DECLARE @i_GroupId INT
DECLARE @DOS DATETIME
 
SELECT DISTINCT PPCP_CLAIMS_ID,PPCP_VERSION_ID FROM #TEMPCLAIMS ORDER BY PPCP_CLAIMS_ID
 
DECLARE Claim_Cursor CURSOR FOR
 
SELECT PPCP_CLAIMS_PROCEDURE_ID,PPCL_MEMBER_BENEFICIARY_ID,PPCP_CLAIMS_ID,PPCP_VERSION_ID,PPCP_DATE_OF_SERVICE
FROM #TEMPCLAIMS
 
 
OPEN	Claim_Cursor
PRINT ' ' 
PRINT '---------------- PROCESS START --------------------'
PRINT ' ' 
 
FETCH NEXT 
FROM	Claim_Cursor
INTO	@ClaimProcedureID,@MemberId,@ClaimID,@VersionID,@DOS
 
WHILE @@FETCH_STATUS = 0 AND @@ERROR = 0
BEGIN 
 
	BEGIN TRAN Claim
 
		SELECT @i_GroupId = PPAD_GROUP_ID  FROM T_PPAD_ADJUDICATED_CLAIM_DETAILS WHERE PPAD_PK_CLAIM_ID = @ClaimID AND PPAD_PK_VERSION_ID = @VersionID
 
		SELECT PPAD_GROUP_ID  FROM T_PPAD_ADJUDICATED_CLAIM_DETAILS WHERE PPAD_PK_CLAIM_ID = @ClaimID AND PPAD_PK_VERSION_ID = @VersionID
 
		IF (@i_GroupId IS NULL)
		BEGIN
			SELECT @i_GroupId = PPEH_GROUP_ID,	
				@i_DivisionId = PPEH_DIVISION_ID
			FROM 	T_PPEH_ELIGIBILITY_HIST_DTLS 
			WHERE 	PPEH_BENEFICIARY_ID = @MemberId 
			AND 	(Convert(DATETIME,Convert(VARCHAR,PPEH_ELIGIBILITY_START_DATE,101)) <=CONVERT(DATETIME,CONVERT(VARCHAR,@DOS,101))
			AND 	Convert(DATETIME,Convert(VARCHAR,PPEH_ELIGIBILITY_END_DATE,101)) >= CONVERT(DATETIME,CONVERT(VARCHAR,@DOS,101)))
			
	
			IF(@i_GroupId IS NULL)    
			BEGIN    
			      SELECT @i_GroupId=PPSS_GROUP_ID,  
				@i_DivisionId = PPSS_DIVISION_ID  
			      FROM T_PPSS_SUBSCRIBER_DTLS    
			      WHERE PPSS_PK_SUBSCRIBER_ID =SUBSTRING(@MemberId,1,8)  
 
			END
 
			UPDATE 	T_PPAD_ADJUDICATED_CLAIM_DETAILS
			SET
			PPAD_GROUP_ID = @i_GroupId,
			PPAD_DIVISION_ID = @i_DivisionId
			WHERE
			PPAD_PK_CLAIM_ID = @ClaimID AND PPAD_PK_VERSION_ID = @VersionID  
			
			IF @@ERROR = 0
			BEGIN
				COMMIT TRAN Claim
				PRINT 'Transaction Committed - Claim ID: ' + CONVERT(varchar(20), @ClaimID) + '-' + @VersionID 
			END
			ELSE
			BEGIN
				ROLLBACK TRAN Claim
				PRINT 'Transaction Rolled Back - Claim ID: ' + CONVERT(varchar(20), @ClaimID) + '-' + @VersionID 
			END
 
		END
					
		
	FETCH NEXT 
	FROM	Claim_Cursor
	INTO	@ClaimProcedureID,@MemberId,@ClaimID,@VersionID,@DOS
 
END
 
CLOSE Claim_Cursor
DEALLOCATE Claim_Cursor
 
PRINT ' ' 
PRINT '---------------- PROCESS END --------------------'
PRINT ' ' 
 
GO
 
/*Update 3*/
 
SELECT * FROM T_PPAD_ADJUDICATED_CLAIM_DETAILS WHERE PPAD_GROUP_ID IS NULL
 
UPDATE T_PPAD_ADJUDICATED_CLAIM_DETAILS
SET PPAD_GROUP_ID = PPSS_GROUP_ID,
PPAD_DIVISION_ID = PPSS_DIVISION_ID
FROM T_PPAD_ADJUDICATED_CLAIM_DETAILS
INNER JOIN T_PPSS_SUBSCRIBER_DTLS on PPSS_PK_SUBSCRIBER_ID = PPAD_SUBSCRIBER_BENEFICIARY_ID
WHERE PPAD_GROUP_ID IS NULL
 
/*Alter Group Id Column*/
ALTER TABLE T_PPAD_ADJUDICATED_CLAIM_DETAILS
ALTER COLUMN PPAD_GROUP_ID INT NOT NULL
 
After updating this scrip error was thrown...

Open in new window

0
Comment
Question by:gpmforever
  • 2
4 Comments
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24116324
Try to discoonect all connections to your database while executing the script.
and second if the concerned table column if refered as a foreign key it must contain valid values....

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24116585
This is because in your table T_PPAD_ADJUDICATED_CLAIM_DETAILS, column PPAD_GROUP_ID has been referenced by many Foreign keys from other tables.

If you try to alter a Foreign Key column , then you have to remove the Foreign Key constraint and then create the Foreign key constraint back.
0
 

Author Comment

by:gpmforever
ID: 24120834
Thanks for both, But My question was not about the alter the column.. Because After Dropping the all the statistics associated with table,i can altered my column to not null. I have no doubt in this.. But How the roll back was happened, Once the transaction committed. I think its related to statistics property...I dont know about statistics So I need some deep analysis on this issue.....
0
 

Accepted Solution

by:
gpmforever earned 0 total points
ID: 24120940
Hi All, Finally i found the problem.. all my update was not committed fully...
Because my  "BEGIN TRAN Claim" was placed before the " IF (@i_GroupId IS NULL)||BEGIN". So some of the transaction are still opened after the update.  So I placed the  "BEGIN TRAN Claim" to inside the
" IF (@i_GroupId IS NULL)||BEGIN". Now all the transaction are commited..

I found this problem by following syntax..
select @@trancount

Once again Thanks for All.. Have a Nice day...

Regards,
Moov
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question