linked server problem with error message 7391

I have two computers both running Windows Server 2003 and SQL Server 2000. I
have a trigger on a table located on the first computer which includes a line
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what is
wrong.
After reading the many threads about this I have checked the following items:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.
See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/default...b;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:

> I have two computers both running Windows Server 2003 and SQL Server 2000. I
> have a trigger on a table located on the first computer which includes a line
> of code that inserts into a table on the second computer. The two computers
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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].
> I have not touched any of the code, so something in terms of the setup must
> have changed so that it doesn't work anymore, how ever I have no idea what is
> wrong.
> After reading the many threads about this I have checked the following items:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is run
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run over
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same error.
> So essentially I can run the code as a stored procedure but not as a trigger.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.

linked server problem with error message 7391

I have two computers both running Windows Server 2003 and SQL Server 2000.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:

> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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 th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.

linked server problem

I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>

linked server problem

I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.
You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>
|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>

linked server problem

I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>>running very smooth and when I turn on the firewall, the trigger action is
>>giving error and failed to take action. Is this a Big NO between firewall
>>and Linked server? Is there a way to solve this problem? Thanks.
>

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
Elecia
Are You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi
|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
Elecia
"saradhi" wrote:

> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:

> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>

Linked Server Issues

Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>sql

linked server insode triggers problems?

Dear all

I am trying to use linked server objects inside a trigger and have some major problems.

Just to explain what i am trying to achieve:

My server A is SQL 2000. When ever a row is added to a table on server A i would like to send some of the inserted values to server B which is a physically different computer and has SQL 2005.

To do that i created linked server object on the SQL 2000 side.

To test the linked server object i go:

Select * ServerB.Database.dbo.Table

This works perfectly and i get the results. I then test the same inside a stored procedure but i do some parameterised queries.

Select id from ServerB.Database.dbo.Table where id=@.myId

This also works perfectly inside the stored procedure.

BUT NOW IT COMES THE FUN PART.

As soon as i place this inside the trigger it just doesnt work. My trigger has nothing else pretty much except for that. Here is a sample:

BEGIN
SET NOCOUNT ON
SET xact_abort ON

DECLARE @.myValue nvarchar(50)

SET @.myValue = '6357'

SELECT * from ServerB.Database.dbo.Table
Where id = @.myValue

END

It just gives me a timeout error. But what is even worse is that after this the whole database is crashed and i have to restart the database service to make it work.

I checked both servers and they have the service DTS for the distributed transaction on. No proxies, no firewals. Also i checked the servers configuration and they have RPC,RPC OUT and Data Access enabled.

I have tried everything over the last week and nothing has worked for me.

Any advice would be much much apreciated.

Sincerely
Dan

When you use 4-part name, the entire table is pulled from the remote server to the local before any filtering is applied. It's never a good idea to fetch & return results from a trigger.

Because trigger, by default, participates in the current transaction, it's best to keep the transaction as short as possible. In the case, you implicitly promote the transaction to a distributed transaction. That means both parties must play nice and DTC (which has a timeout) definitely is needed.

We need a bit more info as to what the timeout value refers to. Do enable 7300 trace flag and post the error here.

e.g.

dbcc traceon (-1,7300,3604)

go

|||

Dan_Dan wrote:

My server A is SQL 2000. When ever a row is added to a table on server A i would like to send some of the inserted values to server B which is a physically different computer and has SQL 2005.

Trigger is bad idea to do this task, you can utilize the Replication feature here.

See more on, http://msdn2.microsoft.com/en-us/library/ms151198.aspx

|||

You have violated a couple of 'no-nos' with TRIGGERS, and as you have discovered, that violation can have severe consequences.

First, a TRIGGER should NOT return data to the client. Use a Stored Procedure or UDF to return data to the client. TRIGGERs were not designed to be part of the client data interface, but were designed for internal operations only.

