challenging cursor issues

I have three tables. secureToken, secureTokenExpiring and secureTokenLog.

The system needs to scan all records in the secureToken table for expiring DateDeaths that are less than 45 deays away. Then load all those records into the secureTokenExpiring  table to be processed by the cursor to assign new (available serialNBRs from the SecureToken table.
This is working!!!!

Stage two is where the trouble is. I had it setup to process records and it was working but I needed to put a STOP in the cursor or procedure if there were NO more available serialNBRs from the token table.
Now I get this error:


(0 row(s) affected)

(22 row(s) affected)

(22 row(s) affected)
Msg 16915, Level 16, State 1, Line 72
A cursor with the name 'CursorTemplate' already exists.
Msg 16916, Level 16, State 1, Line 142
A cursor with the name 'CusrsorTemplate' does not exist.

I have comments in the code explaining what I am trying to do and what needs to be done.

PLEASE HELP,
Thanks



declare @sql nvarchar(2000)
declare @warningdate date
declare @expiresdate date
declare @serialNBR nvarchar(20)
declare @todaysDate date
declare @StopWhile bit


--set the warning date variable. This says if its less than 45 days away
set @todaysDate = GETDATE()
SET @warningdate =   getdate() +45
--SELECT @warningdate
--End set warning date

--Insert expiring records into expiring table
INSERT  secureTokenExpiring (username, DateDeath,assignedTo, serialNBR,dateAssigned)
Select NameReg, DateDeath ,usedby34,serialNBR,DateAssigned
From
(
SELECT  m.NameReg,
        (SELECT TOP 1
                dbo.secureToken.[DateDeath]
        FROM    dbo.secureToken
        WHERE   usedBy34 = s.usedBy34
        ORDER BY dbo.secureToken.[DateDeath] DESC
                   ) DateDeath, processing,usedBy34,serialNBR,DateAssigned
FROM    AssetManagement.dbo.secureToken s
        INNER JOIN dbo.Users_Master m ON s.usedBy34 = m.user34
) DT
WHERE   DateDeath < @warningdate and [processing] is null
--End insert 

----set flag on SecurToken table
update A
Set A.[processing] = @todaysDate
from secureToken A inner join dbo.secureTokenExpiring B on A.serialNBR = B.serialNBR
----End Flag

---insert email addresses into temp table
UPDATE  A
SET A.email = B.emailaddress ,A.assignedTo = B.user34--,A.serialNBR = C.serialNBR
FROM dbo.secureTokenExpiring A INNER JOIN Users_Master B ON A.username = B.NameReg
-----End email address update




---This is where the cursor will go to run through all the records of expiring
-- tokens table and assign new tokens.
--Need to build in a stop of cursor if there are no more avaliable records.
--  then send email to order new tokens if there are more expiring tokens than records available.





------STAGE TWO
--this section is getting the user that needs to be changed in the active token table
declare @nextUserdate date
declare @expiringSerial varchar(50)
declare @user nchar(7)
declare @firstAssignedDate date

--used with cursor
Declare CursorTemplate Cursor
Fast_forward For

Select min(DateDeath),serialNBR
from secureTokenExpiring
group by serialNBR,datedeath order by dateDeath

Open Cursortemplate
Fetch Next From CursorTemplate
into @nextUserdate,@expiringSerial

while (@@FETCH_STATUS = 0 and @StopWhile = 0)
Begin
-----
--set @nextUserdate = (select MIN(datedeath) from secureTokenExpiring)

 --set @expiringSerial =  (select top 1 serialNBR from dbo.secureTokenExpiring
 --where dateDeath = @nextUserdate)
 
 set @user = (select assignedTo from dbo.secureTokenExpiring
 where serialNBR = @expiringSerial)
 
 Set @firstAssignedDate = (select dateAssigned from dbo.secureTokenExpiring
 where serialNBR = @expiringSerial)

print @nextUserdate
print @expiringSerial
print @user

---this section will get the next available serialNBR to use for expiring token
--to assign to the above user @user
declare @maxdate date
declare @avaSerialNBR int

set @maxdate = (select MAX(datedeath) from secureToken where usedby34 is null)


 set @avaSerialNBR =  (select top 1 serialNBR from dbo.secureToken
where usedBy34 is null and DateDeath = @maxdate)

print @avaSerialNBR
If @avaSerialNBR is  null
begin
set @StopWhile = 1
 
End



--assigning the user to the next serialnbr token 

Update dbo.secureToken
set usedBy34 = @user,DateDeath = @maxdate,DateAssigned = GETDATE()
where serialNBR = @avaSerialNBR

--remove the old token card and record.
delete from dbo.secureToken
where serialNBR = @expiringSerial
--


 --Insert a record into the token Log
insert into dbo.secureTokenLog (secureTokenID,Usedby34,dateFirstAssigned,dateLastUsed,reason)
values (@expiringSerial,@user,@maxdate,@firstAssignedDate,'token expired')
--End log file
 

 Fetch next from cursorTemplate
 into @nextUserdate,@expiringSerial

 


 End
 Close CursorTemplate
 Deallocate CusrsorTemplate

Open in new window

dboyd02Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
Maybe you created the cursor once without deallocating it? Have you tried doing a deallocation just by itself--like a "reset"?
0
dboyd02Author Commented:
I ran this:
 Close CursorTemplate
 Deallocate CusrsorTemplate

and got these results:
Msg 16917, Level 16, State 1, Line 1
Cursor is not open.
Msg 16916, Level 16, State 1, Line 2
A cursor with the name 'CusrsorTemplate' does not exist.
0
ralmadaCommented:
1) you have a syntax error there.
2) You should try to avoid using cursors.
3) Try this version with some changes

