Msg 7391, Level 16 The operation could not be performed because OL

I am getting the following error message
Msg 7391, Level 16, State 2, ProcedureName, Line 103
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "LINKEDSERVERNAME" was unable to begin a distributed
transaction.
This error is thrown from a stored procedures from a SQL Server 2005 (32)
default instance on Windows 2003 Server. This sp creates a #temp table and
inserts values based on the results of an executed stored procedure against a
linked server as follows: The linked server points to a SQL Server 200(32)
on Windows 2000 Server os.
create table #Data (
...columns...
)
insert into #Data
(...columns)
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
If I run the following command by itself, it runs fine. It only errors
within the sp and while it is inserting into #temp.
exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
I have enabled network DTC access on the executing Windows 2003 server as
suggested by the following article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
Any Help?Hi
Have you checked out http://support.microsoft.com/kb/839279
John
"timw86" wrote:
> I am getting the following error message
> Msg 7391, Level 16, State 2, ProcedureName, Line 103
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "LINKEDSERVERNAME" was unable to begin a distributed
> transaction.
> This error is thrown from a stored procedures from a SQL Server 2005 (32)
> default instance on Windows 2003 Server. This sp creates a #temp table and
> inserts values based on the results of an executed stored procedure against a
> linked server as follows: The linked server points to a SQL Server 200(32)
> on Windows 2000 Server os.
> create table #Data (
> ...columns...
> )
> insert into #Data
> (...columns)
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> If I run the following command by itself, it runs fine. It only errors
> within the sp and while it is inserting into #temp.
> exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> I have enabled network DTC access on the executing Windows 2003 server as
> suggested by the following article.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
> Any Help?|||Thanks John.
The issue appears to be MSDTC security configurations for the initiating
server (windows 2003 server). By default, after I enabled Distributed
Transactions, the transaction manager communication had its setting to Mutual
Authentication. I concluded that this will not work for this our
environment. We have a windows 2003 server initiating dtc to a windows 2000
server. I changed the setting to no authentication is required. This has
allowed the sql commands to be executed without error. I conclude that the
mutual authentication is a new feature to windows 2003 and requires 2 windows
2003 servers communicating via DTC to one another. The kb article's
symptoms and cause are lacking in detail. However, the kb workaround
provided usefull steps.
Tim
"John Bell" wrote:
> Hi
> Have you checked out http://support.microsoft.com/kb/839279
> John
> "timw86" wrote:
> > I am getting the following error message
> >
> > Msg 7391, Level 16, State 2, ProcedureName, Line 103
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "LINKEDSERVERNAME" was unable to begin a distributed
> > transaction.
> >
> > This error is thrown from a stored procedures from a SQL Server 2005 (32)
> > default instance on Windows 2003 Server. This sp creates a #temp table and
> > inserts values based on the results of an executed stored procedure against a
> > linked server as follows: The linked server points to a SQL Server 200(32)
> > on Windows 2000 Server os.
> >
> > create table #Data (
> > ...columns...
> > )
> >
> > insert into #Data
> > (...columns)
> > exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> >
> > If I run the following command by itself, it runs fine. It only errors
> > within the sp and while it is inserting into #temp.
> >
> > exec LINKDEDSERVER.DATABASE.dbo.STOREDPROCEDURE @.PARAM
> >
> > I have enabled network DTC access on the executing Windows 2003 server as
> > suggested by the following article.
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;817064
> >
> > Any Help?