Solved

Unable to open cursor created in SQL Express

Posted on 2009-03-29
2
660 Views
Last Modified: 2012-05-06
Hi there,

I'm following the book 'Sams Teach Yourself SQL in 10 Minutes' in Lesson 21 about cursors. I've created the cursor in the book with the code below.

Then I tried to open the cursor as stated in the book using the Open method as below and it gives me an error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'cursor'.

So I look on the internet and try just open and the name of the cursor and I get the error:

Msg 16916, Level 16, State 1, Line 1
A cursor with the name 'CustCursor' does not exist.

I declare the cursor again with the same name and I get the error:

Msg 16915, Level 16, State 1, Line 3
A cursor with the name 'CustCursor' already exists.

So what I've figured is that the cursor 'CustCursor' is already created but I can't open it. Can anyone tell me what's going on?

Thanks
declare CustCursor cursor
for
select * from customers
where cust_email is null;
 
 
open cursor CustCursor;
 
 
open CustCursor;

Open in new window

0
Comment
Question by:longe77
[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 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 50 total points
ID: 24014087
Your cursor Structure is not complete. A complete cursor statement needs to have Declare, Open, Fetch, Close and Deallocate to avoid these kind of errors. An Example for Complete structure is given below:

DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
   BEGIN
      FETCH NEXT FROM Employee_Cursor;
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO

<< A cursor with the name 'CustCursor' already exists.>>

As I mentioned earlier, You have Opened a cursor without closing it. So it is not closed and hence a new cursor can't be opened in the same name. Kindly restart the SQL Services to get it closed out.
0
 

Author Closing Comment

by:longe77
ID: 31564089
Thanks rrjegan17.

I finally got it to work. I adapted your code and I inserted a print statement to print the contents of the variable. Well, as I am learning SQL from a book I haven't spotted the significance of using Cursors. I could have obtained the same results using a select statement.

Why would I need a cursor? I understand that a Cursor goes through records line by line.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 to SQL Server 2016 24 54
sql, how to change the data type after data loading? 9 58
SQL syntax for max(date) 3 38
SQL - Subquery in WHERE section 4 33
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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