Solved

Unable to open cursor created in SQL Express

Posted on 2009-03-29
2
647 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now