DISTRIBUTED TRANSACTION PROBLEM
indaclub
I linked one to another with
EXEC sp_addlinkedserver
@server='AV1',
@srvproduct='Av1',
@provider='SQLOLEDB',
@datasrc='88.247.124.178\ALISVERISSAATI'
My other sql instance is on 212.154.34.50\ALISVERISSAATI (i will name it
as AV2)
on av2 i have a sp which executes some sp's and data manipulation on Av1
I want to perform distributed tranactions on av2.
I did all
RESOLUTION
To resolve this problem, follow these steps: 1. Click Start, point to All
Programs, point to Administrative Tools, and then click Component Services.
2. In the Component Services Wizard, expand Component Services, and
then double-click Computers.
3. Right-click My Computer, and then click Properties.
4. Click the MS DTC tab, and then click Security Configuration.
5. In the Security Configuration dialog box, click to select the
Network DTC Access check box.
6. Under Network DTC Access, click Network Transactions.
Note If you installed Service Pack 1 for Windows Server 2003, you can
click Allow Inbound and Allow Outbound.
7. Make sure that DTC Logon Account is set to NT
Authority\NetworkService.
8. Click OK.
9. In the message box, click Yes to continue.
10. In the DTC Console Message dialog box, click OK.
11. In the System Properties dialog box, click OK.
Reboot the computer for these changes to take effect.
2.Enable MSDTC to allow the network transaction. To do this, follow
these steps: a. Click Start, and then click Run.
b. In the Run dialog box, type dcomcnfg.exe, and then click OK.
c. In the Component Services window, expand Component Services,
expand Computers, and then expand My Computer.
d. Right-click My Computer, and then click Properties.
e. In the My Computer Properties dialog box, click Security
Configuration on the MSDTC tab.
f. In the Security Configuration dialog box, click to select
the Network DTC Access check box.
g. To allow the distributed transaction to run on this computer
from a remote computer, click to select the Allow Inbound check box.
h. To allow the distributed transaction to run on a remote
computer from this computer, click to select the Allow Outbound check box.
i. Under the Transaction Manager Communication group, click to
select the No Authentication Required option.
j. In the Security Configuration dialog box, click OK.
k. In the My Computer Properties dialog box, click OK.
3. Configure Windows Firewall to include the MSDTC program and to
include port 135 as an exception. To do this, follow these steps: a. Click
Start, and then click Run.
b. In the Run dialog box, type Firewall.cpl, and then click OK
c. In Control Panel, double-click Windows Firewall.
d. In the Windows Firewall dialog box, click Add Program on the
Exceptions tab.
e. In the Add a Program dialog box, click the Browse button,
and then locate the Msdtc.exe file. By default, the file is stored in the
<Installation drive>:\Windows\System32 folder.
f. In the Add a Program dialog box, click OK.
g. In the Windows Firewall dialog box, click to select the
msdtc option in the Programs and Services list.
h. Click Add Port on the Exceptions tab.
i. In the Add a Port dialog box, type 135 in the Port number
text box, and then click to select the TCP option.
j. In the Add a Port dialog box, type a name for the exception
in the Name text box, and then click OK.
k. In the Windows Firewall dialog box, select the name that you
used for the exception in step j in the Programs and Services list, and then
click OK.
I successfull used dtcping utility and get Invoking RPC method on av1
RPC test is successful message on av2.
but when i try
SET xact_abort ON
GO
BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM [Av1].deneme.dbo.urunler
COMMIT TRAN
GO
it returns
OLE DB provider "SQLNCLI" for linked server "Av1" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "Av1" was unable to begin a distributed transaction.
any help will be much appreciated.
John Bell
Have you checked things like the firewall
http://support.microsoft.com/kb/306843/ or used DTCPing or DTCtester to make
a connection? you may also want to check out http://tinyurl.com/2tdbtf
John
indaclub
RPC test is successful
++++++++++++RPC test completed+++++++++++++++
Please start PING from av1 to complete the test
by the wat i read something a webpage and executed msdtc - uninstall
and msdtc - install on av1
when i run a query on av2 i get
Msg 8501, Level 16, State 2, Line 1
MSDTC on server 'FENERMARKET\ALISVERISSAATI' is unavailable.
for av1
i opened port 135 on both server and succeeded telnet av1 135
and telnet av2 135 for both server. dtcping util suceeds too . what am i
doing wrong ?
John Bell
Your original post covered what is needed for windows 2003 to allow DTC
http://support.microsoft.com/kb/817064/
I assume this is not a virtual server! http://support.microsoft.com/kb/889706/
asnd you have checked the RPC ports http://support.microsoft.com/kb/250367
Also check remote connections are enabled in the services and connection
section of the Surface Area Configuration Tool.
John
indaclub
with that issue. it is ok.
You are right . it is not a virtual server.
What about rpc ports . I opened 135 ports on both firewalls . isnt dtc
using 135 port By default?
When i run
SELECT * FROM [av1].deneme.dbo.urunler
it runs correctly.My surface area configuration is ok.
SET REMOTE_PROC_TRANSACTIONS ON
SET xact_abort ON
GO
BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM [av1].deneme.dbo.urunler
COMMIT TRAN
GO
returns
OLE DB provider "SQLNCLI" for linked server "av1" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "av1" was unable to begin a distributed transaction.
"John Bell" <jbellne...@hotmail.com>, haber iletisinde şunları
yazdı:4B5B98E8-710A-443F...@microsoft.com...
John Bell
I assume that just issuing a BEGIN DISTRIBUTED TRANSACTION will give the
error? Can you confirm DTC is running on AV1 and DTCPing works when
connecting to it?
If these check out I am not sure what is wrong! You may want to remove and
re-install DTC
http://support.microsoft.com/kb/306212
http://support.microsoft.com/kb/873160
http://support.microsoft.com/kb/839279
John
<in da club> wrote in message
news:u%23sYGX5V...@TK2MSFTNGP05.phx.gbl...