Cursor Does Not Exist Error Sql Server
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more
Sql Server Error 16916
about Stack Overflow the company Business Learn more about hiring developers or posting ads cursor already exists error with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow
A Cursor With The Name Already Exists
is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up A cursor with the name 'cursor_name' does not exist up vote 1 cursor is not open sql server down vote favorite I have a code the uses nested cursors. When I parse it, SQL studio tells me "Command(s) completed successfully," but whenever I execute, I get a bunch of repeated "A cursor with the name 'cursor_stats' does not exist." The error message displays for every line cursor_stats is mentioned in, then repeats many times. Any idea what my problem is? DECLARE @dc_grp AS VARCHAR(50) DECLARE @reqt_id AS INT the cursor is already open DECLARE cursor_pairs CURSOR FOR SELECT DISTINCT dc.dc_grp, dcx.reqt_id FROM DC_GRP dc INNER JOIN DC_GRPX dcx ON dc.dc_grp = dcx.dc_grp INNER JOIN REQT req ON dcx.reqt_id = req.reqt_id WHERE dc.calc_stddev = 1 AND req.v_a = 'V' AND dcx.stddev_last_update != CONVERT(datetime, Convert(int, GetDate())) ORDER BY dc.dc_grp, dcx.reqt_id -------------------------------------- DECLARE @vavg AS FLOAT DECLARE @vstddev AS FLOAT DECLARE cursor_stats CURSOR FOR SELECT AVG(r.[var]), STDEV(r.[var]) FROM RESULTS r INNER JOIN INSTANCE i ON r.inst_id = i.inst_id WHERE i.dc_grp = @dc_grp AND r.reqt_id = @reqt_id AND r.[var] != 0 AND r.inst_id IN ( SELECT TOP 100 inst_id FROM RESULTS WHERE reqt_id = @reqt_id ORDER BY inst_id DESC ) --------------------------------------- OPEN cursor_pairs FETCH NEXT FROM cursor_pairs INTO @dc_grp, @reqt_id WHILE @@FETCH_STATUS = 0 BEGIN OPEN cursor_stats FETCH NEXT FROM cursor_stats INTO @vavg, @vstddev WHILE @@FETCH_STATUS = 0 BEGIN PRINT @dc_grp + ' ' + @reqt_id + ' ' + @vavg + ' ' + @vstddev FETCH NEXT FROM cursor_stats INTO @vavg, @vstddev END CLOSE cursor_stats DEALLOCATE cursor_stats FETCH NEXT FROM cursor_pairs INTO @dc_grp, @reqt_id END CLOSE cursor_pairs DEALLOCATE cursor_pairs sql cursor share|improve this question asked Jul 31 '13 at 13:39 Kevin Kesicki 50312 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted You should NOT DEALLOCA
SQL Server experts to answer whatever question you can come up with. Our new SQL Server
Sql Cursor Within A Cursor
Forums are live! Come on over! We've restricted the ability to alternative to cursor create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ
Fast_forward Sql Server
Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums General SQL Server Forums New to SQL Server Administration Cursor does not exists http://stackoverflow.com/questions/17972022/a-cursor-with-the-name-cursor-name-does-not-exist Reply to Topic Printer Friendly Author Topic vijays3 Constraint Violating Yak Guru India 354 Posts Posted-04/24/2010: 23:46:46 Hi All,,I want to shrink the databases if they are online and in simple recovery mode i have written a store stored procedure but the problem is when i execute it it says that cursor does not exists..Here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143534 is the codeCREATE PROCEDURE SRINKDATABASEASCREATE TABLE #TEMP ( DBNAME VARCHAR(50), STATUS VARCHAR(20), UPDATEABILITY VARCHAR(50), RECOVERY VARCHAR(10))INSERT INTO #TEMP SELECT A.DBNAME,A.STATUS,A.UPDATEABILITY,A.RECOVERY FROM ( SELECT DBname=name, Status= CONVERT(VARCHAR(20),DatabasePropertyEx(name,'Status')), Updateability= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'Updateability')), UserAccess= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'UserAccess')), Recovery= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'Recovery')), Version= CONVERT(VARCHAR(10),DatabasePropertyEx(name,'Version'))from sysdatabases ) A WHERE A.DBNAME NOT IN ('MASTER','TEMPDB','MODEL','MSDB')DECLARE @CUR_NM VARCHAR(50)DECLARE @CUR_STAT VARCHAR(20)DECLARE @CUR_UPDT VARCHAR(50)DECLARE @CUR_REC VARCHAR(10)DECLARE @MSG VARCHAR(100)DECLARE CHKDATABASE_CUR CURSOR FOR SELECT DBNAME,STATUS,RECOVERY FROM #TEMPOPEN CHKDATABASE_CURFETCH NEXT FROM CHKDATABASE_CUR INTO @CUR_NM,@CUR_STAT,@CUR_RECWHILE (@@FETCH_STATUS=0)BEGINIF (@CUR_STAT = 'ONLINE' AND @CUR_REC = 'SIMPLE') BEGIN DBCC SHRINKDATABASE (@CUR_NM, TRUNCATEONLY) SET @MSG = 'NAME OF THE DATABSE SHRINKED IS ' + @CUR_NM PRINT @MSG FETCH NEXT FROM CHKDATABASE_CUR INTO @CUR_NM,@CUR_STAT,@CUR_REC END CLOSE CHKDATABASE_CURDeallocate CHKDATABASE_CURENDPlease help me to dig it out.Below is the error maessage it is keep giving.Msg 16916, Level 16, State 1, Line 24A cursor with the name 'CHKDATABASE_CUR' does not exist.Msg 16916, Level 16, State 1, Line 25A cursor with the name 'CHKDATABASE_CUR' does not exist.Msg 16916, Level 16, State 1, Line 24A cursor with the name 'CHKDATABASE_CUR' does not exist.Msg 16916, Level 16, State 1, Line 25A cursor with the
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2008 » SQL Server 2008 - General » A cursor with the http://www.sqlservercentral.com/Forums/Topic1306338-391-1.aspx name 'CursorName' already... A cursor with the name 'CursorName' already exists. Rate Topic Display Mode Topic Options Author Message B Raj DhakalB Raj Dhakal Posted Friday, May 25, 2012 12:55 AM SSC Rookie Group: General Forum Members Last Login: Sunday, March 20, 2016 5:14 PM Points: 30, Visits: 112 Hi there,I am working to upgrade SQL Server 2000 to SQL Server 2008R2. We have application developed on VB6. While running the application I encountered sql server an error where it is trying to update a table. The table has update trigger on which there is a Cussor called UpdateLoop. DECLARE UpdateLoop CURSOR FOR The error returned was Msg 16915, Level 16, State 1, Procedure JobUpdate, Line 16A cursor with the name 'UpdateLoop' already exists.The statement has been terminated.It was working perfectly fine on SQL Server 2000, there are several triggers having Cussor in the database tables.From my idea what cursor does not I know is, having cursor in a trigger is not a good idea as there is no guarantee that the previous cursor has been de-allocated before the next one is declared. I don’t mind to change it to set-based T-SQL statements, but it might be a time consuming. Just want to share the solution with those who are having the same trouble. After a long struggle, I finally found the solution. Change the scope of the cursor to local.DECLARE UpdateLoop CURSOR LOCAL FOR B Raj Dhakal Post #1306338 derek.colleyderek.colley Posted Friday, May 25, 2012 2:44 AM SSC-Addicted Group: General Forum Members Last Login: Thursday, October 1, 2015 5:02 AM Points: 496, Visits: 603 Thanks for this, I wasn't aware that CURSORs could be specified in context.If you had posed this as a problem I would have said to deallocate and close the cursor in the code immediately preceding your cursor definition.I'll read up on the syntax today if I get time. Cheers. ---Note to developers: CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1So why complicate your code AND MAKE MY JOB HARDER??!Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)My blog: http://uksqldba.blogspot.comVisit http://www.DerekColley.co.uk to find out more about me. Post #1306386 vinu512vinu512 Posted Friday, May 25, 2012 5:40 AM Ten Centuries Group: General Forum Members Last Login: