Distributed Transaction Problem
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.
What's happening here ?
Thanks in advance.
View Complete Forum Thread with Replies
Related Forum Messages:
The Microsoft Distributed Transaction Coordinator (MS DTC) Has Cancelled The Distributed Transaction.
We have a test db, a staging db and a live db. I have a stored procedure that runs fine on test and staging, but throws the following error on live. The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction. The stored procedure uses linked servers and a transaction. We're using the following transaction code in the stored procedure BEGIN BEGIN TRANSACTION BEGIN TRY --- procedure stuff here --- COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT SET @ErrorSeverity = ERROR_SEVERITY() SET @ErrorNumber = ERROR_NUMBER() SET @ErrorMessage = ERROR_MESSAGE() SET @ErrorState = ERROR_STATE() IF @ErrorState = 0 SET @ErrorState = 1 RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity, @ErrorState, @ErrorNumber) IF XACT_STATE() < 0 ROLLBACK TRANSACTION END CATCH END 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?
View Replies !
Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
Hi All 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
View Replies !
SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
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. Please help me it's very urgent.
View Replies !
Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
Hi all, I've already read the thread by the same topic, but I found Kevin Yu's comment a bit confusing. I don't know how to solve my problem. I have two stored procedures in sql server. First one inserts into a bunch of tables so it has Begin Transaction and Commit in it. Second one just inserts into one table so it doesn't have the begin transaction statement in it. From my data access class in c#.net, I have a function which calls both of these stored procedures from within a "using transactionscope" block. So when it makes the call to the first stored procedure, it returns fine, but when it calls the second one, I get the above error. I think it has something to do with me declaring transactionscope in my application code, and then calling a stored procedure which also has its own begin transaction. But I don't know how to fix this. Any ideas? Thanks
View Replies !
Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction
I'm using TransactionScope object and it is giving me problems. The situation is like this. I've 2 tables in sql server express, say student and courses and other one studentcourse for courses a student has opted for. An aspx page displays list of users in listbox, when user chooses student, it fetches courses he has opted for and in the checkedlistbox (for courses) these courses are checked. Now when uses makes any changes to courses and clicks update button, i've two functions at backend first one removes the courses which were previously selected but now not selected in postback, next it inserts courses which was not selected first but now are in this postback. I use following code: protected void Update(object sender, EventArgs e) { /* get selections, existing courses etc. */ using(TransactionScope tscope=new TransactionScope()) { try { RemoveCourses(/*Student*/ s, /*IList*/ oldCourses, /*IList*/ newCourses); AddCourses(/*Student*/ s, /*IList*/ oldCourses, /*IList*/ newCourses); tscope.Complete(); } catch(Exception ex) { lblMessage.Text=ex.Message; } } } If any exception occurs during update and page is reload or I go to any other page where some data is fetched from sql server I get the exception: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction Please help!
View Replies !
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. 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?!
View Replies !
Why Transaction Gets Promoted To Distributed Transaction
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
View Replies !
Distributed Transaction....
I have the following error:Server: Msg 7391, Level 16, State 1, Line 1The 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 inthespecified transaction coordinator. ]I have DTC services running in both servers, SET XACT_ABORT clause, ipaddress in hosts file, there´s not a firewall between servers,The only difference is sql server version, server A have Personaledition, server B have Standard edition Sql 2000...what im doing wrong???sorry for my english, but its not my native language...
View Replies !
Distributed Transaction
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. any help will be greatly appreciated
View Replies !
Distributed Transaction
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].
View Replies !
Why A Distributed Transaction Here?
I want to execute a remote stored proc and put the results in a local table. MachineB is a linked server on MachineA. I can do this succesfully on MachineA.... EXEC [MachineB].master.dbo.sp_helpdb I can also do either of these without any trouble.... SELECT * INTO local_copy FROM [MachineB].master.dbo.sysdatabases or INSERT INTO local_copy(name, dbid) SELECT name, dbid FROM [MachineB].master.dbo.sysdatabases ...assuming in the 2nd case that table 'local copy' has the appropriate matching columns. But I CAN'T do this..... (and the target table is correct) INSERT INTO local_copy EXEC [MachineB].master.dbo.sp_helpdb I get the error ""The transaction manager has disabled its support for remote/network transactions." or sometimes "The partner transaction manager has disabled its support for remote/network transactions." Whether or not the word 'partner' appears may be OS-level dependent. I have SET IMPLICIT_TRANSACTION OFF set. What I don't get is why INSERT ... remote-SELECT is not a distributed transaction and INSERT ... remote-EXEC is It's probably not going to help to use component services to allow remote transactions, because I'm not going to be able to do inbound authentication. (different domains wo/trust) And inbound authentication WILL be set on some targets because it HAS to be set on clusters. I don't like this... it's Microsoft forcing Windows authentication down our throats, even that that's not practical in lots of scenarios - such as when some participating nodes are some OS other than Windows. So for me the solution seems to be for this NOT to be a distributed transaction. Why should it be, when INSERT...remote-select doesn't have to???? Besides, only one machine is getting updated. No need to coordinate changes on multiple machines.
View Replies !
Distributed Transaction
Hi, I have a quick question about distributed transaction. We currently use a stored procedure to insert data in two tables, one of the table is located on a SQL 2000 server and the other one on an Oracle server (Linked server). I would like to ensure data integrity between the two server. For some reason i'm not able to retreive an error code from oracle when the transaction fail... How could the SP Rollback the entire transaction on SQL and Oracle side? Here is an example of the SP we use: CREATE PROCEDURE PLB_1_sp (@Value1 INTEGER, @Value2 INTEGER) AS BEGIN SET XACT_ABORT ON BEGIN DISTRIBUTED TRANSACTION INSERT INTO SQL1 VALUES (@Value1,@Value2) IF (@@ERROR <> 0) GOTO ERROR_HANDLER INSERT INTO linkedSvr..ORACLE.Table1 VALUES (@Value1,@Value2) IF (@@ERROR <> 0) GOTO ERROR_HANDLER COMMIT TRANSACTION ERROR_HANDLER: ROLLBACK TRANSACTION SET XACT_ABORT OFF END Thanks for your help
View Replies !
Distributed Transaction
I got following error when i tried to run a procedure which has a distributed tran in it: 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] both my local and linked server has DTC service running, sql7 with sp1 any idea why it still give me this error? Thanks!
View Replies !
Distributed Transaction
Hi again, I do not know when to use Distributed Transaction. Would you please give an example about its usage or give a tutorials about this ? Thanks
View Replies !
SCOPE_IDENTITY( ) In Distributed Transaction [:S]
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?
View Replies !
Distributed Transaction Problem ?
Hi,MSSQL 2000 booth servers. Booth running DTC.Now, the client application, is starting in DB1 a procedure.The connection open to db, is within the transation opened fromclient.In the SP, I'm starting the transaction as well, with savepoint.Unfortunately, I have to get data, from other linked server (DB2).While performing query, the client application is recieving followmessages:If the query on DB2 is after a savepoint------Cannot go remote while the session is enlisted in a distributedtransaction that has an active savepointIf the query, is before starting transaction in sp (save point aswell)-----The operation could not be performed because the OLE DB provider'SQLOLEDB' was unable to begin a distributed transactionNow ... I was trying to set xact_abort to on, before, and within thetransaction in SP.But doesynt matter, it will not do.While calling an SP from QA, it goes threw, with query on DB2 beforetransaction inside SP.For me, it is an information, that the problem which I get, is becausethe client, while connecting to DB, is using it's connection withintransaction.My question is: how can I, run this query on DB2, without affectingchanges on the client side ?Below, small layout of this transactions stackBEGIN TRANSACTION_1 (client side)BEGIN TRANSACTION_2 (inside SP)SAVE TRANSACTION_2COMMIT OR ROLLBACK _TRANSACTION_2COMMIT OR ROLLBACK _TRANSACTION_1 (client side)where and with what option, put this query ?Best regards - Matik
View Replies !
Distributed Transaction Issue
I have two database servers and I have a stored procedure which will do a distributed transaction. The procedure runs on serverA and gets the data from serverB onto serverA. But the procedure is getting failed and I am getting the following error. The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000] (Error 7300). The step failed. So when I checked the properties of my serverA on the connections tab Enforce Distributed Transactions(MTS) is not checked. Is this causing the above error or do I need to anything else to run my SP successfully. Thanks.
View Replies !
Distributed Transaction Failure
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? The code that I’m trying to run is: create table msver ( [Index] int, [Name] varchar(30), Internal_Value varchar(20), Character_Value varchar(512) ) insert into msver exec ('exec [REMOTESVR].master.dbo.xp_msver') Note that the remote query works fine. Thanks! Eric
View Replies !
Distributed Transaction Error, Need Help Please!!
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. Thanks a lot...
View Replies !
Distributed Transaction Error
Hi all, I encountered this problem when tried to perform a distributed transaction using link server. My aim was to connect 2 data base servers using TCP/IP and transfer data. I was able to run single queris using "linkserver.catelog. owner.table". But this message was shown when i tried to execute a stored procedure. The message was "The operation could not be performed since OLEDB provider SQLOLEDB doesn't support distributed transactions. [OLE/DB distributed transaction error]" The documentations about the distributed transactions showed that SQLOLEDB do support distributed transactions. Please forward your valueble suggestions. Thanks in advance, Sabu
View Replies !
Problem With Distributed Transaction
A stored Procedure when used to insert data in one table runs fine but fails when used with different table giving the message below. It is using linked servers to get to the tables -------------------- 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. ] ----------------------------
View Replies !
Msg 7391 With 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]. SET XACT_ABORT ON BEGIN DISTRIBUTED TRAN insert tblA(column1, column2, column3) exec [remoteServerB].DBX.dbo.usp_XYZ '03/28/2006 ' COMMIT TRAN Both remote SQL and Local SQL are SQL2000 with sp4 and both Distributed Transaction Coordinator are on. Any idea? :rolleyes: thanks David
View Replies !
BEGIN DISTRIBUTED TRANSACTION
Hi I am using MSDTC and RPC to transfer data from one server to other. It was working fine since last day but today my procedure is getting stuck at BEGIN DISTRIBUTION TRANSACTION command. Any idea why? Manish
View Replies !
Distributed Transaction Problem
Hi! I am working on a distributed system managed from a particular server. I am getting a problem when using a particular database server. The error in the event viewer is- MS DTC is unable to communicate with MS DTC on a remote system. MS DTC on the primary system established an RPC binding with MS DTC on the secondary system. However, the secondary system did not create the reverse RPC binding to the primary MS DTC system before the timeout period expired. Please ensure that there is network connectivity between the two systems. Error Specifics:.cso.cpp:1841, CmdLine: C:WINNTSystem32msdtc.exe, Pid: 540 This error doesn't come when I use some other database server. I looked at all possible configuration parameters I could think of but there doesn't seem to be anything wrong with this server. Could someone please suggest what could be going wrong or where can I look for the problem. Thanks! - Manoj
View Replies !
DTC ... Distributed Transaction Update.... Help
Hello. I am trying to update a table on another server which is configured as a linked server on the server issueing the update. I can select data from the linked server no problem. But when I issue the update statement within the begin distributed tranaction I get the following. I am getting the following message Server: Msg 7392, Level 16, State 2, Line 1 Could not start a transaction for OLE DB provider 'SQLOLEDB'. [OLE/DB provider returned message: Cannot start more transactions on this session.] Has anyone had any similar problems with DTC.... I am getting ready to call Microsoft. But wanted to avoid the charge. Thanks John
View Replies !
Distributed Transaction Error
Hi All, I am getting the following error when I execute a Stored Procedure which has some distributed transactions in it. The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. I have enables the MSDTC serveices on both the SQL servers according to the steps given in this site http://support.microsoft.com/default.aspx?scid=kb;en-us;839279#appliesto But, the same stored procedure is working fine from one SQL server and it is not working on the other SQL server. Can any one help me in resolving this issue? thanks in advance. Regards, Vijay
View Replies !
Distributed Transaction Problem
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. regards Andreas
View Replies !
How To Prevent A Distributed Transaction?
I have a stored proc that performs some validations and returns a result set. I catch the result set in a calling procedure as follows: declare @tblErr (ErrType varchar(20), ErrMsg varchar(255)) insert @tblErr (ErrType, ErrMsg) exec dbo.spMyProc ...(args) Within the def of spMyProc there is a cross-server call: create procedure dbo.spMyProc (...params...) as begin declare @tblErr (ErrType varchar(20), ErrMsg varchar(255)) ...(do checking, inserts to @tblErr)... -- check other server exec @n = otherserver.mydb.dbo.spCheck ...(args) if @n != 0 insert @tblErr values ('someerr', 'somemsg') ...etc. select ErrType, ErrMsg from @tblErr end Apparently, the implicit transaction of the outer Insert statement is causing a distributed transaction to be started. There is no 'begin tran', distributed or non, and the foreign sp has no data-modifying statements. The call to spMyProc, if done from a query window, works fine, returning the desired resultset. When called from the outer proc, the call fails because the servers are not configured for distributed transactions. When performed without the cross-server call, using a copy of the remote db attached to the local server, the outer proc works fine. I do not want a distributed transaction. How can I prevent one? The local server is 2005 Standard on Server 2003, and the linked server is SQL2000 on Windows 2000 Server.
View Replies !
Unable To Begin A Distributed Transaction
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?
View Replies !
Distributed Transaction Takes Far Too Long
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
View Replies !
Unable To Begin A Distributed Transaction
Having a SQL Server 2K (SP3a) with a link to another SQL Server 2K (noservice packs), distributed transactions works as expected most of thetime.But occasionally the MSDTC(s) seems to fall in a state of failure,causing the following error when involving distributed transactions:"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 inthe specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a]."The only way out of this state is to stop and start both of the SQLServers (stop/start of the DTC's doesn't help).Is this a common problem?Guess I will be recommended to install SP on the server withoutservice packs, but will this positively solve the problem describedabove?Could general network failures cause the error state described above,and if so - is there a way to make the MSDTC's survive or recover fromthe error situation automatically?--Morten Haugen
View Replies !
Unable To Begin A Distributed Transaction
I am using MSDASQL provider to create a linkedServer and is using Distrubuted Transaction in the application...but it is throwing out the following error... Server: Msg 7391, Level 16, State 1, Line 1 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]Distributed transaction error] OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a]. What keeps me guessing is that the same code worked in the development environment and had no problems in starting the Distributed Transaction... Note: DTC is running on both the server and I am not using a loop back server!!!! Please Help
View Replies !
Distributed Transaction Error - Help Urgent
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.
View Replies !
Distributed Transaction Completed Error.
Hi, I have an ASP application which uses a COM object to read/write/delete/update data from a SQL Server 2000 (SP3). In the ASP page on the first line I have: <%@TRANSACTION=REQUIRED%> and for commiting or aborting the transactions I use ObjectContext.SetComplete or ObjectContext.SetAbort The application works fine but under heavy use it sometimes throws the error: [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. The error code is -2147217900 If I do not use MS DTC in the ASP page, I don't get this error any more but also I don't have the transaction integrity (sometimes I have to update also some other database). Any help would be greatly appreciated. Sorin
View Replies !
Distributed Transaction Coordinator Will Not Start.
SQL Server version 7.0 (Service Pack 1) When trying to start the Distributed Transaction Coordinator through Enterprise Manager, the following error is returned: An error 1068 - (The dependency service or group failed to start) occurred while performing this service operation on the MSDTC service. Any ideas?
View Replies !
Distributed Transaction Using Linked Server
Hi, 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. Thanks in advance John Jayaseelan
View Replies !
Distributed Transaction Error From CLR Trigger
I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message: Command attempted: if @@trancount > 0 rollback tran (Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1) Error messages: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391) Get help: http://help/7391 The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391) Get help: http://help/7391 A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger': System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. Changed database context to 'pims'. OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ". System.Data.S (Source: MSSQLServer, Error number: 6549) Get help: http://help/6549 Any thoughts or direction appreciated Richard
View Replies !
Star Schemma - Distributed Transaction
I'm designing a DW, and i have some doubts relative to the Distributed Transaction when modeling a star schemma. My problem is: I have a main dtsx package in wich i call all the child packages in order to create the (Fact and Dimension Tables). (1) First i have several child packages that create and populate all the Dimension Tables (with the latest values from the relational DB). (2)Then i have several child packages that create all the fact tables, in this process i use the surrogate keys from the dimension tables (obtained in step 1). The problem here is , " How do i use the multiple transaction ?" , if i put a "required" Transaction Option on the parent package, then after calling the child packages that creates the dimension tables. The values are not commited, so they are not available when i later execute the childs packages related with the fact tables. How can i use transaction when modelling a star schemma, in order to have a full roll back or a full commit in all tables (Dimensions and Fact Tables). Thanks
View Replies !
Poison Message, Distributed Transaction
Hi! I have crl stored procedure with distributed transaction in it. I really need such transaction. When a poison message occurs 5 time my queue is turned off. I've read about handling poison messages in msdn (save transaction and rollback part of it), but it works only with local transaction. What should I do? An important point is that messages shoold be processed in right order, I can't receive message and put it to the end of queue. I want to try process poison message constantly. I doesn't want to stop receiving messages in that queue. Thanks. kostya
View Replies !
Distributed Transaction Coordinator On SQL Server Cluster
Please, what is the MS DTC and why do I need to use it on a Clusterconfiguration?How SQL Server works with it? How SQL "see" / reach it?If I'm using a cluster active / active how does it work? (I'm askingbecause I have installed DTC as active / passive).I know that I need to install it, but do not know why.Regards,Marcio
View Replies !
Unable To Begin A Distributed Transaction (Msg 7391)
I am receiving the following error when I run a stored procedure:Server: Msg 7391, Level 16, State 1, Procedure spXXXXThe SP inserts data into a local (sql 2000) table from a remote sql2000 database. The SP works fine until I add a trigger to thedestination. The contents of the trigger do not appear to matter, as Ihave tried removing everything but one line, which is just a declarestatement. Updating the table with the trigger (even the full trigger)in place does not cause an error. Running the SP without the triggeron the table does not cause the error.I have the exact same setup on another server. Both the SP and thetrigger are IDENTICAL. This setup works on one server, but not theother. The only difference I can see is that the non-working server isWindows 2000 Server, while the working server is Windows 2003 Server.Any idea what's going on here?Thanks in advance.Eric
View Replies !
Distributed Transaction Problem On Remote Server
I am getting a problem in executing this distributed query CREATE PROCEDURE [sp_ec2fetch] AS set XACT_ABORT on begin distributed tran select * from OPENDATASOURCE( 'SQLOLEDB', 'Data Source=[ip];User ID=[uid];Password=[pass]' ).dml.dbo.act where code like '4010101000' commit tran GO When I run it with a local LAN IP it works fine, but when I use a public ip my analyzer hangs and doesnt give me any output. I am using a dedicated dialup 64kbps connection. I have tried this query without begin distributed trans and commit trans and it worked fine, may be there are extra overheads with transactions, if so let me know As an alternate I have tried registering server with sp_addlinkedserver and then executing a query with direct server reference, but had the same problem with it.
View Replies !
|
TRACKER
