?
Solved

Unable to open cursor created in SQL Express

Posted on 2009-03-29
2
Medium Priority
?
672 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 150 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 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