Updating cursor conditional formatting not updating automatically
Do we care if someone tries to update them at the same time ?Also, if the result that was to be achieved with a combination of SELECT FOR UPDATE and DELETE CURRENT OF statement could be done in a single delete statement (with a sub-query) can it be error-prone in a multi-user env ?Thanks Syn Transaction isolation refers to the mechanism that SQL Server uses to isolate different transactions from each other.
On this occasion I want to update a field in the temporary table, yet I can't figure out how to do it.The words CURRENT OF, followed by the cursor name, refer to the last row that was retrieved through the cursor with the FETCH command. The set-at-a-time update to which you referred is called a searched update.Searched updates give you a lot of bang for the buck, but positioned updates are needed when the update logic is complex.The table I'm selecting data from and then trying to update is on another server and I think this whole little process is going a little over my head.Here's a brief idea of whats happening: CREATE PROCEDURE Call Log Send Email AS DECLARE @email Rcpts varchar(50) DECLARE @email Msg varchar(255) DECLARE @data varchar(255) DECLARE @log ID int DECLARE @notification int DECLARE @email Sent Result int SET @email Rcpts = '[email protected]' DECLARE x Log Cursor CURSOR FOR SELECT ID, Data, Notification from [SERVER1]. Call Log WHERE Notification = 0 OPEN x Log Cursor FETCH NEXT FROM x Log Cursor INTO @log ID, @data, @notification WHILE @@FETCH_STATUS = 0 BEGIN SELECT @email Msg = 'Some extra text and string manipulatio on the data' @data exec @email Sent Result=SERVER2.master..xp_sendmail @[email protected] Msg, @subject='test', @recipients = @email Rcpts IF @email Sent Result = 1 BEGIN UPDATE [SERVER1]. Call Log SET Notification = 1 WHERE CURRENT OF x Log Cursor END FETCH NEXT FROM x Log Cursor INTO @log ID, @data, @notification END COMMIT CLOSE x Log Cursor DEALLOCATE x Log Cursor GONow, I've tried creating a transaction with the SET CURSOR_CLOSE_ON_COMMIT OFF command set, then encapsulate the update with a BEGIN TRAN and COMMIT, but it doesn't seem to do anything, if I do the TRAN outside the cursor, everything seems to go horribly wrong.