January 19, 2009 at 4:24 am
hi experts
i have the following code and when i'm running it i'm getting the error "A cursor with the name 'lMCursor' does not exist" please help:
DECLARE
@liDepLinksIDdm_ID,
@liTransIDdm_ID,
@liMaxMaxIDdm_ID,
@liNextIDdm_ID,
@aiTransIDdm_ID,
@aiDepLinksIDdm_ID
SELECT @liNextID = NextID from stdTables where TableName = 'astDepreciationTransactions'
DECLARE lMCursor CURSOR LOCAL FOR
SELECT MAX(TRANSACTIONID) FROM UDMLastTransaction
OPEN lMCursor
FETCH NEXT FROM lMCursor INTO @liMaxMaxID
WHILE @@fetch_status = 0
BEGIN
IF @liMaxMaxID < @liNextID - 1
BEGIN
SET @liMaxMaxID = @liMaxMaxID + 1
UPDATE UDMLastTransaction set TransactionID = @liMaxMaxID
DECLARE lcursor CURSOR LOCAL FOR
SELECT ID, DepreciationLinksID FROM astDepreciationTransactions
WHERE ID = @liMaxMaxID
OPEN lcursor
WHILE 0 = 0
BEGIN
FETCH NEXT FROM lcursor INTO @liTransID,@liDepLinksID
IF @@fetch_status <> 0
BEGIN
BREAK
END
EXEC pr_UDMAssetValuesReport @liTransID, @liDepLinksID
END
CLOSE lcursor
DEALLOCATE lcursor
END
CLOSE lMCursor
DEALLOCATE lMCursor
END
January 19, 2009 at 4:29 am
Which line's the error on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2009 at 4:36 am
hi Gail
thanks for responding so soon
the error is coming from this line "FETCH NEXT FROM lMCursor INTO @liMaxMaxID" and here "WHILE 0 = 0"
January 19, 2009 at 4:41 am
Following 2 lines will come after last END
CLOSE lMCursor
DEALLOCATE lMCursor
-Vikas Bindra
January 19, 2009 at 4:46 am
i don't understand
the end of the code looks like:
END
CLOSE lMCursor
DEALLOCATE lMCursor
END
January 19, 2009 at 4:49 am
One more logical error is there in the code...
The code should like...
DECLARE
@liDepLinksID dm_ID,
@liTransID dm_ID,
@liMaxMaxID dm_ID,
@liNextID dm_ID,
@aiTransID dm_ID,
@aiDepLinksID dm_ID
SELECT @liNextID = NextID from stdTables where TableName = 'astDepreciationTransactions'
DECLARE lMCursor CURSOR LOCAL FOR
SELECT MAX(TRANSACTIONID) FROM UDMLastTransaction
OPEN lMCursor
FETCH NEXT FROM lMCursor INTO @liMaxMaxID
WHILE @@fetch_status = 0
BEGIN
IF @liMaxMaxID < @liNextID - 1
BEGIN
SET @liMaxMaxID = @liMaxMaxID + 1
UPDATE UDMLastTransaction set TransactionID = @liMaxMaxID
DECLARE lcursor CURSOR LOCAL FOR
SELECT ID, DepreciationLinksID FROM astDepreciationTransactions
WHERE ID = @liMaxMaxID
OPEN lcursor
WHILE 0 = 0
BEGIN
FETCH NEXT FROM lcursor INTO @liTransID,@liDepLinksID
IF @@fetch_status <> 0
BEGIN
BREAK
END
EXEC pr_UDMAssetValuesReport @liTransID, @liDepLinksID
END
CLOSE lcursor
DEALLOCATE lcursor
END
FETCH NEXT FROM lMCursor INTO @liMaxMaxID
END
CLOSE lMCursor
DEALLOCATE lMCursor
-Vikas Bindra
January 19, 2009 at 4:56 am
i don't understand
the end of the code looks like:
END
CLOSE lMCursor
DEALLOCATE lMCursor
END
Always close and deallocate a cursor when your are done with it. In your code you have done before you are done....
Also, always indent your code properly so that it is easy to understand for others (and for you also). It also looks neat.
-Vikas Bindra
January 19, 2009 at 5:08 am
THANKS GUYS, I'M GONNA IMPLEMENT THE CODE AND SEE IF IT'S WORKS
MUCH APPRECIATED
January 19, 2009 at 5:18 am
No reason to shout 😉
Why are you using a cursor for this in the first place ?
SELECT MAX(TRANSACTIONID) FROM UDMLastTransaction
It would be better to just select it directly into a variable or join it in your next cursor.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 19, 2009 at 5:32 am
the reason is that the table "UDMLastTransaction" has only one record if this criteria " IF @liMaxMaxID < @liNextID - 1" is met, i'm setting the MaxTransactionID to "SET @liMaxMaxID = @liMaxMaxID + 1" and update the table UDMLastTransactionson with @liMaxMaxID + 1, then i select the ID from the second cursor which will be equal to the one from the first cursor and exec pr_UDMAssetValuesReport.
i have to do this until this condition " IF @liMaxMaxID < @liNextID - 1" is false
January 19, 2009 at 5:51 am
hi Vikas
thanks for the modification of the code... it's works.
just one other thing it this condition "IF @liMaxMaxID < @liNextID - 1" is false how do i rollback the transaction??
January 19, 2009 at 6:07 am
Which transaction you want to rollback?
The earlier UPDATES that you made on UDMLastTransaction when the condition was true in the loop...?
OR/AND
the processing done by the pr_UDMAssetValuesReport SP when the condition was true in the loop....?
Also, notice that
When this condition (@liMaxMaxID < @liNextID - 1) is false nothing will be updated nor your SP will be called.
I don't think you have to rollback anything.
I would suggest you to go through the processing logic once again and then come up with your exact query.
-Vikas Bindra
January 19, 2009 at 6:10 am
Also, you don't need the outer cusor:
DECLARE
@liDepLinksID dm_ID,
@liTransID dm_ID,
@liMaxMaxID dm_ID,
@liNextID dm_ID,
@aiTransID dm_ID,
@aiDepLinksID dm_ID
SELECT @liNextID = NextID from stdTables where TableName = 'astDepreciationTransactions'
--DECLARE lMCursor CURSOR LOCAL FOR
SELECT @liMaxMaxID = MAX(TRANSACTIONID) FROM UDMLastTransaction
--OPEN lMCursor
--FETCH NEXT FROM lMCursor INTO @liMaxMaxID
--WHILE @@fetch_status = 0
--BEGIN
WHILE (@liMaxMaxID < @liNextID - 1)
BEGIN
SET @liMaxMaxID = @liMaxMaxID + 1
UPDATE UDMLastTransaction set TransactionID = @liMaxMaxID
DECLARE lcursor CURSOR LOCAL FOR
SELECT ID, DepreciationLinksID FROM astDepreciationTransactions
WHERE ID = @liMaxMaxID
OPEN lcursor
WHILE 0 = 0
BEGIN
FETCH NEXT FROM lcursor INTO @liTransID,@liDepLinksID
IF @@fetch_status <> 0
BEGIN
BREAK
END
EXEC pr_UDMAssetValuesReport @liTransID, @liDepLinksID
END
CLOSE lcursor
DEALLOCATE lcursor
END
-- --FETCH NEXT FROM lMCursor INTO @liMaxMaxID
--END
--CLOSE lMCursor
--DEALLOCATE lMCursor
-Vikas Bindra
January 19, 2009 at 6:13 am
thanks, it's the update which shouldn't happen if the condition if false. if the update doesn't happen the procedure won't be executed anyways.
January 19, 2009 at 6:19 am
vikas bindra (1/19/2009)
Also, you don't need the outer cusor: ...
Right on target :exclamationmark:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply