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,DiscountPointsAmoun t,
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,DiscountPointsAmoun t,
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.com
I 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.
. When I try to scroll down I get a Message Box: