Friday, March 23, 2012

Error when issuing OPENQUERY

I'm running on a Windows 2003 server SQL Server 2000. I'm getting the
following error when trying to execute an OPENQUERY statement. I have
about 5 linked servers, and they all worked in the past. Now one of
them is giving me trouble when issuing the OPENQUERY statement with
the following error:
Server: Msg 7391, Level 16, State 1, Line 1
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've tried all the Microsoft Workaround solutions:
http://support.microsoft.com/kb/873160 (even if it doesn't apply 100%)
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
http://support.microsoft.com/default.aspx?scid=kb;en-us;816701
http://support.microsoft.com/?kbid=816701
The linked server has not changed according to the administrator.
There is no firewall under either side of the severs (internal private
network). After enabling DTC under the remote server, I've noticed
that the queries take longer, but they continue returning the same
error as indicated above.
The servers have been configured with 'No authentication on DTC' so I
can't imagine what the problem could be, I've also checked the routing
(IP routes) between the two servers and I can't see any flaw, all
traffic is internal. I say this because the servers host dual cards
(internal and external networks) - but the external network is not
being enabled. Bidirectional traceroutes show the same path.
I've Googled the error messages and they all point to the same thing,
but it appears not to work. I have other SQL servers under the same
internal networks and I can run the OPENQUERY on them.
Can anyone help me out here?Hi
The last two links you posted are the same!
Have you tried DTCping? http://support.microsoft.com/kb/306843
John
"InvestorTrade" wrote:
> I'm running on a Windows 2003 server SQL Server 2000. I'm getting the
> following error when trying to execute an OPENQUERY statement. I have
> about 5 linked servers, and they all worked in the past. Now one of
> them is giving me trouble when issuing the OPENQUERY statement with
> the following error:
> Server: Msg 7391, Level 16, State 1, Line 1
> 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've tried all the Microsoft Workaround solutions:
> http://support.microsoft.com/kb/873160 (even if it doesn't apply 100%)
> http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
> http://support.microsoft.com/default.aspx?scid=kb;en-us;816701
> http://support.microsoft.com/?kbid=816701
> The linked server has not changed according to the administrator.
> There is no firewall under either side of the severs (internal private
> network). After enabling DTC under the remote server, I've noticed
> that the queries take longer, but they continue returning the same
> error as indicated above.
> The servers have been configured with 'No authentication on DTC' so I
> can't imagine what the problem could be, I've also checked the routing
> (IP routes) between the two servers and I can't see any flaw, all
> traffic is internal. I say this because the servers host dual cards
> (internal and external networks) - but the external network is not
> being enabled. Bidirectional traceroutes show the same path.
> I've Googled the error messages and they all point to the same thing,
> but it appears not to work. I have other SQL servers under the same
> internal networks and I can run the OPENQUERY on them.
> Can anyone help me out here?
>
>|||On Jan 7, 12:33=A0pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> The last two links you posted are the same!
> Have you tried DTCping?http://support.microsoft.com/kb/306843
> John
>
> "InvestorTrade" wrote:
> > I'm running on a Windows 2003 server SQL Server 2000. =A0I'm getting the=
> > following error when trying to execute an OPENQUERY statement. =A0I have=
> > about 5 linked servers, and they all worked in the past. =A0Now one of
> > them is giving me trouble when issuing the OPENQUERY statement with
> > the following error:
> > Server: Msg 7391, Level 16, State 1, Line 1
> > 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've tried all the Microsoft Workaround solutions:
> >http://support.microsoft.com/kb/873160(even if it doesn't apply 100%)
> >http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;329332
> >http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;816701
> >http://support.microsoft.com/?kbid=3D816701
> > The linked server has not changed according to the administrator.
> > There is no firewall under either side of the severs (internal private
> > network). =A0After enabling DTC under the remote server, I've noticed
> > that the queries take longer, but they continue returning the same
> > error as indicated above.
> > The servers have been configured with 'No authentication on DTC' so I
> > can't imagine what the problem could be, I've also checked the routing
> > (IP routes) between the two servers and I can't see any flaw, all
> > traffic is internal. =A0I say this because the servers host dual cards
> > (internal and external networks) - but the external network is not
> > being enabled. =A0Bidirectional traceroutes show the same path.
> > I've Googled the error messages and they all point to the same thing,
> > but it appears not to work. =A0I have other SQL servers under the same
> > internal networks and I can run the OPENQUERY on them.
> > Can anyone help me out here... Hide quoted text -
> - Show quoted text -
Thank you!!! DTCPing helped to spot the problem - Bottom line - the
RPC Locator was shutdown under the remote server - the moment I
enabled it, it worked like a charm.

No comments:

Post a Comment