cannot begin DISTRIBUTED TRANSACTION via LinkedServer

cannot begin DISTRIBUTED TRANSACTION via LinkedServer

Post by Wujek Dobr » Mon, 27 Feb 2006 19:01:22


hello,
i've a problem:

server-A :SQLServer2005 running on WindowsXP+SP2 proff.
server-B :SQLServer2005 running on WindowsXP+SP2 proff.

I've done from A to B:
a) sp_addlinkedserver ....
b) sp_addLinkedSrvLogin ...

and it's working ok:

i'can made Select, Update from A on B,
but i cannot do sth like (placed in stored procedure on server A):

BEGIN DISTRIBUTED TRANSACTION
select * from B.......
..
update A ....
..
update B .....
COMMIT

the answer is "Another transaction is using conntext",
or "cannot start transaction because @@trancount is 1, byt should be 2"

(ansewer is probably bit different from the above, cause i'm
writing it two days after i met that problem),

Anyone knows what to do - to performit such a transaction?

Wujek Dobra Rada
 
 
 

1. Error when creating replication: Cannot use SAVE TRANSACTION within a distributed transaction

2. Cannot use SAVE TRANSACTION within a distributed transaction.

sql2k sp2

Im trying to figure out how to run a script at nite which
will alter the same table on different machines. The idea
is to widen a column. The remote machine has a proc called
AlterABC and the Local machine has a script which both
alters the Local tables and exec's the Remote proc. All
this is done inside a transaction. A small code snippet:

begin
ALTER TABLE dbo.account_balances_current_abc
DROP CONSTRAINT
DF_account_balances_current_abc_abc_branch_no
ALTER TABLE dbo.account_balances_current_abc
alter column abc_branch_no varchar(30) not null
ALTER TABLE dbo.account_balances_current_abc
ADD CONSTRAINT
DF_account_balances_current_abc_abc_branch_no
DEFAULT ('') FOR abc_branch_no
set @AltPub = @@error

exec sqldev1.abc.dbo.AlterABC
set @AltSub = @@error

if @AltPub = 0 and @AltSub = 0 and @@trancount = 0
commit

else rollback

I get the following message:

Server: Msg 627, Level 16, State 1, Procedure
sp_MSreinit_article, Line 35
Cannot use SAVE TRANSACTION within a distributed
transaction.

Thanks in advance.
Chris

3. SQLOLEDB was unable to begin a distributed transaction

4. 'SQLOLEDB' was unable to begin a distributed transaction!!!!

5. ...unable to begin a distributed transaction

6. 'SQLOLEDB' was unable to begin a distributed transaction

7. unable to begin a distributed transaction

8. 'SQLOLEDB' was unable to begin a distributed transaction

9. 'MSDAORA' was unable to begin a distributed transaction - why?

10. Seeking Testing Volunteers W2K MTS/DTC to VMS DECdtm Distributed 2PC Transactions 2PC Transactions 2PC Transactions

11. Problem with distributed transaction via linked server

12. problem with distributed transaction via linked server

13. OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message "No transaction is active.".

14. SQL error on URL -cannot create new connection because in manual or distributed transaction mode

15. Cannot update table using BEGIN TRANSACTION in Novell Server 4.11