No Transaction is active - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

No Transaction is active

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 block

BEGIN TRAN
BEGIN TRY
INSERT 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 TRAN
END TRY
BEGIN CATCH
Rollback TRAN
END CATCH
END

Please 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 query

Lets 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 135
6 - 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.exe
DSN: test
User Name: "value"
Password: "value"
tablename= #dtc17056
Creating Temp Table for Testing: #dtc17056
Warning: No Columns in Result Set From Executing: 'create table #dtc17056 (ival
int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Error:
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 s
tate
Typical Errors in DTC Output When
a. 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 Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.

ANY IDEA'S WHAT MIGHT STILL BE CAUSING THE PROBLEM?

THANKS,

ROSHAN

Lets 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources