Problem transfering data between servers

[TechSpiders]

Hi all,

I m trying to get records from one server to another using Commit and Rollback feature in the Sql Server and getting this 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

Following is the complete error message i receive (more detailed)...

OLE DB provider "SQLNCLI" for linked server "SERVERNAME\SQLEXPRESS" 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 "SERVERNAME\SQLEXPRESS" was unable to begin a distributed transaction.

Any help to resolve would be appreciated!

Thanks

Roshan

 

[SQLDenis]

you probably need SET XACT_ABORT ON as the first statement, this is required for distributed transactions


>>WHERE Convert(smalldatetime,purchasedate)>=Convert(smalldatetime,'2/14/2008 00:00:00')
and Convert(smalldatetime,purchasedate)<= Convert(smalldatetime,'2/14/2008 23:59:59')

This is not sargable, what datatype is purchasedate



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TechSpiders]

Hi,

purchasedate is type of Datetime

I tried SET XACT_ABORT ON on Top but same error is comming

here is the Sql block:

SET XACT_ABORT ON
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



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.

 

[SQLDenis]

make sure that MS DTC is running on both servers
REMOTE_PROC_TRANS must be set to 1

also maybe this is a little neater

SET XACT ABORT ON
BEGIN TRANSACTION;

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')

END TRY
BEGIN CATCH
    
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TechSpiders]

Yes MS DTC is running in both server

How to SET REMOTE_PROC_TRANS 1 ?

 

[TechSpiders]

when i execute without using transaction it works but why not using transaction

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')

 

[SQLDenis]

>>when i execute without using transaction it works but why not using transaction

yes that is why MS DTC has to be running, Is it?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TechSpiders]

Yes MS DTC is running

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 AUTHORITY\NetworkService

 

[SQLDenis]

>>Yes MS DTC is running

on both servers?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TechSpiders]

Ya same setting on MS DTC on both the server

 

[SQLDenis]

Take a look here

http://support.microsoft.com/?kbid=873160

>>Windows Firewall is enabled on the computer. By default, Windows Firewall blocks the MSDTC program.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TechSpiders]

Firewall are allowed for MS DTC

 

[TechSpiders]

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