Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cursor problem

Posted on 2008-09-29
7
Medium Priority
?
317 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
[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
  • 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

722 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