Error 16915 Sql Server
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might a cursor with the name already exists sql server have Meta Discuss the workings and policies of this site About a cursor with the name does not exist Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting cursor already exists error in sql server ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7
The Cursor Is Already Open.
million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Cursor problem up vote 1 down vote favorite I am getting the following errors but can't figure out why.. Msg 16915, Level 16, State 1, Procedure client_myClientsProc, Line 46 A cursor with the name 'cur_keywords' already exists. deallocate cursor Msg 16905, Level 16, State 1, Procedure client_myClientsProc, Line 47 The cursor is already open. And then if I try to run it again it says Msg 208, Level 16, State 0, Procedure client_myClientsProc Line 49 Invalid object name '##CLIENTS_KEYWORD. Now this is old code that I am trying to fix, so please bear with me... ALTER PROCEDURE [dbo].[client_myclientsproc] @Keywords varchar(max), @Delimiter varchar(10) = ' ' AS BEGIN SET NOCOUNT ON; DECLARE @MYQUERY NVARCHAR(MAX); DECLARE @tempkeyword varchar(4000) DECLARE @TempCount INT IF OBJECT_ID('TempDB..##CLIENTS_KEYWORD') IS NOT NULL BEGIN DROP TABLE ##CLIENTS_KEYWORD END ELSE BEGIN CREATE TABLE ##CLIENTS_KEYWORD(client_id int) END IF OBJECT_ID('TempDB..##TEMP_CLIENTS_KEYWORD') IS NOT NULL BEGIN DROP TABLE ##TEMP_CLIENTS_KEYWORD END ELSE BEGIN CREATE TABLE ##TEMP_CLIENTS_KEYWORD(productid int) END SET @MYQUERY = 'SELECT clientID, Client_Name FROM MYCLIENTS WHERE ClientID IN '; IF(@Delimiter<>'none') BEGIN DECLARE cur_keywords CURSOR FOR select value from SC_Split(@Keywords,@Delimiter) OPEN cur_keywords FETCH NEXT FROM cur_keywords into @tempkeyword INSERT ##CLIENTS_KEYWORD(client_id) exec getClientsByKeyword @tempkeyword WHILE @@FETCH_STATUS = 0 FETCH NEXT FROM cur_keywords into @tempkeyword INSERT ##TEMP_CLIENTS_KEYWORD(client_id) exec getClients
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL cursor_status Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get
@@fetch_status
your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2000 Forums SQL Server Development (2000) Error 16915 -
Tsql Cursor
Cursor 'cursor_name' already exists Reply to Topic Printer Friendly Author Topic AskSQLTeam Ask SQLTeam Question USA 0 Posts Posted-09/18/2001: 10:47:13 Avinash writes "Hi all,I have a peculiar problem. I get the Error 16915 - http://stackoverflow.com/questions/7260218/sql-cursor-problem Cursor 'cursor_name' already exists. Despite the fact that we have done 'Close cursor' & 'Deallocate cursor' as soon as it is not required.My platform is ASP - VB Com + MTS - SQL 7 Stored Proc.This happens only when there are concurrent users to the SQL Server. Let me ask a few questions1. In the ASP page, if the user clicks on another hyperlink before the page is actually completed execution, does the SQL stored procedure http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9175 get executed completely or does it cancel execution.2. Is each cursor unique to each connection to the DB. Why I am asking this question is because my perception is that MTS does connection pooling using OLE DB SERVICES. So if there is reuse of Connections this problem may occur. Just a thought. If anyone has a concrete idea that would be great.Please do try to help me out of this. I need to reply to my client - He's got a knife to my throat :-)Thanks,Avinash." Topic Reply to Topic Printer Friendly Jump To: Select Forum General SQL Server Forums New to SQL Server Programming New to SQL Server Administration Script Library Data Corruption Issues Database Design and Application Architecture SQL Server 2012 Forums Transact-SQL (2012) SQL Server Administration (2012) SSIS and Import/Export (2012) Analysis Server and Reporting Services (2012) Replication (2012) Availability Groups and DR (2012) Other SQL Server 2012 Topics SQL Server 2008 Forums Transact-SQL (2008) SQL Server Administration (2008) SSIS and Import/Export (2008) High Availability (2008) Replication (2008) Analysis Server and Reporting Services (2008) Other SQL Server 2008 Topics SQL Server 2005 Forums Transact-SQL (2005) SQL Server Administration (2005) .NET Inside SQL Server (2005) SSIS and Import/Export (2005) Service Broker (2005) Replication (2005) High Availability (2005) Analysis Server and Reporting Services (2005) Express Edition and Compact Edition (2005) Other SQL Ser
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: A cursor https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f5a8cb10-d402-4f4c-a7a7-167829382873/a-cursor-with-the-name-already-exists?forum=transactsql with the name '*' already exists SQL Server > Transact-SQL Question 0 Sign in to vote Hi Guys, I have the following Stored Procedure. I keep getting http://sqlblog.com/blogs/aaron_bertrand/archive/2010/01/14/when-you-don-t-follow-your-own-bad-habits-advice.aspx errors: ALTER PROCEDURE [dbo].[billables_report] -- Add the parameters for the stored procedure here @case_id INT, @min_disbchrg DECIMAL(10,2) AS BEGIN SET NOCOUNT ON; DECLARE @cur_amount DECIMAL(10,2) DECLARE sql server @cur_batchno INT DECLARE @orig_disb DECIMAL(10,2) DECLARE @unbilled_disb DECIMAL(10,2) DECLARE bil_cursor CURSOR READ_ONLY FOR SELECT batch_no, amount FROM dbo.tbl_billables WHERE (dbo.tbl_billables.case_id = @case_id) AND ((dbo.tbl_billables.code_type)='D') AND ((dbo.tbl_billables.billed_ar)IS NULL) OPEN bill_cursor FETCH NEXT FROM bill_cursor INTO @cur_batchno, @cur_amount WHILE @@FETCH_STATUS = 0 BEGIN SET @orig_disb = ( SELECT SUM(dbo.tbl_billables.amount) FROM dbo.tbl_billables, dbo.tbl_disb_batch WHERE (dbo.tbl_billables.batch_no = a cursor with @cur_batchno) AND (dbo.tbl_disb_batch.batch_no = dbo.tbl_billables.batch_no) AND ((dbo.tbl_disb_batch.batch_split_flag)='Y')) IF @orig_disb IS NULL BEGIN SET @orig_disb = 0 END IF NOT (@orig_disb < @min_disbchrg AND @orig_disb !=0) OR NOT ((@cur_amount < @min_disbchrg) AND (@orig_disb < @min_disbchrg)) BEGIN SET @unbilled_disb = @unbilled_disb + @cur_amount END END CLOSE bill_cursor PRINT @unbilled_disb DEALLOCATE bill_cursor END Errors: Msg 16915, Level 16, State 1, Procedure billables_report, Line 18 A cursor with the name 'bil_cursor' already exists. Msg 16916, Level 16, State 1, Procedure billables_report, Line 23 A cursor with the name 'bill_cursor' does not exist. Msg 16916, Level 16, State 1, Procedure billables_report, Line 24 A cursor with the name 'bill_cursor' does not exist. Msg 16916, Level 16, State 1, Procedure billables_report, Line 43 A cursor with the name 'bill_cursor' does not exist. Msg 16916, Level 16, State 1, Procedure billables_report, Line 45 A cursor with the name 'bill_cursor' does not exist. What I'm I doing wrong? Thanks SD Thanks for everything, all you
of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide. When you don't follow your own "bad habits" advice... Today I came across a self-created problem that could have been avoided if I had only followed my own advice. It wasn't directly and explicitly mentioned in this context, but the concept is the same. Let's start with the source of the problem: one stored procedure that called another. The first ("inner") procedure was something like this: take a database_id as a parameter, and show all the table names with indexes that have more updates than seeks/scans/lookups: CREATE PROCEDUREdbo.bar@database_id INT,@object_id INT = NULLASBEGINSET NOCOUNT ON; PRINT'';PRINTDB_NAME(@database_id);PRINT''; DECLAREcCURSOR FORWARD_ONLY STATIC READ_ONLY FOR ( SELECTDISTINCT[object_id]FROMsys.dm_db_index_usage_statsWHERE [database_id] = @database_idAND[object_id] = COALESCE(@object_id,[object_id])ANDuser_updates>user_seeks+user_scans+user_lookups );OPENc; FETCH NEXT FROMcINTO@object_id; WHILE @@FETCH_STATUS = 0BEGINPRINT OBJECT_SCHEMA_NAME(@object_id,@database_id) +'.'+OBJECT_NAME(@object_id,@database_id); FETCH NEXT FROMcINTO@object_id;END DEALLOCATEc;ENDGO Now that is great, but what if I wanted to do this for multiple (or all databases), without modifying dbo.bar? Well that's simple, right? We'll just create a new ("outer") stored procedure, dbo.foo, which acts as a wrapper for dbo.bar. In reality this could probably take a comma-separated list of database names or IDs, but for brevity let's just keep it simple and assume we want a report for all databases. This just