MSDTC Service and DTS problem

Last Post 22 Aug 2007 06:08 AM by sqldba1976. 11 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Amber_Robertsona
New Member
New Member

--
25 Jan 2007 05:48 AM
We have created a local DTS package which connects to 2 different servers in SQL Server 2000. When I try to execute the DTS package I keep getting the error message “Microsoft Distributed Transaction Coordinator (MSDTC Service) is not available". I have checked both servers and MSDTC service is running on both. Does anybody know what is causing this error message?


Any suggestions are much appreciated.


Amber
Amber_Robertsona
New Member
New Member

--
26 Jan 2007 12:01 AM
Thank you for the suggestion, however after updating the the msdtc tab as recommended unfortantley hasn't improved it. Is there anything else I can check?

Appreciate your quick response.


Regards,
Amber
Amber_Robertsona
New Member
New Member

--
26 Jan 2007 04:00 AM
I am now executing the package logged into destination server referenced in DTS package. Now I am not getting the same error message but getting "Beginning of package Transaction Failed when executing step.....A connection with the transaction manager was lost". I successfully pinged the other server, and both connections are using SA login credentials.

Any ideas appreciated.


Regards
Amber
Amber_Robertsona
New Member
New Member

--
26 Jan 2007 05:50 AM
I believe we are using TCP/IP by default in sql client network utility. Do you think this could be related?

Regards,
Amber
Amber_Robertsona
New Member
New Member

--
29 Jan 2007 01:02 AM
Russell, thank you for the further suggestions however the error message still keeps appearing. Not sure if it is helpful but in the DTS package properties; Use Transaction, Rollback transaction on failure and join transaction if present are ticked. We updated the DTS package so it is using a single transaction to allow full rollback if any steps fails. Do you know of another more reliable way of integrating roll back into a DTS package if any steps fail?

Appreciate all the help you have given so far.


Regards,
Amber
Amber_Robertsona
New Member
New Member

--
29 Jan 2007 01:02 AM
Russell, thank you for the further suggestions however the error message still keeps appearing. Not sure if it is helpful but in the DTS package properties; Use Transaction, Rollback transaction on failure and join transaction if present are ticked. We updated the DTS package so it is using a single transaction to allow full rollback if any steps fails. Do you know of another more reliable way of integrating roll back into a DTS package if any steps fail?

Appreciate all the help you have given so far.


Regards,
Amber
Amber_Robertsona
New Member
New Member

--
30 Jan 2007 06:30 AM
Thank you for all the suggestions provided however we changed the DTS package to not include Use Transaction but included a step to rollback data.


Regards
Amber
RJoys
New Member
New Member

--
09 Mar 2007 09:36 PM
I have been grappling with the same error since the past few days and am at my wits end. I am copying data from a SQL Server source (using the Microsoft OLE DB Provider for SQL server) to an Oracle source (using the Microsoft ODBC driver for Oracle).
I have checked the box 'Use Transactions' in Package Properties.
I have also clicked on 'Join Transactions if present' in the Workflow properties of each task.

When I execute the package however, I get the error,
Error source : Microsoft OLE DB Provider for ODBC drivers
Error Desc : Connection 'Microsoft ODBC Driver for Oracle' for Task 'DTSask_DTSDataPumpTask_1' does not support joining distributed transactions or failed when attempting to join. Unable to enlist in the transaction.
[Microsoft][ODBC driver for Oracle]Driver not capable

I have used the 'Data Transform Task' and I have 3 simple select statements.
The first two are selecting values from a SQL Server table into local variables and the 3rd select returns values from a table.
My 'Transformations' is a simple Source table column -> Destination table column copy.

I do need to use a Transaction since I would like to rollback the package if any one of the 4 tasks fails.

Any help on why I am getting this error or what I could do as a workaround would greatly help.
Thanks,
Renu
sqldba1976
New Member
New Member

--
21 Aug 2007 12:33 PM
Was there a resolution for this problem? I am having the same problem. I'm getting the error The Microsoft Distributed Transaction Coordinator (MSDTC service) is not available. Error Desc : Connection 'Microsoft ODBC Driver for SQL Server' for Task 'DTSask_DTSDataPumpTask' does not support joining distributed transactions or failed when attempting to join. Unable to enlist in the transaction
The DTC is running on both servers and the package actually ran once but now it's failing. I'm at my wits end with this problem and it needs to be fixed ASAP! Help is greatly appreciated.
sqldba1976
New Member
New Member

--
22 Aug 2007 06:08 AM
Windows 2003 and yes, Network DTC is enabled. The DTC is enabled on both servers and all security settigns are enabled and are the same on both servers. The destination server is a clustered server and yesterday when all resources were failed over to all be on the same node the job ran successfully. That took care of the MSDTC unavailable error but now the job is failing again with failing to enlist transactions.
sqldba1976
New Member
New Member

--
22 Aug 2007 12:56 PM
Yes, it is enabled on both servers.
sqldba1976
New Member
New Member

--
24 Aug 2007 04:09 AM
The job is a DTS package that is being executed via a job. The package is pushing data from a table on the source server to a table on the destination server. We are not using linked servers. Also, the destination server is clustered but all the resources are on the same node. What's strange is that the job will run during the day if the MSDTC is restarted but it fails when it runs during its scheduled run around 2 am. Restarting the MSDTC doesn't work at that time. It's very strange.
You are not authorized to post a reply.

Acceptable Use Policy