Solved

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

Posted on 2009-04-10
4
412 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

14 Experts available now in Live!

Get 1:1 Help Now