October 28, 2008 at 9:43 am
Hi there
I'm having a wee bit of a problem. I've declared a cursor and have selected multiple rows from a DB to get some data, then do some manipulation on that data to send a email. Once thats done, I'm trying to update the table again and set the row as processed.
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 CallLogSendEmail
AS
DECLARE @emailRcpts varchar(50)
DECLARE @emailMsg varchar(255)
DECLARE @data varchar(255)
DECLARE@logID int
DECLARE @notification int
DECLARE @emailSentResult int
SET @emailRcpts = 'bob@accounting.com'
DECLARE xLogCursor CURSOR FOR
SELECT ID, Data, Notification
from [SERVER1].DB.dbo.CallLog
WHERE Notification = 0
OPEN xLogCursor
FETCH NEXT FROM xLogCursor
INTO @logID, @data, @notification
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @emailMsg = 'Some extra text and string manipulatio on the data' + @data
exec @emailSentResult=SERVER2.master..xp_sendmail @message=@emailMsg, @subject='test', @recipients = @emailRcpts
IF @emailSentResult = 1
BEGIN
UPDATE [SERVER1].DB.dbo.CallLog
SET Notification = 1
WHERE CURRENT OF xLogCursor
END
FETCH NEXT FROM xLogCursor
INTO @logID, @data, @notification
END
COMMIT
CLOSE xLogCursor
DEALLOCATE xLogCursor
GO
The error I get is this:
Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.
Now, 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. I've also read up the help files on REPEATABLE_READ and SERIALIZABLE and it seems to mostly be going over my head.
Any help or shove in the right direction would be greatly appreciated.
Thanks
Syn
October 28, 2008 at 9:53 am
Never mind.
Did a FOR READ ONLY on the select statement and then did a normal update on the table with the ID.
If there is a beter way of doing this, I'd still like to know.
Thanks
Syn
October 28, 2008 at 9:56 am
Have you tried setting the transaction isolation level?
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Do this inside the body of the stored proc, before you declare the cursor.
October 28, 2008 at 10:07 am
SQLZ (10/28/2008)
Have you tried setting the transaction isolation level?
SET TRANSACTION ISOLATION LEVEL SERIALIZABLEDo this inside the body of the stored proc, before you declare the cursor.
I did read that section in the help file, but it simple didn't make much sense to me 🙁
October 28, 2008 at 10:20 am
Garth Michel (10/28/2008)
SQLZ (10/28/2008)
Have you tried setting the transaction isolation level?
SET TRANSACTION ISOLATION LEVEL SERIALIZABLEDo this inside the body of the stored proc, before you declare the cursor.
I did read that section in the help file, but it simple didn't make much sense to me 🙁
Transaction isolation refers to the mechanism that SQL Server uses to isolate different transactions from each other. For example, if I'm updating data in a table then SQL Server can isolate other transactions from viewing or updating that data.
There are four isolation levels (we'll ignore snapshot isolation), each with a varying degree of isolation.
read uncomitted offers virtually no isolation. So if you're updating a table, I can happily come along and read that data even though you might have not finished updating the data in that table.
In read committed (the default isolation level) if I'm reading data from a table, that data is locked until I've finished reading that data (i.e. until my select statement completes). Also, if you're updating data no one else can read the data until you're done with updating it.
In repeatable read if I'm reading data from the table, the data is locked until the end of the transaction. Other transaction can still insert data.
Serializable offers the most isolation, where data is completely locked until the end of the transaction.
Hope that helps.
October 28, 2008 at 10:57 am
Isn't me setting the SELECT to READ ONLY pretty much the same as setting the mode to repeatable read and would this mode be sufficient for what I want to do?
Syn
October 28, 2008 at 1:29 pm
Garth Michel (10/28/2008)
Isn't me setting the SELECT to READ ONLY pretty much the same as setting the mode to repeatable read and would this mode be sufficient for what I want to do?Syn
Unfortunately it's not the same. Imagine this:
You run the select against CallLog for the cursor and 5 seconds later you perform the update statement on CallLog. How do you know that in those 5 seconds the data in the table CallLog has not changed because another transaction modified the data?
The answer is that you can't know for sure.
By running in repeatable read or in serializable you are ensuring that the table CallLog cannot be modified by another transaction until your transaction is finished.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply