No transaction is active.

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

Howdy, the BEGIN TRAN should be inside of the BEGIN TRY !
Best regards,
Paulo Condeça.

No working same error is comming
OLE DB provider "SQLNCLI" for linked server "SERVERNAMESQLEXPRESS" 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 "SERVERNAMESQLEXPRESS" was unable to begin a distributed transaction.

That is a differente issue !
Configure the Microsoft Distributed Transaction Coordinator and check that it is up n' running !
When you are implementing transactions in more than 1 server, you need MSDTC.
Hope this gives a light to you .
Best Regards,
Paulo Condeça.

MSDTC is running on both the server
Here what is selected:

Network DTC Access (Checked)
Allow Remote Client (Checked)
Allow Remote Administration(Checked)
Allow Inbound and Allow outbound (Both Checked)
Manual Authentication Required(Checked)
Enable Transaction Internet Protocol(TIP) Transactions(Checked)

Enable XA Transaction (Checked)

DTC Logon Account
Account:NT AUTHORITYNetworkService

Roshan,
What are the Transaction Statistics & DTC LOG C:Windowssystem32MSDtc (Default Path).
By the way... are there any Firewalls in the middle ?
Best Regards,
Paulo Condeça.

I Checked for the MSDTC in both the server it is enabled and running

Thanks for the replys,

Satya I followed your instructions and can confirm 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 AUTHORITYNetworkService.

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

Another option: skip the distributed transaction altogether.
Instead, just do a plain insert - select from.. into a staging table on the destination server.
Validate
Do the final insert into the prod table as a local transaction. (well, local in the sense only one server is involved..)
Now, I haven't tried this with Express, so I'm not sure that it still won't complain about distributed transactions, since it may do it implicit anyway.
But, maybe worth a try?
/Kenneth