Insert Statement Fails on Linked servers

Hi,
we have a local server with a database say A on it . we also have a
linked server which has a database B.
now we are trying to insert into a table in a using data from the
database B. both of the tables in both the database are the same in
structure .
now when i use a query like
insert into a.Table1
( No,
Name
)
select
no,
name
frrom
host_sever.B.dbo.table1
where <some condition >
the above query fails and the error says a nested distributed
transaction cannot be started
both the tables have a trigger attached to it .
we found that first inserting the data into a temp table and then
copying that data into the main table in local server in database A
works fine.
also i tested some scenario with no trigger and it works fine .
is this how it is when there are triggers attached and is there any way
we can succesfully run the query ableve without temp tables.
thanks
ravinderavravinder@.gmail.com wrote:
> Hi,
> we have a local server with a database say A on it . we also have a
> linked server which has a database B.
> now we are trying to insert into a table in a using data from the
> database B. both of the tables in both the database are the same in
> structure .
> now when i use a query like
> insert into a.Table1
> ( No,
> Name
> )
> select
> no,
> name
> frrom
> host_sever.B.dbo.table1
> where <some condition >
> the above query fails and the error says a nested distributed
> transaction cannot be started
> both the tables have a trigger attached to it .
> we found that first inserting the data into a temp table and then
> copying that data into the main table in local server in database A
> works fine.
> also i tested some scenario with no trigger and it works fine .
> is this how it is when there are triggers attached and is there any way
> we can succesfully run the query ableve without temp tables.
> thanks
> ravinder
>
Is the MSDTC service running on both machines? Is it enabled for
network access?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> avravinder@.gmail.com wrote:
> Is the MSDTC service running on both machines? Is it enabled for
> network access?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
i looked at the services and DTC is runnign . there is another one is
control panel which says MSDTC which i think you are referreing too.
the network protocol says TCP/Ip and the salection default MS DTC
server is blank and disabled .
should this also not have created a issue when dum[ing into the temp
table and cause the same issue.
thanks
ravinder|||avravinder@.gmail.com wrote:
> Tracy McKibben wrote:
> i looked at the services and DTC is runnign . there is another one is
> control panel which says MSDTC which i think you are referreing too.
> the network protocol says TCP/Ip and the salection default MS DTC
> server is blank and disabled .
> should this also not have created a issue when dum[ing into the temp
> table and cause the same issue.
> thanks
> ravinder
>
No, it wouldn't cause an issue that way. Everything that occurs within
a trigger is done inside a transaction, and for a transaction to cross a
linked server, DTC must be available.
I would suggest starting here:
http://www.sqlservercentral.com/col...realsqlguy.com