MSDTC Problem With Distributed View
Apr 30, 2008
Hi Folks.
I have a distributed view, that UNIONs two tables on two different machines. I can run a Select query against the Distributed View, which returns rowset results from both machines.
I am having a problem while trying to execute an INSERT statement against the view. Query is as follows:
insert into Fed_Entity (EntityID,BId,Account_Email,Password,Contact_First,Contact_Last,Address1) values(14,14,'abc@abc.com','abc','test','test','test')
I get the following 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].
-----
Both machines are running SQL 2000 Enterprise Edition and Windows 2000 Server.
I have checked to see that Distributed Transaction Coordinator service is installed and started on both the machines. Any ideas what could be wrong?
Thanks
View 11 Replies
ADVERTISEMENT
May 3, 2006
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
2. Restarted the Sql Server
3. Restarted the machine.
Any help will be highly appreciated.
View 5 Replies
View Related
Nov 8, 2007
Hi All, I am using the CSLA framework for the data access with SQL Server 2005 and we are not using distributed transaction. When we are using this architecture for our local machine it is working fine and our DB is in local machine itself.
But I am getting the following error in our development server and our DB is in the Network. " DataPortal.Fetch failed
(DataPortal.Fetch failed (DataPortal.Fetch failed (Network access for
Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for
network access in the security configuration for MSDTC using the Component
Services Administrative tool.))) ---> DataPortal_Fetch method call failed
---> DataPortal.Fetch failed (DataPortal.Fetch failed (Network access for
Distributed Transaction Manager (MSDT... Procedure Script call failed." Please help me out to get rid of it. Your help would be appreciated.
RegardsGomaz
View 1 Replies
View Related
Mar 13, 2008
Distributed transactions were happily running between our SQL servers and then we installed Windows 2003 Server SP2 and all of a sudden we got this nice error message when attempting a remote data update via linked servers. I couldn't even run "BEGIN DISTRIBUTED TRANSACTION" without getting the error message. The full error message is:
"Enlist operation failed: 0x8004d00e(XACT_E_NOTRANSACTION). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MSDTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager."
Apparently, SP2 disables network DTC access (how nice, I feel more secure knowing that my data can't be updated remotely anymore).
Solution:
From Control Panel | Add or Remove Programs | Add Remove Windows Components
Select "Application Server" and click the "Details" button
Check "Enable network DTC access" and click OK for however many prompts it takes to apply the changes.
****NOTE**** this change WILL SHUT DOWN SQL SERVER so plan accordingly (this would have been handy to know in advance since I ran it in the middle of the workday and everyone got disconnected).
Hope this helps!
View 2 Replies
View Related
Apr 29, 2008
Hi folks,
I am trying to insert records on a distributed view (Fed_Entity) which UNIONs 2 tables on 2 different machines.
insert into Fed_Entity (EntityID,Bid,Account_Email,Password,Contact_First,Contact_Last,Address1) values(99,99,'99@abc.com','abc','test','test','test')
Fed_Entity is a basic distributed view. I am able to successfully run a select statement on this view which consolidates data from two tables.
I haven't yet defined a partition function .. I was just checking to see what will happen if I try to insert.
I have MS SQL SP4 installed. MS-SQL 2000 v. 8.00.2039 on both machines
I get the following error message:
Views referencing tables on multiple servers are not updatable on this SKU of SQL Server.
I thought that Insert/Updates were supported on MS-SQL 2000. Am I wrong?
View 3 Replies
View Related
May 7, 2008
Hi Folks,
I have been trying to setup distributed views. On two machines I was successful. These machines do not have any domain configured. I was running SQL 2000 Enterprise on both machines with Win2K.
The steps I followed were:
1) Add linked servers
EXEC master.dbo.sp_addlinkedserver @server = 'FED2', @srvproduct = 'SQLServer OLEDB Provider',@provider='SQLOLEDB', @datasrc='WEBSERVER1', @catalog='Federated_Bridge'
2) Add linked servers login
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='FED2',@useself='False',@locallogin=NULL,@rmtuser='sa',@rmtpassword='mysapwd'
3) Create table
create table Entity_2(id int not null ,EntityID int not null)
go
4) Add Table constraints
ALTER TABLE [dbo].Entity_2 ADD
CONSTRAINT [CK_11] CHECK ([EntityID] <= 10),
CONSTRAINT [PK_Entity_2] PRIMARY KEY CLUSTERED (id,EntityID )
GO
5) Set Xact Abort on
SET XACT_ABORT ON
GO
..The above steps were repeated on two machines. Step 4 was changed so the check constraint did not overlap.
The steps worked. But now I am trying to do the same thing on two different machines. Both are running Win2K and SQL 2000. The only difference that I can see in the setup is that these new machines are setup under a single domain - ie there is a domain controller - whereas in the first case there is no domain controller.
The error message I get is:
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].
Please help.
View 3 Replies
View Related
Oct 5, 2007
I am setting up 3 Linked Servers (SERVER_A, SERVER_B and SERVER_C) in an isolated local network. They are all running SQL Server 2005 Developer Edition, all on XP SP2. On each server, I have a distributed partitioned view named WAREHOUSE_ALL that basically is the UNION of all WAREHOUSE tables.
I am having trouble in running write (INSERT, UPDATE or DELETE) queries on the distributed partitioned view. The error returned was (run from SERVER_B)
OLE DB provider "SQLNCLI" for linked server "SERVER_A" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 7The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SERVER_A" was unable to begin a distributed transaction.
However, executing a read (SELECT) query ran smoothly without error.
I have done all the steps required as described in the article at http://support.microsoft.com/?kbid=873160 . Note that the only difference between the situation and our situation is the provider (SQLOLEDB and SQLNCLI), which I guess does not important. Unfortunately, the error still comes out.
After reading heaps of other article, I suspected that there is something wrong with MSDTC. As far as I know, all the settings for MSDTC were set accordingly. Then, I ran DTCPing - http://www.microsoft.com/downloads/details.aspx?FamilyID=5e325025-4dcd-4658-a549-1d549ac17644&DisplayLang=en and the error returned was
DTCping log file: C:Documents and SettingsAdministratorDesktoplSERVER_B2496RPC server is ready
Please Start Partner DTCping before pinging
++++++++++++Validating Remote Computer Name++++++++++++
Please refer to following log file for details:
C:Documents and SettingsAdministratorDesktoplSERVER_B2496.log
Invoking RPC method on SERVER_C
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
-->RPC pinging exception
-->5(Access is denied.)
RPC test failed
And here is the log file:
Platform:Windows XP
IP Configure Information
Host Name . . . . . . . . . : SERVER_B
DNS Servers . . . . . . . . : 129.78.99.2
Node Type . . . . . . . . . :
NetBIOS Scope ID. . . . . . :
IP Routing Enabled. . . . . : no
WINS Proxy Enabled. . . . . : no
NetBIOS Resolution Uses DNS : no
Ethernet adapter {4404F3CB-F4B7-4990-912C-E69721C885B1}:
Description . . . . . . . . : 3Com EtherLink XL 10/100 PCI TX NIC (3C905B-TX) #2 - Packet Scheduler Miniport
Physical Address. . . . . . : 00-01-02-85-B8-A9
DHCP Enabled. . . . . . . . : no
IP Address. . . . . . . . . : 172.19.102.35
Subnet Mask . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . : 172.19.102.250
DHCP Server . . . . . . . . : 255.255.255.255
Primary WINS Server . . . . : 0.0.0.0
Secondary WINS Server . . . : 0.0.0.0
Lease Obtained. . . . . . . : Thu Jan 01 00:00:00 1970
Lease Expires . . . . . . . : Thu Jan 01 00:00:00 1970
++++++++++++lmhosts.sam++++++++++++
++++++++++++hosts ++++++++++++
127.0.0.1 localhost
++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SERVER_B
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
10-05, 17:10:54.769-->Start DTC connection test
Name Resolution:
SERVER_C-->172.19.102.36-->SERVER_C
10-05, 17:11:09.781-->Start RPC test (SERVER_B-->SERVER_C)
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
-->RPC pinging exception
-->5(Access is denied.)
RPC test failed
I guess it could be due to port problem, which I have already opened in the Windows Firewall. There is one article which is confusing me -> Update to automatically open port 135 in Windows Firewall when a TCP or a UDP RPC server registers with the endpoint mapper at http://support.microsoft.com/kb/838191 (This article shows automatic opening of port 135!)
Please help me. Thanks.
View 8 Replies
View Related
Sep 4, 2015
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
View 4 Replies
View Related
Feb 29, 2008
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 11 Replies
View Related
Jul 28, 2000
Is it true that you only have one instance of MSDTC on an active/active Cluster SQL configuration?
All the installations I have seen so far will only allow the MSDTC resource to be under on resource group (usually one of the SQL groups...
Can you all that have worked with cluster respond.
View 2 Replies
View Related
Apr 21, 2004
Trying to locate information on MSDTC. Is this "needed" to run SQL Server? That is, if this part of the installation is deleted, will SQL Server still function? Also, does anyone know if this is a crucial tool needed by Veritas Volume Manager or Windows Disk Manager?
If anyone knows of a link to this information, I'd appreciate it. My searches come up with lots on information on MSDTC, but nothing that answers my specific questions.
Thanks for your time.
View 1 Replies
View Related
Mar 21, 2006
When i do a BEGIN TRAN to a SQL server sitting on Windows 2003 Server from a SQL Server Sitting on Windows 2000 Server,
the Transaction hangs and if I try o kill it the Transaction is in ROLLBACK State.
I tried setting the Properties for the MSDTC and restarted the Windows 2003 Server but in vain
It still hangs
Did any one come across this problem?
Thx
View 1 Replies
View Related
Oct 16, 2006
I keep getting this error on my application server...what does it mean?The description for Event ID ( 0 ) in Source ( ODBC ) cannot be found.The local computer may not have the necessary registry information ormessage DLL files to display messages from a remote computer. Thefollowing information is part of the event: Failed to enlist in DTC:SQL state 37000, native error 8501, error message [Microsoft][ODBC SQLServer Driver][SQL Server]MSDTC on server 'PROSQL' is unavailable..any help would be appreciated...thanks-Jim
View 1 Replies
View Related
Jul 20, 2005
I'm using msde. On the little icon in task tray, you have under currentservice, a choice of mssqlserver, and another choice is msdtc. What ismsdtc? And what is sqlserver agent?
View 1 Replies
View Related
Aug 20, 2007
The resource msdtc is down. Every time, it works pretty well , after we restart the servers(node1 and node2). But, about half a hour later, the problem is there. We reintalled msdtc. It didn't work out too. msdtc and sql server is on a cluster. Can anyone help me out?
View 1 Replies
View Related
Oct 6, 2006
Hi,
I am running SQl 7.0 (SP4) on Windows XP Professional (SP2). Whenever I try an insert/update type of activity, the System returns the following message :
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'SERVER' is unavailable.
However, all the required services including MSDTC are running on the System.
Please help ASAP.
Thanks & Regards - Rajesh Pathak
View 3 Replies
View Related
Nov 3, 2006
I have a class that use a TransactionScope object for make make two operations into a database. The problem is that in a computer works well but in another I receive a error message: "MSDTC is not running in .SQLEXPRESS". I had watched the services in the computer and the "Microsoft Distributed Transaction Coordinator" is running.
Does somebody know what is happening?
Thankyou and sorry for my English.
View 2 Replies
View Related
Nov 4, 2003
I am selectively insert data from serverA databaseX to serverB databaseX.
(serverA is linked server on B.)
I could insert parent and child tables. But get error when insert to grand-child tables:
Server: Msg 8501, Level 16, State 3, Line1
MSDTC on server B is unavailable.
Don't think I need to turn on MSDTC on B. But I could copy and paste from source A server to target B server. Confusing on some table could insert while others with error....
thanks
-D
View 2 Replies
View Related
Dec 14, 2004
I have an ugent situation. Due to a failure we had to build a new db server for an application and this was done succuessfully. The application requires DTC and it was not intalled when we install SQL on the server.
How do I install DTC? Did I miss an obscure option during SQL install?
Sidney Ives
Database Administrator
Sentara Healthcare
View 2 Replies
View Related
Apr 12, 2004
Hi,
I recently moved & upgraded a database from 7.0 to 2000 that makes use of linked servers. After upgrading, a number of queries started failing with the error below. These linked servers with this problem either had a stopped MSDTC or were behind a firewall. I've read the articles on how to configure MSDTC to work through a firewall, but before I do this I'm wondering why this is even happening. There were no problems when the database was at SQL 7.0. Does anyone know why sql 2000 would need to use MSDTC, but SQL7.0 would not? I don't need to use a distributed transaction, so ideally I'd like to somehow tell SQL not to use MSDTC at all. Any ideas?
Thanks
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].
View 2 Replies
View Related
Jul 20, 2005
The following code:DECLARE @tsql varchar(1000)CREATE TABLE #Test (...)SET @tsql = 'SELECT * FROM OPENQUERY(MyDataSource, ...)'INSERT INTO #Test EXEC(@tsql)generates the following error message at the insert statement:Server: Msg 8501, Level 16, State 3, Line 4MSDTC on server 'MyComputer' is unavailable.Why?Thanks,Tony PerovicCompumation, Inc.
View 2 Replies
View Related
May 4, 2007
I have a SSIS package in which there are lot of Sequence Containers. Each sequence container has two Execute Sql tasks which brings 2 values, and if the comparison of the value is true, it runs a data flow task. Comparison is done using Expression.
If any of the sequence container fails the comparison, I wanted whole package to rollback so I had to use Transactions. I set "Required Transaction" on the package and "Supports Transaction" on each Sequence Container and Tasks respectively.
Now that I am trying to run the package, it runs few sequence containers fine and on few it gives this error.
[Execute SQL Task] Error: Executing the query "SELECT ISNULL(MAX(ClaimBillTypeKey), -1) ClaimBillTypeKey FROM dbo.Dim_ClaimBillType" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Also, this is not consistent, one time one Seq. Cont will fail and next time it will run fine but something else will fail. What is going on????
View 2 Replies
View Related
Feb 3, 2008
Hi all,
I have set up two server and also created linked server between server1 to server2
I could select from there just fine but when i try to run tirgger on table server1 which updates in server2 it gives me error following:
OLE DB provider "SQLNCLI" for linked server "Server2" return message "No Transaction is active"
MSG 7391, level 16, state 2, Procedure servertrigger, line 34
the operation could not be performed because OLE DB provider "SQLNCLI" for linked sever "SErver2" was unbale to begin a distributed transaction.
I have configured on both server exactly same:
with all the option:
MSDTC > Security Configuration:
Network DTC Access > TRUE
Allow Remote Clients > TRUE
Allow Remote Administration > TRUE
Allow Inbound > TRUE
Allow Outbound > TRUE
Mutual Authentication Required > TRUE
Enable Transaction Internet Protocol (TIP) Transactions > TRUE
Enable XA Transactions > TRUE
Account: NT AuthorityNetworkService
on both server..
On server2 i tried remote proc trans to on and off but it did not help.
What am i missing please Help!!!!!!!
View 1 Replies
View Related
Mar 18, 2008
Hi,
We got this error frequently "
Commit of internal MSDTC transaction failed: Result Code = 0x8004d019
How to solve this issues.
Regards,
S.Balavenkatesh
View 1 Replies
View Related
Oct 19, 2006
Hi all
I got the error below after run the query:
INSERT TABLE1 -- ran at SERVER1
EXEC [SERVER2].DB2.dbo.spInformation
@VR_STARTSHIFT = '10/10/2006 06:00:00',
@VR_INCDATE = '10/10/2006 14:00:00',
@VR_STATION_ID = 221
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].
Server1 has a linked server to Server2 (this linked server is working because i can run the SP without the insert).
The Server1 is under WIN2K Server and Sever2 is under WIN2003
The MSDTC is installed and is running in both servers.
How can I fix that?
Thanks in advance
Manuel Rodrigues
View 2 Replies
View Related
Oct 22, 2007
hi all....active/active. windows 2003 sp2. two nodes. two instances, each on a separate virtual server. my msdtc resource is in the cluster group. when i have the cluster group on one node, the sql instance that is running on the other node can't see DTC. Makes sense since the service is not up, but is this a problem? Seems strange to have a red stop sign on my dtc icon in sql management studio. I suppose I won't be able to run Distributed Transactions on that node, which kinda stinks. Wondering if anyone has seen a workaround.
View 3 Replies
View Related
Sep 26, 2007
I have setup a trigger to update records on a linked server, the link works fine and I can query tables on the remote server. However when my trigger activates I get the following error
The operation could not be performed because OLE DB provider SQLNCLI for linked server "NHS" was unable to begin a distributed transaction.
OLE DB provider SQLNCLI for linked server "NHS" returned message
"No transaction is active"
I've followed a microsoft KB article and installed MSDTC on both servers, what else do I need to do?
Thanks
View 20 Replies
View Related
Aug 28, 2006
HelloI had a question reagrding MSDTC usage. I know that MSDTC will enlisteach connection transactions as it's own (take ownership) andcommit/abort accordingly.However I'm not sure what would happen where I have a stored proc withinput parameters and 2 Delete commands on 2 different tables. In thiscase, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2ndaborts ? In the script for the Stored Proc, there is no checking for@@ERROR value. There are just DELETE statement and a plain RETURN.thanksSunit
View 1 Replies
View Related
May 3, 2007
Hello,
As newbie in server country I try a course example to test a distributed transaction accessing both SQL Server 2005 and Sql Server 2005 Express.
But I get an error message saying:
--->MSDTC on server is not available
I Internetted somewhere that MSDTC stands for TransactionManager service.
If I look in the 'Services' list I do not see something like MSDTC, so I think it is not installed or started.
How do I install or activate the MSDTC service?
Thanks in advange,
Henk
View 5 Replies
View Related
Aug 9, 2006
Today we installed on MS Cluster (W2k3) and SQL Server 2000 a MSDTC Service. The installation was succesfull, but wenn we try to move the group, then it failed. Because we have only 2 Disks on the cluster one for the Quorum, the other for the Sql Server. We installed the MSDTC as a Resource on the Quorum Disk. The other Thing, wenn we start the firts node all is running on the first node, but we cannot move. Wenn we start the second node all is running on the second node, but we cannot move the Group which contain the Msdtc resource.
Who has a solution to this problem??
Thanks to all
View 1 Replies
View Related
Apr 10, 2007
High level question: Is there a way to rollback a transaction without failing a task?
Here's the scenario:
A single foreach loop contains a sequence container. The sequence container has its TransactionOption set to "Required." All activity happens within the sequence container. This is so that each iteration of the foreach loop is in its own transaction space. Now the catch. In the sequence container I run a few tasks, but more importantly, at the end (final task before the next foreach iteration) I run a Script Task to compare the values of two variables to see if they match. If they do, then all is well; move onto the next iteration. If they don't (and this is where my problem lies), then I need to roll back the current transaction without halting the foreach loop's iterations.
Is the best idea/approach to increase the value of MaximumErrorCount for the parent/package and set the Dts.TaskResult to Dts.Results.Failure, or is there a way in the script task that I can roll back the transaction and set Dts.TaskResult = Dts.Results.Success??
Thanks,
Phil
View 5 Replies
View Related
May 12, 2006
Simultneous transactions of parallel packages not functioning.
The following error ocurs:
* The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction
with error 0x8004D00A "Unable to enlist in the transaction.".
* The AcquireConnection method call to the connection manager "<connection manager name>"
failed with error code 0xC0202009.
* Aborting the current distributed transaction.
The packages work fine when they are executed separately or without transactions, but
when the TransactionOption of the parent package is set to "Required", and all the
containers in the package are set to "Supported".
I guess the problem is with distributed transaction, but not able to resolve. Please
suggest a solution if someone has come across the same problem.
Thanks in Advance
!!!
View 2 Replies
View Related
Oct 24, 2007
Hello,
When I run sp_who2, the blkby col shows spid -2.
From doing some reading I understand this is caused by SQL waiting on MSDTC.
If I look at MSDTC running transactions I can see 2 transactions, the transaction are not mark as indoubt so I cannot commit, abort or forget them.
If I stop ALL the Processes that are using the DB, eg IIS and a 3rd party workflow engine, these transactions do not go away.
Anyone got a clue?
If I restart MSDTC, the blocks go away in SQL but this is not a solution to the problem.
All MSDTC gives me is the ID for the transaction, can I get more info on whats happening from this ID.
Thanks
Steve
View 1 Replies
View Related
