[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.2

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

Asked by gpmforever in MS SQL Server

Tags: MSSQL Server 2000

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? 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:

/*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...
[+][-]04/10/09 10:20 PM, ID: 24120940Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zone: MS SQL Server
Tags: MSSQL Server 2000
Sign Up Now!
Solution Provided By: gpmforever
Participating Experts: 2
Solution Grade: A
 
[+][-]04/10/09 08:31 AM, ID: 24116324Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04/10/09 09:00 AM, ID: 24116585Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04/10/09 08:53 PM, ID: 24120834Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091111-EE-VQP-92 - Hierarchy / EE_QW_3_20080625