April 7, 2004 at 10:39 am
I have a procedure that is fired off from a job, that does an archive operation to another server. So it performs a distributed transaction.
Initially i would get an error about the unability to start a distributed transaction. I had a heck of a time getting it to work in the first place, but seemed to get the right combination of sql server agent user to execute it, and the correct linked server user to the other machine. Once I found the magic combination and it started working, i was happy.
But then, on monday, i checked its status, and it had failed on friday prior or thursday, and no longer executes.
I am not sure if it has something to do with SQL Server Agent or not. I m not sure if its a DTC thing, because that is running on both machines. I am not sure if it is a user issue. I thought i had solved that too. It cannot start a distributed transaction. Its the same thing that happened originally, and was magically solved.
I have tried dtcping, dtctester applications that i found on microsoft's website. These seem to perform successfully and do not suggest any kinda problems that would explain why my process doesnt work.
Any ideas?
Thanks, in advance.
April 7, 2004 at 11:08 am
Could you please post the error message.
April 7, 2004 at 4:21 pm
The error is retrieved from the job history of the particular job that is failing.
Executed as user: sa. MSDTC on server 'ACS_RAC_SRV3' is unavailable. [SQLSTATE 42000] (Error 8501) The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c]. [SQLSTATE 01000] (Error 7300). The step failed.
SRV2 is trying to connect to DTC on srv3 to archive some data, with a company built archive solution.
June 14, 2004 at 3:01 pm
Not sure if this is relevant to your issue but we had the same problem and same error message after upgrading our server to Windows 2003 Enterprise.
I looked in the MS Knowledge base and found this
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql
The network access for the DTC is disabled by default in Windows 2003.
I followed the steps in the KB article and it resolved my problem. You say you have had yours working tho....so maybe a different issue. This may give you some ideas tho.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply