Solved

T-SQL Error Message when Updating Data Remotely

Posted on 2001-09-05
20
1,081 Views
Last Modified: 2008-03-06
Hi experts,
I am using SQL 2000 Server.
I am doing transformation and updating of data from one server to the other server, with the Data Transformation - Local Package.

When I tried to put in the BEGIN TRANSACATION.. COMMIT TRAN and ROLLBACK TRAN... I hv the following errors msg..
What's the error msg about ? How can I resolve this error ?

Please advice. Thanks..
---------------------------------------------------------
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.

(1 row(s) affected)

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
Msg 7391, Level 16, State 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

---------------------------------------------------------


Regards,
ms3756
0
Comment
Question by:ms3756
  • 8
  • 5
  • 2
  • +4
20 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6456445
I guess that the service MSDTC is not running on neither of the servers... running this service should solve your problem...
CHeers
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 6456460
In addition to the above, you will probably have to use the:
'begin distributed transaction' statement also.
Check BOL.
Thanks.
0
 

Author Comment

by:ms3756
ID: 6456824
(1) The MSDTC service is running on both of the server
(2) I am using 'begin distributed transaction' too

The following is some of the sql statements:-
DECLARE cursor1 CURSOR
SCROLL KEYSET OPTIMISTIC FOR
SELECT
User_id, Seq_no
FROM server1.database1.dbo.table1

OPEN cursor1
FETCH FIRST FROM cursor1 INTO @User_id, @Seq_no
WHILE (@@FETCH_STATUS <> -1)
BEGIN
if (@@FETCH_STATUS <> -2)
   BEGIN
     BEGIN DISTRIBUTED TRAN
        INSERT INTO server2.database2.dbo.table2
        (User_id, Seq_no)
        SELECT User_id, Seq_no
         FROM server1.database1.dbo.table1
         WHERE Seq_no = @Seq_no AND seq_no NOT IN      (SELECT seq_no FROM server2.database2.dbo.table2
         WHERE seq_no = @seq_no)
     
     UPDATE server1.database1.dbo.table1 SET   Retrieve_Stat ='Y'
        WHERE Seq_no = @Seq_no
     if condition successful
          COMMIT TRAN
     else
          ROLLBACK TRAN
     
     FETCH NEXT FROM cursor1 INTO  @User_id, @Seq_no

CLOSE cursor1
DEALLOCATE cursor1


regs,
ms3756

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6456845
i would declare the cursor like this (omit the scroll, which is only needed when you want to fetch back or move back in the cursor):
DECLARE cursor1 CURSOR
KEYSET OPTIMISTIC FOR UPDATE FOR
...

and the update statement probably like this:
UPDATE server1.database1.dbo.table1
SET   Retrieve_Stat ='Y'
WHERE CURRENT OF cursor1

Not sure if that will help...

I would also probably change the insert statement into this:

IF NOT EXISTS (SELECT seq_no FROM server2.database2.dbo.table2
        WHERE seq_no = @seq_no)
INSERT INTO server2.database2.dbo.table2
       (User_id, Seq_no)
VALUES (@user_id, @Seq_no)

...

Cheers
0
 

Author Comment

by:ms3756
ID: 6456867
(1) The MSDTC service is running on both of the server
(2) I am using 'begin distributed transaction' too

The following is some of the sql statements:-
DECLARE cursor1 CURSOR
SCROLL KEYSET OPTIMISTIC FOR
SELECT
User_id, Seq_no
FROM server1.database1.dbo.table1

OPEN cursor1
FETCH FIRST FROM cursor1 INTO @User_id, @Seq_no
WHILE (@@FETCH_STATUS <> -1)
BEGIN
if (@@FETCH_STATUS <> -2)
   BEGIN
     BEGIN DISTRIBUTED TRAN
        INSERT INTO server2.database2.dbo.table2
        (User_id, Seq_no)
        SELECT User_id, Seq_no
         FROM server1.database1.dbo.table1
         WHERE Seq_no = @Seq_no AND seq_no NOT IN      (SELECT seq_no FROM server2.database2.dbo.table2
         WHERE seq_no = @seq_no)
     
     UPDATE server1.database1.dbo.table1 SET   Retrieve_Stat ='Y'
        WHERE Seq_no = @Seq_no
     if condition successful
          COMMIT TRAN
     else
          ROLLBACK TRAN
     
     FETCH NEXT FROM cursor1 INTO  @User_id, @Seq_no

