Solved

Cursor problem

Posted on 2008-09-29
7
311 Views
Last Modified: 2012-05-05
Can anyone see in the attached code why I am getting the following errors?

Msg 16915, Level 16, State 1, Procedure sp_dealerClassNotifications, Line 56
A cursor with the name 'CDealers' already exists.
Msg 16905, Level 16, State 1, Procedure sp_dealerClassNotifications, Line 67
The cursor is already open.

Thanks.
PROCEDURE [dbo].[sp_dealerClassNotifications]
	-- Add the parameters for the stored procedure here
AS
--BEGIN try
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	declare @c_classBookingClassID int;
	declare @c_bookedCount int;
	declare @c_maxDelegates int;
	declare @c_spaces int;
	declare @c_classesStartDate datetime;
	declare @c_classesClassID int;
	declare @count int;
	declare @c_country int;
 
	declare CClasses cursor fast_forward for
		select cb.classID as tableClassBookingClassID, count(cb.classID) as bookedCount,
			cl.maxDelegates,
			cl.maxDelegates - count(cb.classID) as spaces, cl.startDate,
			cl.classID as tableClassesClassID,
			cl.country
			from dbo.trn_classBookings cb
			right outer join dbo.trn_classes cl on cb.classID = cl.classID
			where cl.classStatus = 1
			and cl.startDate >= getdate()
			and dateadd(day, +14, getdate()) > cl.startDate
			group by cb.classID, cl.maxDelegates, cl.startDate, cl.classID, cl.country
			having cl.maxDelegates - count(cb.classID) > 0
 
	open CClasses
 
	fetch next from CClasses into @c_classBookingClassID, @c_bookedCount, @c_maxDelegates,
		@c_spaces, @c_classesStartDate, @c_classesClassID, @c_country
 
	while @@fetch_status = 0
	begin
		declare @d_roleUserID int;
		declare @d_emailAddress varchar(50);
		declare @d_firstName varchar(50);
		declare @d_familyName varchar(50);
		declare @d_fullName varchar(100);
		declare @d_fullDetails varchar(150);
		declare @d_dealerID int;
		declare @d_dealerName varchar(60);
 
		declare CDealers cursor fast_forward for
			select ur.UserID,
				mu.emailAddress, mu.firstName, mu.familyName,
				mu.firstName + ' ' + mu.familyName as fullName,
				mu.firstName + ' ' + mu.familyName + ' (' + mu.emailAddress + ')' as fullDetails,
				mu.dealerID,
				dl.dealerName
				from dbo.usr_usersInRoles ur
				inner join dbo.usr_masterUsers mu on ur.UserID = mu.profileID
				left outer join dbo.usr_dealers dl on mu.dealerID = dl.dealerID
				where ur.RoleID = 4
				and mu.status = 1
		open CDealers
	
		fetch next from CDealers into @d_roleUserID, @d_emailAddress, @d_firstName,
			@d_familyName, @d_fullName, @d_fullDetails, @d_dealerID, @d_dealerName
 
		while @@fetch_status = 0
		begin
			declare @count2 int;
		
			select @count2 = count(*)
				from dbo.sys_dealerNotifications
				where ClassID = @c_classesClassID
				and DealerID = @d_dealerID
 
			if @count2 > 0
			begin
				declare @lastNotificationDate datetime;
				declare @notificationCount int;
 
				select @lastNotificationDate = LastNotificationDate,
					@notificationCount = NotificationCount
					from dbo.sys_dealerNotifications
					where ClassID = @c_classesClassID
					and DealerID = @d_dealerID
 
				if dateadd(day, +3, @lastNotificationDate) > getdate() and
					@notificationCount <= 3
				begin
					Declare @emailBody Varchar(Max)
					Declare @class_name varchar(100)
					Declare @location_name varchar(75)
					declare @country_name varchar(75)
					declare @courseID int;
					declare @locationID int;
					declare @countryID int;
					declare @startDate datetime;
					declare @endDate datetime;
					declare @startTime varchar(5);
					declare @endTime varchar(5);
 
					select @courseID = courseID, 
						@locationID = location,
						@countryID = country,
						@startDate = startDate,
						@endDate = endDate,
						@startTime = startTime,
						@endTime = endTime			
						from dbo.trn_classes
						where classID = @c_classesClassID
 
					Set @class_name = (select courseName from dbo.trn_coursesMaster
						where courseID = @CourseID)
						
					set @location_name = (select DepotName from dbo.usr_depots 
						where depotid = @locationID)
 
					set @country_name = (select countryName from dbo.usr_countries 
						where id = @countryID)
					
					Set @emailBody = '<h3>Dealer Alert: Course Spaces Available!</h3>' +
						'<p>Dear ' + @d_dealerName + ',</p>' +
						'<p>An upcoming class has spaces still available.' +
						'The class details are:</p>' +
						'<p>Course: ' + @class_name +
						'<br>Start Date: ' + cast(@startDate as varchar(20)) +
						'<br>End Date: ' + cast(@endDate as varchar(20)) +
						'<br>Location: ' + @location_name +
						'<br>Country: ' + @country_name + '</p>' +
						'<p>Please login to your dealership administration area to ' + 
						'take up these spaces. ' +
						'<p>Kind Regards,</p><p><b>The JCB Training Web Team.</b></p>'
 
					if @d_emailAddress <> ''
					begin
						Exec msdb.dbo.sp_send_dbmail
							@profile_name = 'JCBTrainingEmailProfile',
							@body_format = 'HTML',
							@recipients = @d_emailAddress,
							@body = @emailBody,
							@subject = 'Dealer Alert: Course Spaces Available!'
					
						-- Log dealer email being sent
						Exec dbo.sp_logEntry 'Further Dealer Alert: Class Spaces', 
							@d_dealerName, @class_name, @startDate
					end
		
					-- Update our notifications database
					update dbo.sys_dealerNotifications set
						LastNotificationDate = getdate(),
						NotificationCount = (NotificationCount + 1)
						where ClassID = @c_classesClassID
						and DealerID = @d_dealerID 
				end	
			end
			else
			begin
				-- No dealer notification record found, lets notify them and record it
				select @courseID = courseID, 
					@locationID = location,
					@countryID = country,
					@startDate = startDate,
					@endDate = endDate,
					@startTime = startTime,
					@endTime = endTime			
					from dbo.trn_classes
					where classID = @c_classesClassID
 
				Set @class_name = (select courseName from dbo.trn_coursesMaster
					where courseID = @CourseID)
					
				set @location_name = (select DepotName from dbo.usr_depots 
					where depotid = @locationID)
 
				set @country_name = (select countryName from dbo.usr_countries 
					where id = @countryID)
					
				Set @emailBody = '<h3>Dealer Alert: Course Spaces Available!</h3>' +
					'<p>Dear ' + @d_dealerName + ',</p>' +
					'<p>An upcoming class has spaces still available.' +
					'The class details are:</p>' +
					'<p>Course: ' + @class_name +
					'<br>Start Date: ' + cast(@startDate as varchar(20)) +
					'<br>End Date: ' + cast(@endDate as varchar(20)) +
					'<br>Location: ' + @location_name +
					'<br>Country: ' + @country_name + '</p>' +
					'<p>Please login to your dealership administration area to ' + 
					'take up these spaces. ' +
					'<p>Kind Regards,</p><p><b>The JCB Training Web Team.</b></p>'
 
				if @d_emailAddress <> ''
				begin
					Exec msdb.dbo.sp_send_dbmail
						@profile_name = 'JCBTrainingEmailProfile',
						@body_format = 'HTML',
						@recipients = @d_emailAddress,
						@body = @emailBody,
						@subject = 'Dealer Alert: Course Spaces Available!'
				
					-- Log dealer email being sent
					Exec dbo.sp_logEntry 'Dealer Alert: Class Spaces', 
						@d_dealerName, @class_name, @startDate
 
					-- Insert the new notification into the notifications table
					insert into dbo.sys_dealerNotifications (
						ClassID, DealerID, LastNotificationDate, NotificationCount) values (
						@c_classesClassID, @d_dealerID, getdate(), 1)
				end
			end
			-- Get the next record for our cursor
			fetch next from CDealers into @d_roleUserID, @d_emailAddress, @d_firstName,
				@d_familyName, @d_fullName, @d_fullDetails, @d_dealerID, @d_dealerName
		end
		-- Get next record for our cursor
		fetch next from CClasses into @c_classBookingClassID, @c_bookedCount, @c_maxDelegates,
			@c_spaces, @c_classesStartDate, @c_classesClassID, @c_country
	end
 
	close CDealers;
	close CClasses;	
	
	deallocate CDealers;
	deallocate CClasses;

