Solved

Cursor problem

Posted on 2008-09-29
7
308 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:xDJR1875
Comment Utility
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
Comment Utility
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
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 17

Expert Comment

by:xDJR1875
Comment Utility
Sounds like a new question, did my reply answer your first question?
0
 
LVL 11

Author Comment

by:G0ggy
Comment Utility
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:
xDJR1875 earned 500 total points
Comment Utility
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
Comment Utility
Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore 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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

12 Experts available now in Live!

Get 1:1 Help Now