Solved

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

Posted on 2009-04-10
4
417 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now