Problem with linked servers

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561632
I have two servers that are linked together ServerA and ServerB. I can do
selects from remote tables on both servers with no problems. The problem
that i am running into is when I am doing an insert into a local table via a
join with a local and remote table.
Both Win2k3 server with SQL 2000 SP3
ie (I know this code isnt correct, just an example)
insert into ServerA.DB.dbo.table
select *
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
I can run the selects just fine, but when adding the inserts, it craps out.
----
---
this is the error that i am getting
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].try this, it might help
select *
into #tmpData
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
insert into ServerA.DB.dbo.table
select *
from
#tmpData
remember that 'select *' shouldn't be used in production code.
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> I have two servers that are linked together ServerA and ServerB. I can do
> selects from remote tables on both servers with no problems. The problem
> that i am running into is when I am doing an insert into a local table via
a
> join with a local and remote table.
> Both Win2k3 server with SQL 2000 SP3
> ie (I know this code isnt correct, just an example)
> insert into ServerA.DB.dbo.table
> select *
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> I can run the selects just fine, but when adding the inserts, it craps
out.
> ----
---
> this is the error that i am getting
> The operation could not be performed because the OLE DB provider
'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561776
I had given thought to the temp table, but i would like to know if there is
configuration problem with my server keeping me from doing this.
TIA
Kevin E.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4360865d$0$141$7b0f0fd3@.mistral.news.newnet.co.uk...
> try this, it might help
> select *
> into #tmpData
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> insert into ServerA.DB.dbo.table
> select *
> from
> #tmpData
> remember that 'select *' shouldn't be used in production code.
>
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> a
> out.
> ---
> 'SQLOLEDB'
>