[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Msg 7391 Unable to begin a distributed transaction

GW

3/29/2007 2:38:00 AM

I am getting the following error

Msg 7391, Level 16, State 2, Procedure procedurename, Line 20
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "servername" was unable to begin a distributed transaction.

Both Servers are:
Microsoft Windows Server 2003 R2 Standard x64 Edition Service Pack 1
SQL Server 2005 Developer Edition 64-bit Service Pack 1 (Version 9.00.2047.00)

They are in the same Workgroup. They are set up as Linked Servers in SQL and
have entries in the Hosts file on each server.

Distributed queries work fine.

MSDTC is set to 'Use local coordinator' and 'TCP/IP'

MSDTC > Security Configuration:
Network DTC Access > TRUE
Allow Remote Clients > TRUE
Allow Remote Administration > TRUE
Allow Inbound > TRUE
Allow Outbound > TRUE
Mutual Authentication Required > TRUE
Enable Transaction Internet Protocol (TIP) Transactions > TRUE
Enable XA Transactions > TRUE
Account: NT Authority\NetworkService

I have tried the following without success:
- Setting 'No Authentication Required' on both servers.
- http://support.microsoft.com...
- Note: Firewalls are not enabled on either server.

An example of the SQL in the stored procedure is:

create table #temp (
FID int
)
INSERT INTO #temp
exec server.msdb.dbo.sp_executesql N'
select top (10) ID from databasename.dbo.tablename'



Any help greatly appreciated as I have run out of ideas!

Thanks,

Graham
2 Answers

Adi Cohn

3/29/2007 4:38:00 AM

0

On Mar 29, 4:38 am, GW <G...@discussions.microsoft.com> wrote:
> I am getting the following error
>
> Msg 7391, Level 16, State 2, Procedure procedurename, Line 20
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "servername" was unable to begin a distributed transaction.
>
> Both Servers are:
> Microsoft Windows Server 2003 R2 Standard x64 Edition Service Pack 1
> SQL Server 2005 Developer Edition 64-bit Service Pack 1 (Version 9.00.2047.00)
>
> They are in the same Workgroup. They are set up as Linked Servers in SQL and
> have entries in the Hosts file on each server.
>
> Distributed queries work fine.
>
> MSDTC is set to 'Use local coordinator' and 'TCP/IP'
>
> MSDTC > Security Configuration:
> Network DTC Access > TRUE
> Allow Remote Clients > TRUE
> Allow Remote Administration > TRUE
> Allow Inbound > TRUE
> Allow Outbound > TRUE
> Mutual Authentication Required > TRUE
> Enable Transaction Internet Protocol (TIP) Transactions > TRUE
> Enable XA Transactions > TRUE
> Account: NT Authority\NetworkService
>
> I have tried the following without success:
> - Setting 'No Authentication Required' on both servers.
> -http://support.microsoft.com...
> - Note: Firewalls are not enabled on either server.
>
> An example of the SQL in the stored procedure is:
>
> create table #temp (
> FID int
> )
> INSERT INTO #temp
> exec server.msdb.dbo.sp_executesql N'
> select top (10) ID from databasename.dbo.tablename'
>
> Any help greatly appreciated as I have run out of ideas!
>
> Thanks,
>
> Graham

Try setting xact_abort on. This is taken from Books On Line:

"XACT_ABORT must be set ON for data modification statements in an
implicit or explicit transaction against most OLE DB providers,
including SQL Server. "

Adi

GW

3/29/2007 4:56:00 AM

0



"Adi" wrote:
> Try setting xact_abort on. This is taken from Books On Line:
>
> "XACT_ABORT must be set ON for data modification statements in an
> implicit or explicit transaction against most OLE DB providers,
> including SQL Server. "
>
> Adi
>

Yes I already have that set in my stored procedure.