Distributed Transactions between SQL2005 and SQL2000

Hi there,

We have two servers, one (we'll call 'SERVERA') has SQL2005 running on it. The second (we'll call 'YELLOWSTEONE') is running both SQL2000 and SQL2005 on it. The SQL instances on YELLOWSTONE are 'YELLOWSTONE\SQL2000' and 'YELLOWSTONE\SQL2005'. As a linked server, I have an entry for YELLOWSTONE which then links to the SQL Server of YELLOWSTONE\SQL2000 on the server network name of YELLOWSTONE. By them selves they seem to run fine. However, if I have trigger that Runs on SERVERA to do a distributed transaction on 'YELLOWSTONE\SQL2000', I get the following error:

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 2, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [2].

If You can provide me any assistance, I would greatly appreciate it. Thanks! - Eric -

Ok, Figured it out. I needed to define the Server type as 'SQL Server' instead of 'Other' and then picking the SQL Server connector. Ooops!

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

distributed transaction error from CLR Trigger

I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:

Command attempted:

if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)

Error messages:

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391

A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549

Any thoughts or direction appreciated

Richard

Hi!

You may try to play with settings of OraOLEDB (DistribTX).

|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--

First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.