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:
> > 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
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:
>> avravinder@.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
> 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/columnists/ckempster/debuggingmsdtcissues.asp
Tracy McKibben
MCDBA
http://www.realsqlguy.com