September 14, 2005 at 3:34 am
Does anybody know if Distributed Transactions can be used against an MS Access database in SQL 7.0
I have a Link server and a SQL script which I want to fetch through a cursor based on a table in an MS access linked server. I am then performing some updates on some SQL tables and updating the records on Access...But inside a transaction. When I run the followng code however I get
Server: Msg 8524, Level 16, State 1, Line 168
The current transaction could not be exported to the remote provider. It has been rolled back.
MSDTC is running
Code
BEGIN TRANSACTION
DECLARE curMembers CURSOR FOR
SELECT [ID],
Name,.....etc
FROM [Access Linked Server]...[PersonalDetails]
OPEN curMembers
FETCH NEXT FROM curMembers INTO @id, @name, ...etc
WHILE @@FETCH_STATUS = 0
BEGIN
-- Conditional logic updating some SQL tables and populating a field @reg_no
UPDATE [Access Linked Server]...[PersonalDetails]
SET reg_no= @reg_no
FROM [Access Linked Server]...[PersonalDetails] MEM WHERE MEM.[id] = @id
FETCH NEXT FROM curMembers INTO @id, @name, ...etc
END
ROLLBACK
September 14, 2005 at 11:50 pm
1st the syntax is BEGIN DISTRIBUTED TRAN [SACTION] not BEGIN TRAN [SACTION]
I suggest that you move this just before your loop:
DECLARE @Err int
DECLARE curMembers CURSOR FOR
SELECT [ID], Name,.....etc
FROM [Access Linked Server]...[PersonalDetails]
OPEN curMembers
FETCH NEXT FROM curMembers INTO @id, @name, ...etc
IF @@FETCH_STATUS = 0
BEGIN DISTRIBUTED TRAN
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE [Access Linked Server]...[PersonalDetails]
SET reg_no= @reg_no
FROM [Access Linked Server]...[PersonalDetails] MEM WHERE MEM.[id] = @id
SET @Err = @@ERROR
IF @Err = 0
FETCH NEXT FROM curMembers INTO @id, @name, ...etc
ELSE
BREAK
END
IF @Err = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
Andy
September 15, 2005 at 3:31 am
Thanks David
September 15, 2005 at 3:53 am
Nope spoke too soon, still getting the same Error
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply