Not sure i this is the right forum, but here is my problem:
I am having a problem with distributed transactions, at least that is what the error messages are stating. My intentions are to do the following:
1. Set up a trigger on Server1.database1.table1 to fire after Insert/Update
2. The trigger will call a stored procedure on Server2 that will insert data into server2.database2.table2 passed as parameters from within the trigger.
3. I will use a linked server on Server1 to Server2.
4. This must work for version 2000/2005 SqlServers.
The following is the trigger I set up as a test trigger on Server1:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
CREATE TRIGGER [dbo].[trig_test]
DECLARE @OffNum int
DECLARE @Time DateTime
SET @OffNum = (SELECT OffNumber FROM Inserted)
SET @Time = GetDate()
EXEC [lgkprog].[traffic].[dbo].[sp_TestTrigger] @OffNum,@Time
lgkprog is server2 which is setup as a linked server on server1 where the table ‘tblCMOffense’ is located.
The following is the script for the stored procedure sp_TestTrigger:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[sp_TestTrigger]
SET XACT_ABORT ON;
SET NOCOUNT ON;
Insert into tblTestTrigger (offensenumber,insert_date) Values (@OffNum,@Time)
When I run the following query in SQL Server Management Studio new query window:
insert into tblCMOffense (Case#,PartyNumber) Values ('SUCR2005000002',1)
I get the following error:
OLE DB provider "SQLNCLI" for linked server "lgkprog" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure trig_test, Line 15
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "lgkprog" was unable to begin a distributed transaction.
Server1 is running on Windows XP Pro SP2, Server2 is running on Windows Server 2003 Enterprise Edition SP2.
I have enabled MSDTC by following the steps outlined in:
http://support.microsoft.com/kb/817064 How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?kbid=873160 You may receive a 7391 error message in SQLOLEDB when you run a distributed transaction against a linked server after you install Windows XP Service Pack 2 or Windows XP Tablet PC Edition 2005.
Any help on this would be greatly appreciated.
I downloaded and ran 'dtctester.exe' and obtained the following results from server1 machine connected using dsn to server2 machine:
User Name: sa
Creating Temp Table for Testing: #dtc2462
Warning: No Columns in Result Set From Executing: 'create table #dtc2462 (ival int)'
Beginning DTC Transaction
Enlisting Connection in Transaction
SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server Driver]Distributed transaction error'
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cursor state
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
b. Bad WINS/DNS entries
c. Misconfigured network
d. Misconfigured SQL Server machine that has multiple netcards.
Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
A possible solution - URL provided
I have wasted half a day's work trying to understand the origin of this problem. Since this forum post ranks pretty high on a googe search for the search string "2147168242 Distributed transaction error", I decided to post here what's helped me solve this problem:
"Warning: the CID values for both test machines are the same"
The problem stemmed from two duplicate machines (as a result of a VMware template) - both having the same Security Identifiers (SID's).
In case the URL above will become invalid in the future, this is the process that was required to solve my issue:
1. Use Add Windows Components, and remove Network DTC.
2. Go to the command line and run: MSDTC -uninstall
3. Go to the registry and delete the MSDTC keys in HKLM/Software/Microsoft/Software/MSDTC, HKLM/System/CurrentControlSet/Services/MSDTC, and HKEY_CLASSES_ROOT\CID (if they’re still there).
5. Go to the command line and run: MSDTC -install
6. Use Add Windows Components, and add Network DTC.
7. Go to the command line and run: net start msdtc
8. Go to services.msc and change Distributed Transactions Coordinator's startup method to Automatic.