Help in Stored Procedure sqlserver
-
Hi all, I am executing the following SP. But when i execute it, I get the error Server: Msg 16929, Level 16, State 1, Procedure BicRank, Line 34. The cursor is readonly. The Statement has been terminated. Please help me to come out from this problem. Its very urgent for me. This is the SP. SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.BicRank / CREATE PROCEDURE BicRank AS -- determine mo3 bic and m03 ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS') -- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007 --rankings are completed per dmoq, service, bicuniverse combination --100% is considered best Declare BicRank_DESC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where (Mo3_Percent < 101) and dmoq in ('TTR_1','TTR_2','TTR_3','TTR_6','TTR_12','TTR_24','PROGRS') order by compare, Mo3_percent desc declare @3mo as float, @rank as int, @heldcompare as varchar(60), @bic as float, @firstbic as float, @compare as varchar(60) declare @dmoq as varchar(20), @service as varchar(20), @bicuniverse as varchar(6), @RCD_CNT INTEGER OPEN BicRank_DESC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_DESC into @compare, @3mo while (@@fetch_status <> -1) begin IF (@heldcompare <> @compare) begin set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare end ELSE IF (@3mo <> @bic) begin set @rank = @rank + 1 set @bic = @3mo end SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_DESC FETCH NEXT FROM bicrank_DESC into @compare, @3mo END commit transaction CLOSE BicRank_DESC DEALLOCATE BicRank_DESC --determine mo3 bic and m03 ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3') --rankings are completed per dmoq, service, bicuniverse combination --0% is considered best Declare BicRank_ASC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where ( Mo3_Percent < 101 AND MO3_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F') order by compare, Mo3_percent asc OPEN BicRank_ASC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_ASC into @compare, @3mo while (@@fetch_status <> -1) begin IF @heldcompare <> @compare begin set @rank =
-
Hi all, I am executing the following SP. But when i execute it, I get the error Server: Msg 16929, Level 16, State 1, Procedure BicRank, Line 34. The cursor is readonly. The Statement has been terminated. Please help me to come out from this problem. Its very urgent for me. This is the SP. SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.BicRank / CREATE PROCEDURE BicRank AS -- determine mo3 bic and m03 ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS') -- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007 --rankings are completed per dmoq, service, bicuniverse combination --100% is considered best Declare BicRank_DESC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where (Mo3_Percent < 101) and dmoq in ('TTR_1','TTR_2','TTR_3','TTR_6','TTR_12','TTR_24','PROGRS') order by compare, Mo3_percent desc declare @3mo as float, @rank as int, @heldcompare as varchar(60), @bic as float, @firstbic as float, @compare as varchar(60) declare @dmoq as varchar(20), @service as varchar(20), @bicuniverse as varchar(6), @RCD_CNT INTEGER OPEN BicRank_DESC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_DESC into @compare, @3mo while (@@fetch_status <> -1) begin IF (@heldcompare <> @compare) begin set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare end ELSE IF (@3mo <> @bic) begin set @rank = @rank + 1 set @bic = @3mo end SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_DESC FETCH NEXT FROM bicrank_DESC into @compare, @3mo END commit transaction CLOSE BicRank_DESC DEALLOCATE BicRank_DESC --determine mo3 bic and m03 ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3') --rankings are completed per dmoq, service, bicuniverse combination --0% is considered best Declare BicRank_ASC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where ( Mo3_Percent < 101 AND MO3_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F') order by compare, Mo3_percent asc OPEN BicRank_ASC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_ASC into @compare, @3mo while (@@fetch_status <> -1) begin IF @heldcompare <> @compare begin set @rank =
This is probably a permissions issue. Check that you have the appropriate permissions to execute the updates. BTW, you could probably rewrite your code without using cursors.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
Hi all, I am executing the following SP. But when i execute it, I get the error Server: Msg 16929, Level 16, State 1, Procedure BicRank, Line 34. The cursor is readonly. The Statement has been terminated. Please help me to come out from this problem. Its very urgent for me. This is the SP. SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.BicRank / CREATE PROCEDURE BicRank AS -- determine mo3 bic and m03 ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS') -- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007 --rankings are completed per dmoq, service, bicuniverse combination --100% is considered best Declare BicRank_DESC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where (Mo3_Percent < 101) and dmoq in ('TTR_1','TTR_2','TTR_3','TTR_6','TTR_12','TTR_24','PROGRS') order by compare, Mo3_percent desc declare @3mo as float, @rank as int, @heldcompare as varchar(60), @bic as float, @firstbic as float, @compare as varchar(60) declare @dmoq as varchar(20), @service as varchar(20), @bicuniverse as varchar(6), @RCD_CNT INTEGER OPEN BicRank_DESC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_DESC into @compare, @3mo while (@@fetch_status <> -1) begin IF (@heldcompare <> @compare) begin set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare end ELSE IF (@3mo <> @bic) begin set @rank = @rank + 1 set @bic = @3mo end SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_DESC FETCH NEXT FROM bicrank_DESC into @compare, @3mo END commit transaction CLOSE BicRank_DESC DEALLOCATE BicRank_DESC --determine mo3 bic and m03 ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3') --rankings are completed per dmoq, service, bicuniverse combination --0% is considered best Declare BicRank_ASC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where ( Mo3_Percent < 101 AND MO3_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F') order by compare, Mo3_percent asc OPEN BicRank_ASC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_ASC into @compare, @3mo while (@@fetch_status <> -1) begin IF @heldcompare <> @compare begin set @rank =
would u please give me database only with fields too , so that i could run and help u by running this procedure. actually it is really hard to understand as like. u can use my mail id. :)
manoj Kumar Jha "Learn to smile at every situation. See it as an opportunity to prove your strength and ability."
-
Hi all, I am executing the following SP. But when i execute it, I get the error Server: Msg 16929, Level 16, State 1, Procedure BicRank, Line 34. The cursor is readonly. The Statement has been terminated. Please help me to come out from this problem. Its very urgent for me. This is the SP. SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.BicRank / CREATE PROCEDURE BicRank AS -- determine mo3 bic and m03 ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS') -- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007 --rankings are completed per dmoq, service, bicuniverse combination --100% is considered best Declare BicRank_DESC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where (Mo3_Percent < 101) and dmoq in ('TTR_1','TTR_2','TTR_3','TTR_6','TTR_12','TTR_24','PROGRS') order by compare, Mo3_percent desc declare @3mo as float, @rank as int, @heldcompare as varchar(60), @bic as float, @firstbic as float, @compare as varchar(60) declare @dmoq as varchar(20), @service as varchar(20), @bicuniverse as varchar(6), @RCD_CNT INTEGER OPEN BicRank_DESC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_DESC into @compare, @3mo while (@@fetch_status <> -1) begin IF (@heldcompare <> @compare) begin set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare end ELSE IF (@3mo <> @bic) begin set @rank = @rank + 1 set @bic = @3mo end SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_DESC FETCH NEXT FROM bicrank_DESC into @compare, @3mo END commit transaction CLOSE BicRank_DESC DEALLOCATE BicRank_DESC --determine mo3 bic and m03 ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3') --rankings are completed per dmoq, service, bicuniverse combination --0% is considered best Declare BicRank_ASC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where ( Mo3_Percent < 101 AND MO3_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F') order by compare, Mo3_percent asc OPEN BicRank_ASC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_ASC into @compare, @3mo while (@@fetch_status <> -1) begin IF @heldcompare <> @compare begin set @rank =
Your update statement is using "
where current of BicRank_DESC". This is only allowed if the cursor was declared with the "for update" clause. Regards Andy -
Your update statement is using "
where current of BicRank_DESC". This is only allowed if the cursor was declared with the "for update" clause. Regards Andy -
Your update statement is using "
where current of BicRank_DESC". This is only allowed if the cursor was declared with the "for update" clause. Regards AndyHi, I tried to apply for update like below in SP. Error: Server: Msg 16957, Level 16, State 4, Procedure BicRank, Line 12 FOR UPDATE cannot be specified on a READ ONLY cursor. SP: SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.BicRank Script Date: 08/01/2008 12:44:52 ******/ ALTER PROCEDURE BicRank AS -- determine mo3 bic and m03 ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS') -- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007 --rankings are completed per dmoq, service, bicuniverse combination --100% is considered best Declare BicRank_DESC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where (Mo3_Percent < 101) and dmoq in ('TTR_1','TTR_2','TTR_3','TTR_6','TTR_12','TTR_24','PROGRS') order by compare, Mo3_percent desc for update of mo3_bic,mo3_rank declare @3mo as float, @rank as int, @heldcompare as varchar(60), @bic as float, @firstbic as float, @compare as varchar(60) declare @dmoq as varchar(20), @service as varchar(20), @bicuniverse as varchar(6), @RCD_CNT INTEGER OPEN BicRank_DESC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_DESC into @compare, @3mo while (@@fetch_status <> -1) begin IF (@heldcompare <> @compare) begin set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare end ELSE IF (@3mo <> @bic) begin set @rank = @rank + 1 set @bic = @3mo end SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_DESC FETCH NEXT FROM bicrank_DESC into @compare, @3mo END commit transaction CLOSE BicRank_DESC DEALLOCATE BicRank_DESC --determine mo3 bic and m03 ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3') --rankings are completed per dmoq, service, bicuniverse combination --0% is considered best Declare BicRank_ASC Cursor for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel where ( Mo3_Percent < 101 AND MO3_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F') order by compare, Mo3_percent asc for update of mo3_bic,mo3_rank OPEN BicRank_ASC set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' FETCH NEXT FROM bicrank_ASC into @compare, @3mo while (@@fetch_status <> -1) begin IF @heldcompare <> @compare begin set @rank = 1 set @bic =