CLOSE cursor1
DEALLOCATE cursor1


regs,
ms3756

0
 

Expert Comment

by:PhillipVest
ID: 6458433
You need to declare your cursor as follows:
DECLARE cursor1 CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
    SELECT  User_id, Seq_no
    FROM    server1.database1.dbo.table1

Really the only requirement is the READ_ONLY.  As the error statement indicates updatable cursors cannot be opened on remote tables.  This does not mean you cannot update the remote table as you have in your sp, just that the cursor needs to declared READ_ONLY.
0
 

Author Comment

by:ms3756
ID: 6459354
HI,
I had tried on PhilipVest's suggestion, and it's able to ommit the warning given by SQL
>>
(1) The following error is ommitted..
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.

I thaught the READ_ONLY CURSOR Type is for read only recordset ?  In this case, i need to update some field in the remote table, that's why I declare as optimistic.

(2) I still hv the following error when i put in the BEGIN DISTRIBUTED TRANSACTION Statement... For ur information, if I hv taken out the BEGIN DISTRIBUTED TRANSACTION statement,it's can work, but once i added in the statement. This error will come out:-

>>
OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.]
Msg 7391, Level 16, State 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.

Please advise. Thanks..

regards,
ms3756



0
 
LVL 9

Expert Comment

by:miron
ID: 6459420
have you tried to wrap the block of statements in

SET TRANSACTION ISOLATION LEVEL REPEATABLE_READ
or
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
.....
SET TRANSACTION ISOLATION LEVEL READ_COMMITTED -- back to default

Potentail that table level locking will occur, query optimizer is less awair of table rows for distributed transaction. Clustered unique index or a primary key on the table under update it might prevent table level locking.

Thanks.
0
 

Author Comment

by:ms3756
ID: 6459430
I had already tried the following 2:-

SET TRANSACTION ISOLATION LEVEL REPEATABLE_READ
or
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

but, i still get the same error msg..

regs,
ms3756
0
 

Expert Comment

by:PhillipVest
ID: 6461009
Do you have your DTC services running in a domain account with sufficient privileges?  To be honest I am not sure what sufficient is short of domain administrator which will work for sure.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

by:miron
ID: 6462709
have you tried to do it one step at a time,
starting from somethin like this
-- on server2 database2

create table T2
(
    name varchar(24)
)

-- on server1
BEGIN DISTRIBUTED TRANSACTION
insert into server2.database2.dbo.T2
( name ) values 'Celestina'
COMMIT TRANSACTION
0
 

Author Comment

by:ms3756
ID: 6473279
Hi experts,

i had tried out the very simple update as the following, and i have the following error:-

begin distributed transaction
UPDATE server2.database2.dbo.test SET Retrieve_Stat ='Y'
commit transaction

[OLE/DB provider returned message: Cannot start more transactions on this session.]
Msg 7395, Level 16, State 2
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.  
A nested transaction was required because the XACT_ABORT option was set to OFF.

Do I need to do any setting on the servers b4 I do a distributed transaction ?

Thanks.

regs,
ms3756
0
 
LVL 9

Expert Comment

by:miron
ID: 6473867
On - Line documentation says
[when]
SET XACT_ABORT is ON, SQL Server does not
require nested transaction support from the OLE DB provider. Apparently the code needs a few more lines:

SET XACT_ABORT ON
GO
begin distributed transaction
UPDATE server2.database2.dbo.test SET Retrieve_Stat ='Y'
commit transaction
GO
SET XACT_ABORT OFF
GO
0
 

Author Comment

by:ms3756
ID: 6478228
Hi,
Yeap, after i set the XACT_ABORT ON.. the following errors doesn't appear anmore with the simple update statement.

But once I try to fetch the recordset to the cursor..and do the inserting and updating.. the error msg will occurs after the BEGIN DISTRIBUTED TRAN and b4 the INSERT INTO statement..

It will work fine if without the BEGIN DISTRIBUTED TRANSACTION statement..

Please help. Thanks.