--....


------STAGE TWO
--this section is getting the user that needs to be changed in the active token table
declare @nextUserdate date
declare @expiringSerial varchar(50)
declare @user nchar(7)
declare @firstAssignedDate date

--used with cursor
Declare CursorTemplate Cursor
For 
Select min(DateDeath),serialNBR 
from secureTokenExpiring 
group by serialNBR order by 1

Open Cursortemplate

Fetch Next From CursorTemplate
into @nextUserdate,@expiringSerial

while (@@FETCH_STATUS = 0 and @StopWhile = 0)
Begin
 
	set @user = (select assignedTo from dbo.secureTokenExpiring where serialNBR = @expiringSerial)
 
	Set @firstAssignedDate = (select dateAssigned from dbo.secureTokenExpiring where serialNBR = @expiringSerial)

	print @nextUserdate
	print @expiringSerial
	print @user

	declare @maxdate date
	declare @avaSerialNBR int

	set @maxdate = (select MAX(datedeath) from secureToken where usedby34 is null)

	set @avaSerialNBR =  (select top 1 serialNBR from dbo.secureToken where usedBy34 is null and DateDeath = @maxdate)

	print @avaSerialNBR
	If @avaSerialNBR is  null
	begin
		set @StopWhile = 1
 
	End

	--assigning the user to the next serialnbr token 

	Update dbo.secureToken
	set usedBy34 = @user,DateDeath = @maxdate,DateAssigned = GETDATE()
	where serialNBR = @avaSerialNBR

	--remove the old token card and record.
	delete from dbo.secureToken
	where serialNBR = @expiringSerial
	--


	--Insert a record into the token Log
	insert into dbo.secureTokenLog (secureTokenID,Usedby34,dateFirstAssigned,dateLastUsed,reason)
	values (@expiringSerial,@user,@maxdate,@firstAssignedDate,'token expired')
	--End log file
 

	Fetch next from cursorTemplate
	into @nextUserdate,@expiringSerial

End

Close CursorTemplate
Deallocate CursorTemplate

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dboyd02Author Commented:
worked great thank
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.