Open in new window

0
Comment
Question by:G0ggy
  • 4
  • 3
7 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 22597882
While in your loop, you are declaring and opening the cursor cdealers.
If you are going to declare it in the loop and open it in the loop, you will also need to close and deallocate it in the loop.

Otherwise, you are trying to declare and open the cursor more than once thus the error saying it already exists.
0
 
LVL 11

Author Comment

by:G0ggy
ID: 22604539
A bit of shifting around, however the procedure is not send emails, but only for one class, although I know if I run the query for the class selection cursor it returns 20+:

declare CClasses cursor fast_forward for
      select cb.classID as tableClassBookingClassID, count(cb.classID) as bookedCount,
            cl.maxDelegates,
            cl.maxDelegates - count(cb.classID) as spaces, cl.startDate,
            cl.classID as tableClassesClassID,
            cl.country
            from dbo.trn_classBookings cb
            right outer join dbo.trn_classes cl on cb.classID = cl.classID
            where cl.classStatus = 1
            and cl.startDate >= getdate()
            and dateadd(day, +14, getdate()) > cl.startDate
            group by cb.classID, cl.maxDelegates, cl.startDate, cl.classID, cl.country
            having cl.maxDelegates - count(cb.classID) > 0

Can you see why?
PROCEDURE [dbo].[sp_dealerClassNotifications]
	-- Add the parameters for the stored procedure here