ms3756
0
 
LVL 9

Expert Comment

by:miron
ID: 6478431
ms3756,

I tried this code and it worked just as fine. Let me know where our code differs.
regs,
miron

DECLARE @x int
DECLARE cursor1 CURSOR
SCROLL KEYSET OPTIMISTIC FOR
SELECT
value
FROM server1.test.dbo.t_test

OPEN cursor1
FETCH FIRST FROM cursor1 INTO @x
WHILE (@@FETCH_STATUS <> -1)
BEGIN
     if (@@FETCH_STATUS <> -2)
     BEGIN
          SET XACT_ABORT ON
          BEGIN DISTRIBUTED TRAN
             INSERT INTO server2.test.dbo.t_test ( value ) select @x    
          if exists( select 1 from server2.test.dbo.t_test where value = @x + 1 )
               COMMIT TRAN
          else
               ROLLBACK TRAN
          SET XACT_ABORT OFF
     FETCH NEXT FROM cursor1 INTO  @x
     END
END
CLOSE      cursor1
DEALLOCATE cursor1
0
 

Author Comment

by:ms3756
ID: 6486719
i tried the same way as what u did...
the following is my code:-)

DECLARE @menu_code as char(3)
DECLARE @menucode_desc as varchar(40)

DECLARE cursor1 CURSOR
SCROLL KEYSET OPTIMISTIC FOR
SELECT
menu_code, menucode_desc
FROM fsdrnd.epintas.dbo.menucode2


OPEN cursor1
FETCH FIRST FROM cursor1 INTO @menu_code,@menucode_desc
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    if (@@FETCH_STATUS <> -2)
    BEGIN
         SET XACT_ABORT ON
         BEGIN DISTRIBUTED TRAN
      INSERT INTO FIRSTDB.dbbrains.dbo.menucode2
         (menu_code, menucode_desc)
         SELECT @menu_code, @menucode_desc
         if exists( select menu_code from FIRSTDB.dbbrains.dbo.menucode2 where @menu_code= @menu_code + 1 )
              COMMIT TRAN
         else
              ROLLBACK TRAN
         SET XACT_ABORT OFF
    FETCH NEXT FROM cursor1 INTO  @menu_code,@menucode_desc
    END
END
CLOSE      cursor1
DEALLOCATE cursor1


I hv the same error message :-)

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
Msg 7391, Level 16, State 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.


thanks for ur help... :>

ms3756
0
 
LVL 9

Accepted Solution

by:
miron earned 150 total points
ID: 6487256
Pedantic am I. This transaction will not insert even a single value because of where clause in the 'EXISTS' statement. It need to be changed as

menu_code= @menu_code + 1

instead of
@menu_code= @menu_code + 1

In other ways we are back to square one. If you don't mind, let me suggest your releaving "begin distributed transaction" from its duties, if it does not hurt.

On the other hand transactional processing is a nice thing to have  &:)

let us try one more time and combine the simple update and a cursor in one test script, by the way the reason I am optimistic is that the simple update actually worked for you. This time I would like you to pay attention to the server the command is executing from. If simple update works from one server, please switch to another. I also would like to introduce into simple update 2 servers.

-- begin SQL
-- somple update / insert
declare @id int
select @id = 0
select 'test of insert from server1 into server2'
SET XACT_ABORT ON
GO
begin distributed transaction
select @id = max(id) from server1.test_db.dbo.sysobjects
insert into server2.test_db.dbo.sysobjects ( value ) select @id
commit transaction
GO
SET XACT_ABORT OFF
GO

select 'test of insert from server2 into server1'
SET XACT_ABORT ON
GO
begin distributed transaction
select @id = max(id) from server2.test_db.dbo.sysobjects
insert into server1.test_db.dbo.sysobjects  ( value ) select @id
commit transaction
GO
SET XACT_ABORT OFF
GO


select 'test of cursor insert from server1 into server2'

DECLARE @menu_code as char(3)
DECLARE @menucode_desc as varchar(40)

DECLARE cursor1 CURSOR
SCROLL KEYSET OPTIMISTIC FOR
SELECT
menu_code, menucode_desc
FROM fsdrnd.epintas.dbo.menucode2

