OLE DB error - Please Help

OLE DB error
I have the following sp running one a night via an agent: (See error below)
CREATE PROCEDURE [dbo].[IDR_Get_IDR] AS
Declare @.LastRefresh DateTime,
@.ProcessID Int,
@.MyError int
Begin Tran Process_Up
-- Find the last refreshed process
Select @.LastRefresh = P_EndTime
From dbo.IDR_Process
Where P_Status ='S'
-- Record the start of the refresh process
Select @.ProcessID = Max(P_Id)+1
From dbo.IDR_Process
Insert Into dbo.IDR_Process(
P_Id,P_ProcessID,P_StartTime,P_Status)
Select @.ProcessID ,57,GetDate(),'S'
Set @.MyError = @.@.Error
IF @.MyError <> 0
Rollback Tran Process_Up
ELSE
BEGIN
commit Tran Process_Up
Set @.MyError =0
End
Begin Tran Pop_Idr WITH MARK
Truncate Table dbo.IDR_IncomeDist
Insert Into dbo.IDR_IncomeDist(
id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, CommissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified)
SELECT id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, commissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified
FROM OPENQUERY(Digex, 'SELECT * from FileArchive.dbo.IncomeDist')
Where Created >@.LastRefresh-1 or Modified >@.LastRefresh-1
Set @.MyError = @.@.Error
IF @.MyError <> 0
Begin
Rollback Tran Pop_Idr
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'F'
End
ELSE
Begin
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'S'
commit Tran Pop_Idr
End
GO
When pull out the individual pieces the query and run them in sequence they
al run fine but when I run the sp as a whole I get the following error:
Server: Msg 7391, Level 16, State 1, Procedure IDR_Get_IDR, Line 35
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].
Gerald Blackwell
gblackwell@.alliedhomenet.comI cannot give you the exact reason for the error but here are sme things to
check:
Since there is a linked server and a BEGIN TRAN statement a distributed
transaction is used. This requires that MSDTC be running on both machine
and SQL Server has to have access to it. Check the SQL Server errorlog for
a message related to MSDTC or the transaction coordinator. Also if these
servers are on Windows 3003 verify that you have Network MSDTc installed.
It is a Windows component so you can check in Control Panel - Add/Remove
Programs.
Rand
This posting is provided "as is" with no warranties and confers no rights.