AS
 
SET NOCOUNT ON;
 
-- Parameters to hold values for classes cursor
declare @c_classBookingClassID int;
declare @c_bookedCount int;
declare @c_maxDelegates int;
declare @c_spaces int;
declare @c_classesStartDate datetime;
declare @c_classesClassID int;
declare @count int;
declare @c_country int;
 
-- Parameters for dealership cursor
declare @d_roleUserID int;
declare @d_emailAddress varchar(50);
declare @d_firstName varchar(50);
declare @d_familyName varchar(50);
declare @d_fullName varchar(100);
declare @d_fullDetails varchar(150);
declare @d_dealerID int;
declare @d_dealerName varchar(60);
 
-- The Classes cursor itself
declare CClasses cursor fast_forward for
	select cb.classID as tableClassBookingClassID, count(cb.classID) as bookedCount,
		cl.maxDelegates,
		cl.maxDelegates - count(cb.classID) as spaces, cl.startDate,
		cl.classID as tableClassesClassID,
		cl.country
		from dbo.trn_classBookings cb
		right outer join dbo.trn_classes cl on cb.classID = cl.classID
		where cl.classStatus = 1
		and cl.startDate >= getdate()
		and dateadd(day, +14, getdate()) > cl.startDate
		group by cb.classID, cl.maxDelegates, cl.startDate, cl.classID, cl.country
		having cl.maxDelegates - count(cb.classID) > 0
 
-- Our dealership cursor
declare CDealers cursor fast_forward for
	select ur.UserID,
		mu.emailAddress, mu.firstName, mu.familyName,
		mu.firstName + ' ' + mu.familyName as fullName,
		mu.firstName + ' ' + mu.familyName + ' (' + mu.emailAddress + ')' as fullDetails,
		mu.dealerID,
		dl.dealerName
		from dbo.usr_usersInRoles ur
		inner join dbo.usr_masterUsers mu on ur.UserID = mu.profileID
		left outer join dbo.usr_dealers dl on mu.dealerID = dl.dealerID
		where ur.RoleID = 4
		and mu.status = 1
 