Second, a TRIGGER should NOT 'reach' outside the current processing thread. When you access data on a remote server, your client, your TRIGGER, and your local server, are at the mercy of the remote server. Anything disruptive in the connection, and/or the remote server will negatively effect your local operation -as you noticed. (This second factor can be worked around by using some form of 'store and forward' service, such as Service Broker. But again, not to retrieve data from a remote server.)

While you can, and will see these 'no-nos' violated, it just isn't a good idea.|||

This is the line from the sql logs.

I got this from C:\Program Files\Microsoft SQL\MSQL\LOGS

2007-08-09 12:19:40.60 spid8 This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 1 queries and performance may be adversely affected.

Before i enabled the dbcc traceon option as suggested in the post. But it does not give me any usable information to figure out as to why this is happening.

What i cant really understand is as to why the query would run inside a stored procedure or if ran just independently in the query analyser but it not in the trigger.

|||

You're running on MSDE, a free version of sql2k which has workload governor set. This is by design so you must upgrade to a paid version if you want to rid of the error.

http://msdn2.microsoft.com/en-us/library/aa258279(sql.80).aspx

As to the problem with the trigger, it could be authentication or network problem. We would need more info to nail it down. (A full error log would be helpful here).

sql

Linked Server - Trigger problem

Hi !

I have a problem, which i have dealt with a long time now. The scenario is that I have 2 SQL Server's (one 2K, the other 7). There are on both Servers a table (with users in it). In both tables on both servers, there are triggers (which fires on respectively : INSERT, DELETE and UPDATE).
If for example I add a new user on the SQL Server 2K, a new user with same properties are to be added on the other SQL Server. Very straight forward ! The 2 Servers are connected via "Linked Servers". In that way they each hold a reference to the other SQL Server. I have turned off both "recursive triggers" and "indirect recursive triggers". BUT, when I insert a new user, I get an error from MS DTC, that "Transaction context in use by another session. [OLE/DB provider returned message: Unspecified error]". I cannot get by this error. It is because the first trigger insert on the other table that again fires a trigger. This I can handle, but MS DTC cannot, because it fires a new transaction with the first one.... No can do, Microsoft says !!!!

Is there a bright head that have a solution for this one. And yes I know it not a very clever solution the one stated above, but under the circumstances it is the only thing we can do, without getting out in the middleware forest !!! (not a nice thing)what a night mare!!

Don't know if this will help but could both triggers call just one stored procedure that sits on one machine that does the actual insert ??

Alternativley can you use replication ??

linked server

I use a trigger which I update a linked server and this is working very good in SQL 2000. But after I migrate the database to SQL 2005. The trigger is not working with linked server any more. I remember I need to start the distributed transaction coordinator in service manager. But I couldn't find anything similar to this in SQL 2005. Is it somewhere I need to start? I got an error message like following:

Msg 7391, Level 16, State 2, Procedure EAI_ADM_STUDENT_ins_upd_trg, Line 804

The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "10.2.0.119" was unable to begin a distributed transaction.

Please help. Thanks.Try this. our db guy had a hell of a time figuring this out :)

Log into the machine named in the error message via termserv.
Start, Run, type 'dcomcnfg.exe'

This brings up the security manager.

Click Component Services/Computers/My Computer folder

Right click My Computer and select properties.

Pick the MSDTC tab.

Click the Security Configuration Button

Click Network DTC Access checkbox.

Click 'Allow Inbound', 'Allow Outbound' checkboxes.|||Thank. I tried it and unfortunately it still doesn't work.|||KBA http://support.microsoft.com/kb/306212 fyi in thsi case.|||Sorry. I have read the articles. But it doesn't help. The problem is not fixed.|||Have you checked Linked Server Properties?
There are Server Options
If you switch:
RPC = true
RPC Out = true
on both servers, you should have not any problems.

TSQL equivalent of above is:

USE [master]
GO
EXEC master.dbo.sp_serveroption @.server=N'MyServerName', @.optname=N'rpc', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'MyServerName', @.optname=N'rpc out', @.optvalue=N'true'
GO|||Sorry. Tried it but not working.