OPEN cursor1
FETCH FIRST FROM cursor1 INTO @menu_code,@menucode_desc
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   if (@@FETCH_STATUS <> -2)
   BEGIN
        SET XACT_ABORT ON
        BEGIN DISTRIBUTED TRAN
     INSERT INTO FIRSTDB.dbbrains.dbo.menucode2
        (menu_code, menucode_desc)
        SELECT @menu_code, @menucode_desc
        if exists( select menu_code from FIRSTDB.dbbrains.dbo.menucode2 where @menu_code= @menu_code
+ 1 )
             COMMIT TRAN
        else
             ROLLBACK TRAN
        SET XACT_ABORT OFF
   FETCH NEXT FROM cursor1 INTO  @menu_code,@menucode_desc
   END
END
CLOSE      cursor1
DEALLOCATE cursor1
GO


select 'test of cursor insert from server2 into server1'

DECLARE @menu_code as char(3)
DECLARE @menucode_desc as varchar(40)

DECLARE cursor1 CURSOR
SCROLL KEYSET OPTIMISTIC FOR
SELECT
menu_code, menucode_desc
FROM FIRSTDB.dbbrains.dbo.menucode2


OPEN cursor1
FETCH FIRST FROM cursor1 INTO @menu_code,@menucode_desc
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   if (@@FETCH_STATUS <> -2)
   BEGIN
        SET XACT_ABORT ON
        BEGIN DISTRIBUTED TRAN
     INSERT INTO fsdrnd.epintas.dbo.menucode2
        (menu_code, menucode_desc)
        SELECT @menu_code, @menucode_desc
        if exists( select menu_code from fsdrnd.epintas.dbo.menucode2 where menu_code = @menu_ code
+ 1 )
             COMMIT TRAN
        else
             ROLLBACK TRAN
        SET XACT_ABORT OFF
   FETCH NEXT FROM cursor1 INTO  @menu_code,@menucode_desc
   END
END
CLOSE      cursor1
DEALLOCATE cursor1
GO

0
 

Author Comment

by:ms3756
ID: 6540525
Hi,

I m trying to write the query with the sample database so that you can easily help on this...
the following codes still get the same error message:-(


[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
Msg 7391, Level 16, State 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

----------------------------

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

declare @discounttype as varchar(40)
declare @stor_id as char(4)
declare @lowqty as smallint
declare @highqty as smallint
declare @discount as decimal(5,2)

declare cur_pubs cursor
local read_only for
select discounttype, stor_id, lowqty, highqty, discount
from dellserver.pubs.dbo.discounts

open cur_pubs
fetch next from cur_pubs
into @discounttype, @stor_id, @lowqty, @highqty, @discount

while (@@fetch_status <> -1)

begin
   if (@@fetch_status <> -2)
     begin distributed transaction
       begin    
        delete from firstdb.pubs.dbo.discounts where   stor_id =@stor_id
        insert into firstdb.pubs.dbo.discounts
        values
        (@discounttype,@stor_id,@lowqty, @highqty, @discount)
       end
     commit transaction

  fetch next from cur_pubs
  into @discounttype, @stor_id, @lowqty, @highqty,@discount
end

close cur_pubs
deallocate cur_pubs

I really hv no ideas on this ..... :-(
please advise..



regs,
ms3756
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6858703
SECOND REQUEST, YEAR 2000 QUESTIONS STILL OPEN... *** ADMINISTRATION WILL BE CONTACTING YOU SHORTLY ***

Question(s) below appears to have been abandoned. Your options are:
 
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question QID/link(s) that it regards.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Please click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE Pro user, use the Power Search option to find them.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.11516818.html
http://www.experts-exchange.com/questions/Q.20137653.html
http://www.experts-exchange.com/questions/Q.11710518.html
http://www.experts-exchange.com/questions/Q.12022840.html
http://www.experts-exchange.com/questions/Q.20161420.html
http://www.experts-exchange.com/questions/Q.20178726.html
http://www.experts-exchange.com/questions/Q.20206126.html
http://www.experts-exchange.com/questions/Q.20270601.html


PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please click this link http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
 
Thank you everyone.
 
Moondancer
Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6951474
Admin notified of User neglect. Force-accepted by
Netminder
CS Moderator
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now