open CClasses
open CDealers
 
fetch next from CClasses into @c_classBookingClassID, @c_bookedCount, @c_maxDelegates,
	@c_spaces, @c_classesStartDate, @c_classesClassID, @c_country
 
while @@fetch_status = 0
begin
	fetch next from CDealers into @d_roleUserID, @d_emailAddress, @d_firstName,
		@d_familyName, @d_fullName, @d_fullDetails, @d_dealerID, @d_dealerName
 
	while @@fetch_status = 0
	begin
		declare @count2 int;
		
		select @count2 = count(*)
			from dbo.sys_dealerNotifications
			where ClassID = @c_classesClassID
			and DealerID = @d_dealerID
 
		if @count2 > 0
		begin
			declare @lastNotificationDate datetime;
			declare @notificationCount int;
 
			select @lastNotificationDate = LastNotificationDate,
				@notificationCount = NotificationCount
				from dbo.sys_dealerNotifications
				where ClassID = @c_classesClassID
				and DealerID = @d_dealerID
 
			if dateadd(day, +3, @lastNotificationDate) > getdate() and
				@notificationCount <= 3
			begin
				Declare @emailBody Varchar(Max)
				Declare @class_name varchar(100)
				Declare @location_name varchar(75)
				declare @country_name varchar(75)
				declare @courseID int;
				declare @locationID int;
				declare @countryID int;
				declare @startDate datetime;
				declare @endDate datetime;
				declare @startTime varchar(5);
				declare @endTime varchar(5);
 
				select @courseID = courseID, 
					@locationID = location,
					@countryID = country,
					@startDate = startDate,
					@endDate = endDate,
					@startTime = startTime,
					@endTime = endTime			
					from dbo.trn_classes
					where classID = @c_classesClassID
 
				Set @class_name = (select courseName from dbo.trn_coursesMaster
					where courseID = @CourseID)
					
				set @location_name = (select DepotName from dbo.usr_depots 
					where depotid = @locationID)
 
				set @country_name = (select countryName from dbo.usr_countries 
					where id = @countryID)
					
				Set @emailBody = '<h3>Dealer Alert: Course Spaces Available!</h3>' +
					'<p>Dear ' + @d_dealerName + ',</p>' +
					'<p>An upcoming class has spaces still available.' +
					'The class details are:</p>' +
					'<p>Course: ' + @class_name +
					'<br>Start Date: ' + cast(@startDate as varchar(20)) +
					'<br>End Date: ' + cast(@endDate as varchar(20)) +
					'<br>Location: ' + @location_name +
					'<br>Country: ' + @country_name + '</p>' +
					'<p>Please login to your dealership administration area to ' + 
					'take up these spaces. ' +
					'<p>Kind Regards,</p><p><b>The JCB Training Web Team.</b></p>'
 
				if @d_emailAddress <> ''
				begin
					Exec msdb.dbo.sp_send_dbmail
						@profile_name = 'JCBTrainingEmailProfile',
						@body_format = 'HTML',
						@recipients = @d_emailAddress,
						@body = @emailBody,
						@subject = 'Dealer Alert: Course Spaces Available!'
				
					-- Log dealer email being sent
					Exec dbo.sp_logEntry 'Further Dealer Alert: Class Spaces', 
						@d_dealerName, @class_name, @startDate
				end
		
				-- Update our notifications database
				update dbo.sys_dealerNotifications set
					LastNotificationDate = getdate(),
					NotificationCount = (NotificationCount + 1)
					where ClassID = @c_classesClassID
					and DealerID = @d_dealerID 
			end	
		end
		else
		begin
			-- No dealer notification record found, lets notify them and record it
			select @courseID = courseID, 
				@locationID = location,
				@countryID = country,
				@startDate = startDate,
				@endDate = endDate,
				@startTime = startTime,
				@endTime = endTime			
				from dbo.trn_classes
				where classID = @c_classesClassID
 
			Set @class_name = (select courseName from dbo.trn_coursesMaster
				where courseID = @CourseID)
				
			set @location_name = (select DepotName from dbo.usr_depots 
				where depotid = @locationID)
 
			set @country_name = (select countryName from dbo.usr_countries 
				where id = @countryID)
				
			Set @emailBody = '<h3>Dealer Alert: Course Spaces Available!</h3>' +
				'<p>Dear ' + @d_dealerName + ',</p>' +
				'<p>An upcoming class has spaces still available.' +
				'The class details are:</p>' +
				'<p>Course: ' + @class_name +
				'<br>Start Date: ' + cast(@startDate as varchar(20)) +
				'<br>End Date: ' + cast(@endDate as varchar(20)) +
				'<br>Location: ' + @location_name +
				'<br>Country: ' + @country_name + '</p>' +
				'<p>Please login to your dealership administration area to ' + 
				'take up these spaces. ' +
				'<p>Kind Regards,</p><p><b>The JCB Training Web Team.</b></p>'
 
			if @d_emailAddress <> ''
			begin
				Exec msdb.dbo.sp_send_dbmail
					@profile_name = 'JCBTrainingEmailProfile',
					@body_format = 'HTML',
					@recipients = @d_emailAddress,
					@body = @emailBody,
					@subject = 'Dealer Alert: Course Spaces Available!'
				
				-- Log dealer email being sent
				Exec dbo.sp_logEntry 'Dealer Alert: Class Spaces', 
					@d_dealerName, @class_name, @startDate
 
				-- Insert the new notification into the notifications table
				insert into dbo.sys_dealerNotifications (
					ClassID, DealerID, LastNotificationDate, NotificationCount) values (
					@c_classesClassID, @d_dealerID, getdate(), 1)
			end
		end
		-- Get the next record for our cursor
		fetch next from CDealers into @d_roleUserID, @d_emailAddress, @d_firstName,
			@d_familyName, @d_fullName, @d_fullDetails, @d_dealerID, @d_dealerName
	end
	-- Get next record for our cursor
	fetch next from CClasses into @c_classBookingClassID, @c_bookedCount, @c_maxDelegates,
		@c_spaces, @c_classesStartDate, @c_classesClassID, @c_country
