Solved

Cursor problem

Posted on 2008-09-29
7
310 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 17

Expert Comment

by:xDJR1875
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:
xDJR1875 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

15 Experts available now in Live!

Get 1:1 Help Now