August 8, 2007 at 10:25 am
I have a stored procedure that delete data from local (LocalProgramTable) table and remote (server210.DB.dbo.event) table based on Audit (AuditProgramTable) Table's LogType='D' with status= 0 and assumed all as whole One Transaction, if remote delete fails it won't delete data from local Table and won't update Local Audit table aswell. Both Local and remote Databases are running on SQL Server 2000, but on different locations.
here is an example:
Create Proc usp_Delete_Data
AS
BEGIN
BEGIN DISTRIBUTED TRANSACTION
-- ********** delete from Remote Table First
Delete from server210.DB.dbo.event
where ProgramId in (select ProgramID from AuditProgramTable where LogType = 'D' and status = 0)
Delete from LocalProgramTable
where ProgramId in (select ProgramID from AuditProgramTable where LogType = 'D' and status = 0)
update AuditProgramTable set status = 1 where (LogType = 'D' and status = 0)
If @@ERROR = 0
COMMIT TRANSACTION
Else
ROLLBACK TRANSACTION
END
GO
The problem is,
1- When I run above proc. it won't responed anymore. If I put comment on "BEGIN DISTRIBUTED TRANSACTION" it works smoothly. There is no any issue accessing remote server's "server210.DB.dbo.event" Table. How I can make sure that the Transaction will roleback all local deletes and updates if remote server transaction fails for any reason. (Network/services/Relational database constraints)
2- Is above method of writing these three Transaction is correct or NOT? let me know the right way of handling multiple tranactions as one in stored procedures. I have same case with insert and update.
Shamshad Ali.
August 9, 2007 at 4:56 am
I do not claim any expertise but your question intrigued me so I searched BOL and this is what I found, so please eliminate them as possible causes.
Meta Data Services Programming
Integration with Distributed Transaction Coordinator
You can design an application that runs a distributed transaction on Microsoft® SQL Server™ 2000 running on Microsoft Windows® 2000.
Before you use Microsoft Distributed Transaction Coordinator (MS DTC) with a SQL Server 2000 Meta Data Services repository, you must install the Windows 2000 Service Pack 1. This service pack fixes an intermittent bug that causes MS DTC to stop responding when committing changes to a repository database.
The protocol for coordinating transaction atomicity across multiple resource managers is a two-phase commit. The Microsoft facility for a two-phase commit is MS DTC. You can enable distributed transactions to support the following scenarios:
Create an application that updates data in two repositories within the same transaction.
Create an application that updates data in a repository and in another database within the same transaction.
Create an application that runs a Microsoft Transaction Server (MTS) to update a repository, while running the application within the transaction that called it.
Create an information model that aggregates a repository object class and updates another database within the aggregation wrapper.
The distributed transaction must be atomic; that is, it must either commit at all resource managers or terminate at all of them. For more information about supporting atomic operations, see Transaction Management Overview.
The Transaction Protocol
To support MS DTC in your application, you must set the TXN_USE_DTC transaction flag. IRepositoryTransaction supports the TXN_USE_DTC flag on the GetOption and SetOption methods. The bit value for TXN_USE_DTC is 10. The default value of this option is FALSE. If the value is set to TRUE, each call to IRepositoryTransaction::Begin will create an MS DTC transaction.
IRepositoryTransaction::SetOption(10, 1)
IRepositoryTransaction::Begin
MS DTC requires that the participant who started the transaction be the only party who can call Commit.
Programming in Visual C++
If you are a Microsoft Visual C++® programmer, you can use the ITransactionJoin::JoinTransaction method. You can use this method to cause a repository instance that is not currently running a transaction to become part of an existing MS DTC transaction. The active MS DTC transaction object is passed in as an input argument. For more information about the ITransactionJoin interface, see SQL Server Books Online.
Programming in Microsoft Visual Basic
Microsoft Visual Basic® applications must use the following API to enlist an MS DTC transaction:
HRESULT IRepositoryTransaction2::JoinTransaction ([in]VARIANT sVArTxn);
where sVArTxn is an IUnknown pointer to the distributed transaction coordinator.
See Also
IRepositoryTransaction::begin
IRepositoryTransaction::commit
IRepositoryTransaction::getOption
IRepositoryTransaction::setOption
Managing Transactions and Threads
©1988-2000 Microsoft Corporation. All Rights Reserved.
August 9, 2007 at 7:31 am
Thanks for your reply. But I don't understand explaination from BOL. There must be an easy/simple management in SQL Server itself to handle multiple resourse and commit them as one transaction. Again, from BOL:
Distributed transactions are transactions that involve resources from two or more sources. Microsoft® SQL Server™ 2000 supports distributed transactions, allowing users to create transactions that update multiple SQL Server databases and other sources of data.
I will further study it and if any one else has implemented this scenario in production successfully, plz share.
Thanks in advance.
Shamshad Ali.
August 9, 2007 at 7:45 am
Distributed transactions span two or more servers known as resource managers. The management of the transaction must be coordinated between the resource managers by a server component called a transaction manager. Microsoft® SQL Server™ can operate as a resource manager in distributed transactions coordinated by transaction managers such as the Microsoft Distributed Transaction Coordinator (MS DTC), or other transaction managers that support the X/Open XA specification for Distributed Transaction Processing. For more information, see the Microsoft Distributed Transaction Coordinator documentation.
A transaction within a single SQL Server that spans two or more databases is actually a distributed transaction. SQL Server, however, manages the distributed transaction internally; to the user it operates as a local transaction.
At the application, a distributed transaction is managed much the same as a local transaction. At the end of the transaction, the application requests the transaction to be either committed or rolled back. A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure may result in some resource managers successfully committing while others roll back the transaction. This is achieved by managing the commit process in two phases (the prepare phase and the commit phase), which is known as a two-phase commit (2PC).
Prepare phase
When the transaction manager receives a commit request, it sends a prepare command to all the resource managers involved in the transaction. Each resource manager then does everything required to make the transaction durable and all buffers holding log images for the transaction are flushed to disk. As each resource manager completes the prepare phase, it returns success or failure of the prepare to the transaction manager.
Commit phase
If the transaction manager receives successful prepares from all the resource managers, it sends commit commands to each resource manager. The resource managers can then complete the commit. If all the resource managers report a successful commit, the transaction manager then sends a success notification to the application. If any resource manager reported a failure to prepare, the transaction manager sends a rollback command to each resource manager and indicates the failure of the commit to the application.
SQL Server applications can manage distributed transactions either through Transact-SQL or the database API.
August 9, 2007 at 7:45 am
I had in mind this particular para from BOL:
Before you use Microsoft Distributed Transaction Coordinator (MS DTC) with a SQL Server 2000 Meta Data Services repository, you must install the Windows 2000 Service Pack 1. This service pack fixes an intermittent bug that causes MS DTC to stop responding when committing changes to a repository database.
August 9, 2007 at 7:52 am
You need the DTC service to be running and I remember some trick with AD security to be sure that both sides can correctly communicate with each other. The distributed transaction stuff in 2000 was a little finicky and I know I had to change settings to get it to work, but can't remember what.
I'm not sure what is wrong with your situation. We didn't have a domain, so I know I had some issues there.
August 10, 2007 at 1:02 am
Steve, Thanks for your comment. My situation is with Windows Server 2003. I hope the sp1 of windows 2000 should be already included in my Win2K3s. Here one question is do I need to run DTC Services on Both machines? I need some further "change settings" from your side.
Once again thanks for all your efforts in advance.
Shamshad Ali.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply