Distributed Transaction Between 2005 && 2000 Failing
Sep 19, 2007
Hi Folks,
I have been struggling with a problem for the last couple days now regarding MSTD and distributed transactions. The main issue is that 3 servers are each sitting in a different domain. Non of the domains trust each other.
As of yesterday, I could get all the SQL 2005 chatting to each other using MSDTC without a problem. However, I have a SQL 2000 box which refuses to work. A normal query across a linked server works fine. A distributed transaction will not work. This is the case from A to B and B to A.
I have tested with DTCPing and it says all is ok.
The error messages are:
SQL 2000 > SQL 2005
OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "T-ServerTServer" was unable to begin a distributed transaction.
And the other way round:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
If watching the DTC monitor, it shows an active transaction then goes back to 0.
All instances are on a 4 node cluster with 3 nodes turned off for the testing.
I have run out of things to try. Most of the symptoms for the above messages are to do with DTC security, the Turn RPC Security Off reg hack. All instances and OS (W2K3) are the latest updates / patches.
As for the 3rd server, SQL 2005, it is happy to talk
Anybody got a gem which I can try?
Edit: Both SQL 2005 servers are x64 while the 2000 is x86
I have a sql job which has to pull some data from a remote server. This job is running fine last few days and suddenly failing to execute with the following error. The same job is running fine in other servers.
Executed as user: NT AUTHORITYNETWORK SERVICE. The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction. [SQLSTATE 42000] (Error 1206). The step failed.
Note: The steps I have already done :
1. Verified the MSDTC configurations and restarted the MS DTC
I found the following link which seems to be the problem we're experiencinghttp://support.microsoft.com/kb/937517
The link includes a workaround which is the following: "To prevent the SQLNCLI provider from sending an attention signal to the server, use the SQLNCLI provider to consume fully any rowsets that the OLE DB consumer creates. "
How do I use the SQLNCLI provider to fully consume any rowsets?
Just curious if anyone out there has had success running a Distributed Transaction on Windows 2000 Professional SP4 lately?
I have come to the conclusion that is is virutally impossible. I am running MSDE on a Win2k Pro machine and trying to run a distributed transaction via linked server and am getting the
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction."
error. Yes I have been scouring the web looking for answers and have implemented pretty much everything I have run accross... and Yes the MSDTC service is started on both machines... BTW: If I run the procedure on XP it works great!!!
I am wondering if anyone even uses Win2k Pro anymore let alone trying to run a distributed tarnsaction on the darn thing.
If anyone out there has it running let me know I would love to chat with you for a minute or two.
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON Begin distributed Tran update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 and DONE = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
I am getting this error :Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
I am trying to configure distributed transaction and XA support using Microsoft SQL Server 2005 JDBC Driver. I have coppied SQLJDBC_XA.dll from XA directory and placed in my sql server binn directory and trying to run the script xa_install.sql from binn directory with command as below :
But I am getting error saying : [DBNETLIB]SQL Server does not exist or access denied. [DBNETLIB]ConnectionOpen (Connect()).
when I replaced local host with the machine name it gives error : [Shared Memory]SQL Server does not exist or access denied. [Shared Memory]ConnectionOpen (Connect()).
where in I am able to test connection from Websphere using the same connection.
Please help some one ....... I am in URGENT need.... I need to enable XA suport for my application to run........
I have a test environment setup consisiting of one SQL Server 2000 32 bit instance on one box and one SQL Server 2005 64 bit edtion on another box. Nearly everything works fine but have found this one snag...
Linked Server name: SQL2005
From the SQL Server 2000 instance in Query Analyzer....
-- this works fine Delete from [SQL2005].myDB.dbo.myTbl
-- this just hangs Begin Tran Delete from [SQL2005].myDB.dbo.myTbl
It doesn't make any difference what provider I use on the Linked Server.
I have tried it on a SQL Server 2000 to SQL Server 2000 configuration and, interestingly, it would only work if I used set XACT_ABORT ON. However, in the SQL Server 2000 to 2005 configuration, this doesn't help.
We have a number of DTS packages that do deletes across the linked server that are failing due to this issue so we're being stopped from continuing with the migration to SQL Server 2005.
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong. Thank you
Hi,On My local SQL server I have added a linked server to another SQLserver (remoteserver) in another Windows NT Domain.When I run this codeselect count(*) from remoteserver.mosaics.dbo.LocationThis works fine.However when I usebegin transactionselect count(*) from remoteserver.mosaics.dbo.LocationIt errors out saying thatThe operation could not be performed because the OLE DB provider'SQLOLEDB' was unable to begin a distributed transaction.New transaction cannot enlist in the specified transactioncoordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].My question is even though I am just reading data from theremoteserver, why does the local transaction get promoted to adistributed transaction.Any help will be grately appreciated.TIA...Rohit
Hi all, I am trying to merge data of 2 tables on different servers with an insert statement.
INSERT INTO SERVER1.db.owner.table select s2.* from SERVER2.db.owner.table as s2 LEFT JOIN SERVER1.db.owner.table as s1 ON s1.key=s2.key where s1.key is null
I got this error. The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
while the select query is giving the result. I have done simmilar inserts on some other tables which worked fine
I have created sp_addlinkedserver. and DTC set on both servers.
Hi All, I am trying to use distributed transaction (using linked Server). But getting the folloing error..
Some one please help...
following is the error...
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session.] OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].
if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..
For reference, here's an example of a distrib query that reads excel...
ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]
i am inserting new record in linked server and i need to get the id (which is of course autonumber) of newly added record. can't i get it using SCOPE_IDENTITY( ) ? SCOPE_IDENTITY( ) seems to be returning null. so SCOPE_IDENTITY( ) doesn't work in distributed transaction?
Hi: When I tried to run a remote proc (with one parameter of date to return a set of records (no problem in return records) and to insert to the local database table A, with following errors:
Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Hello, I've got stuck trying to run distributed transaction on same machine with two SQL instances. () Running transaction from Query Analizer With SET XACT_ABORT ON it works fine. (Without that - it throws an error about unable to begin nested distributed transaction). But from Application (C++, ODBC) it waits for a while and throus an error: 'unable to begin distributed transaction' even SET XACT_ABORT ON is applied before issuing transaction in Stored Procedure.
I’m attempting to insert the result set from a remote query into a local table and I’m getting the following error:
Msg 8501, Level 16, State 1, Line 1 MSDTC on server 'REMOTESVR' is unavailable. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c]. Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
MSDTC is running on both servers. Has anyone ever seen this or have any insight into the cause?
Hi, I have configured a linked server, and i have a procedure which makes an UPDATE in a local table using the data in the linked server.
Specifically, I have a function which checks if a given code exists in a linked server's table. The UPDATE changes the value of a column in a local table, if the function returns 1.
I've run the procedure and it gave an error after a few hours cause a simple conversion error inside the function. I solved the error. After this, the procedure did not work more. It gives me the following message:
Server: Msg 7391, Level 16, State 1, Procedure EXISTEONC, Line 16 The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
(EXISTEONC is the function, and in the line 16 there is an OPENQUERY) Im sure MSDTC is working... i'm lost because i dont know why it worked the first time and not now. Ive also wrote the function again as it was before, but it still doesent works.
I was getting this error message from our Cold Fusion application front end when it was trying to execute one of our stored procedures.The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.
I was a bit puzzled by this because we don't use distributed transactions (at least I don't specifically code them). I did some research online and I found out how to modify the DTC component on the server to have the proper configurations.Then, when trying again we got this error message:
Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SERVERNAME". A nested transaction was required because the XACT_ABORT option was set to OFF.
So, I was able to resolve that as well by changing that option in the stored procedure...Now, there are 3 stored procedures - One does inserts; one does updates; and one does deletes.The actions are being done to a view in a database on another server. The view definition uses a linked server.
The error was/is only happening on the INSERT stored procedure. So, I'm a little baffled as to why it only bombs on the insert stored procedure and not the others. They are all coded in the same fashion..Do distributed transactions work differently if its an insert vs. update or delete? Why is it all of the sudden treating these as distributed transactions when they aren't coded as such?
The code is very simple and looks just like this:
INSERT vw_Name SELECT bla, bla2, bla3 FROM local table WHERE bla bla
And again vw_Name would be a table on another server that we have via Linked Server. It is also a SQL Server (but its SQL 2000).
I'm having problems with distributed transactions. I know this has been descussed a lot before, but i haven't been able to solve my problems anyway.
So, what do I try to do? I try to recieve data from one server (source) to a destination server with an integration services package using a transaction. The source server is a Windows 2003 server running SQL 2000. The destination server is a SQL 2005 Cluster on Windows 2003 Server. Both machines has MSDTC running and allowing both inbound and outbound transactions. I have run DTCPing and DTCTester successfully in both directions.
A bit simplified, my SSIS package looks like this.
Execute SQL Task (Delete old data on the destination server) Data Flow Task
Get data from source using an OLE DB Source Write data to destination using an OLE DB Destination
This works just fine if I in my topmost container set TransactionOption to supported, but if I change it into Required, my package fails with the error message:
Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
Could the fact that the destination is a cluster be a prolem here? I'm pretty sure I have successfully done operations like this in other environments, but without clusters involved.
Oh, one last thing. No firewall what so ever is runnig on or between the servers.
Hello, I've a problem with a software developed in C# with the framework 2.0. This is the error I receive : The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" returned message "No transaction is active.". If I try directly to restart the process, it works fine. Is there someone who can help me ? This is the process 1. In C# --> Call of a Query : select from the linked server (db in sql 2005) and insert into a table SQL 2005 2. In the C# --> using (TransactionScope scope = new TransactionScope()) and insert in a table in SQL 2005 which is link server Thank in advance.
I have this code DECLARE @tempTableName VarChar(50)SET @tempTableName = NEWID() CREATE TABLE #@tempTableName( State Char(2), Billed Money, AslCode VarChar(10))INSERT INTO #@tempTableName EXEC GetRecords '2/1/2008' which gives me this error when I run The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. GetRecords does a select * from a linked server's table. Which is working fine but if I try to do an insert (into temp table) then I get the error. Any help?
There was a distributed trasaction set up and functioning fine, But gave the following error recently.
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions [OLE/DB provider returned message: Distributed transaction error]
Could anyone please help why is this error and how can be rectified.
i have two remote servers connected to each other through the linked servers.
i can run querries or even perform inserts as far as its not inside a transaction but when i am trying to run distribted transaction but it keeps on returning the same msg.
Server: Msg 7391, Level 16, State 1, Line 3 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
i have configured the firewall, made sure the DTC service is started on both and enabled. they both have the same Sp packs, same config.
Hi all,I would like to perform anINSERT INTO LINKEDSVR.dbo.xyz.abcSELECT ... FROM dbo.dfgwhere LINKEDSVR is a linked server on another machine. Both servers arerunning SQLServer 2000 and have the DTC running.When I run this batch from QueryAnalyzer without explicitly usingtransactions, it works well (takes about 5 sec) - however, when Ienclose it usingbegin [distributed] tran/commit tranthe query runs forever.I also tried to use the local server as linked server (loopback) but itdid not work either.Any suggestions?Thanks,Jo
