[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-04-10
4
Medium Priority
?
432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

650 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