No transaction is active.

Question

No transaction is active.

find.roshan

Old Hand

Points: 389
More actions
May 5, 2008 at 12:56 pm

#190004

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--
OLE DB provider "SQLNCLI" for linked server "SERVERNAME\SQLEXPRESS" returned message "No transaction is active."
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

[font="Courier New"]Roshan,

The table where you are trying to fetch the data does have clustered index?? I hope the table have only non-clustered index.

To resolve try creating a clustered index on the table and then try with the query!![/font]

Regards..Vidhya Sagar
SQL-Articles

find.roshan Old Hand Points: 389 More actions · Answer 2

I tried by creating cluster index to the table

but still not working

find.roshan Old Hand Points: 389 More actions · Answer 3

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