end
 
close CDealers;
close CClasses;	
 
deallocate CDealers;
deallocate CClasses;

Open in new window

0
 
LVL 11

Author Comment

by:G0ggy
ID: 22604547
Sorry it is meant to say "the procedure is sending emails, but only for one class" not "the procedure is not send emails, but only for one class"
0
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.

 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 22610154
Sounds like a new question, did my reply answer your first question?
0
 
LVL 11

Author Comment

by:G0ggy
ID: 22612250
Sort of, the problem is that the procedure is not sending out emails about all classes within 14 days to all dealerships. In answer to your post, you have got me a little further down the road, but it is not working correctly.
0
 
LVL 17

Accepted Solution

by:
Daniel Reynolds earned 500 total points
ID: 22615390
Ok, I believe if you open and deallocate the dealer cursor within the first while statement it will get you your results.

You see, as it is, you run thru all 20 records in the inner cursor(dealers) for the first class and then there are no more records for the remaining classes.

So


while @@fetch_status = 0
begin
         open CDealers
	fetch next from CDealers into @d_roleUserID, @d_emailAddress, @d_firstName,
		@d_familyName, @d_fullName, @d_fullDetails, @d_dealerID, @d_dealerName
 
	while @@fetch_status = 0
....
 
		end
		-- Get the next record for our cursor
		fetch next from CDealers into @d_roleUserID, @d_emailAddress, @d_firstName,
			@d_familyName, @d_fullName, @d_fullDetails, @d_dealerID, @d_dealerName
	end
         close CDealers; --<<-- close it here It will reopen above on the next class
	-- Get next record for our cursor
	fetch next from CClasses into @c_classBookingClassID, @c_bookedCount, @c_maxDelegates,
		@c_spaces, @c_classesStartDate, @c_classesClassID, @c_country
end
 
 
close CClasses;	
 

Open in new window

0
 
LVL 11

Author Closing Comment

by:G0ggy
ID: 31501221
Thanks.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

777 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