Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
roshan_kumar
Starting Member
3 Posts |
Posted - 2008-05-03 : 13:37:25
|
Hi all,I m trying to get record from one server to another using Commit and Rollback feature in the Sql Server and getiing error message--"No transaction is active."below is my sql blockBEGIN TRANBEGIN TRYINSERT INTO [SERVERNAME1].[DATABASENAME].[dbo].[TABLENAME](PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype) SELECT PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype FROM [SERVERNAME2].[DATABASENAME].[dbo].[TABLENAME] WHERE Convert(smalldatetime,purchasedate)>=Convert(smalldatetime,'2/14/2008 00:00:00') and Convert(smalldatetime,purchasedate)<= Convert(smalldatetime,'2/14/2008 23:59:59') COMMIT TRANEND TRYBEGIN CATCH Rollback TRANEND CATCHENDPlease replay ASAP. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-03 : 13:46:51
|
Have you added the one server as linked server to another? |
 |
|
roshan_kumar
Starting Member
3 Posts |
Posted - 2008-05-05 : 12:02:46
|
Yes i have added server as a linked server.If i remove the transaction(Rollback/Commit) feature it is working sucessfully bu ti want to use transaction in my queryLets get it done |
 |
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-05 : 22:25:14
|
Is msdtc running on all involved servers? |
 |
|
roshan_kumar
Starting Member
3 Posts |
Posted - 2008-05-06 : 15:28:53
|
Thanks for the replys,Msdtc is fully enabled on both servers.Netic there is a windows firewall, and I have added an exception for MSDTC and opened port 135.Following is the steps i took...To fully enable MSDTC: 1 - In Control Panel, open Administrative Tools, and then double-click Component Services.2 - In the left pane of Console Root, click Component Services, and then expand Computers.3 - Right-click My Computer, and then click Properties.4 - On the MSDTC tab, click Security Configuration.5 - Under Security Settings, select all of the check boxes.6 - Verify that the DTC Logon Account name is set to NT AUTHORITY\NetworkService.NEXT I MADE CHANGES TO WINDOWS FIREWALL...1 - Click Add Program to display the Add a Program dialog box.2 - Click Browse and navigate to %system32%\msdtc.exe.3 - Click to select msdtc.exe and click Open.4 - Click Change scope to specify the set of computers for which MSDTC communications should be allowed and click OK. (I ADDED THE REMOTE SERVER IP ADDRESSES)5 - Open port 1356 - Stop and restart the Distributed Transaction Coordinator service.Launch a command prompt, type net stop msdtc and press Enter.After the Distributed Transaction Coordinator service has stopped, type net start msdtc and press Enter.THEN I RESTARTED BOTH SERVERS AND USED DTCTESTER AND GOT THE FOLLOWING RESULTS...C:\>dtctester.exe test "username" "password"Executed: dtctester.exeDSN: testUser Name: "value"Password: "value"tablename= #dtc17056Creating Temp Table for Testing: #dtc17056Warning: No Columns in Result Set From Executing: 'create table #dtc17056 (ivalint)'Initializing DTCBeginning DTC TransactionEnlisting Connection in TransactionError:SQLSTATE=25S12,Native error=0,msg='[Microsoft][SQL Native Client]The transaction has already been implicitly or explicitly committed or aborted'Error:SQLSTATE=24000,Native error=0,msg=[Microsoft][SQL Native Client]Invalid cursor stateTypical Errors in DTC Output Whena. Firewall Has Ports Closed-OR-b. Bad WINS/DNS entries-OR-c. Misconfigured network-OR-d. Misconfigured SQL Server machine that has multiple netcards.Aborting DTC TransactionReleasing DTC Interface PointersSuccessfully Released pTransaction Pointer.ANY IDEA'S WHAT MIGHT STILL BE CAUSING THE PROBLEM?THANKS,ROSHANLets get it done |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-06 : 15:33:37
|
It is unnecessary to use a transaction when you have only one DML statement. A transaction is implicit in this case.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
|
|
|
|