one matrix above another doesn't export properly to Excel

A newb question: I've got a report with a table leftmost in the body,
with a matrix immediately to the right of the table. Immediately
below the table are 2 textboxes displaying totals from the table.
Immediately below the first matrix is another matrix displaying totals
from the first matrix:
Table Matrix1
Textboxes Matrix2
All is well in preview, HTML view, and PDF export.
After Excel export, the sheet is arranged thus:
Table Matrix1
Matrix2 Textboxes
I've tried putting the 2 matrices in a rectangle, and even inside
nested rectangles, with no effect on the incorrect Excel layout.
The report is designed this way because the users want empty space
between the detail data (in the Table and Matrix1) and the totals.
The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
expand in step with Matrix1.
I think. Help? RDL available if anybody wants it.
Thanks
MatrixLoverI am running into similar problem. I have one matrix over another and report
logic hide / unhide matrix. Every work like charm in reports, export to pdf,
but having trouble in excel export. When export to excel, the hidden matrix
also appears just below the active matrix. Any help appreciated.
"MatrixLover" wrote:
> A newb question: I've got a report with a table leftmost in the body,
> with a matrix immediately to the right of the table. Immediately
> below the table are 2 textboxes displaying totals from the table.
> Immediately below the first matrix is another matrix displaying totals
> from the first matrix:
> Table Matrix1
> Textboxes Matrix2
> All is well in preview, HTML view, and PDF export.
> After Excel export, the sheet is arranged thus:
> Table Matrix1
> Matrix2 Textboxes
> I've tried putting the 2 matrices in a rectangle, and even inside
> nested rectangles, with no effect on the incorrect Excel layout.
> The report is designed this way because the users want empty space
> between the detail data (in the Table and Matrix1) and the totals.
> The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
> expand in step with Matrix1.
> I think. Help? RDL available if anybody wants it.
> Thanks
> MatrixLover
>

One last xp_cmdshell Q (sorry)

I run the below on the box that has psExec installed as sa (dbo) in QA.
The MSSQLSERVER service is set to Local Account.
Domain\Administrator will run c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat
from a CmdExec job sucessfully. I run same from command prompt successfully.
Do I have to set MSSQLSERVER agent to start as admin.?
Or can I explicitly say exec master.Domain\Administrator.cp_cmdshell or
similar?
Even our DBA is stumped!!
EXEC xp_cmdshell 'c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat' ;
output
---
NULL
PsExec v1.60 - Execute processes remotely
Copyright (C) 2001-2005 Mark Russinovich
Sysinternals - www.sysinternals.com
NULL
Access is denied.
Connecting to 10.2.27.230...
Couldn't access 10.2.27.230:
NULL
It looks like psExec doesn't have permission but it is SQL QA that is the
sourcer of the problem.Local Account? or Local System? The SQL Server service must execute with
the credentials needed to execute commands on the other computer. It cannot
execute as Local System. It must execute as a domain user account.
WARNING!!! It is extremely dangerous to use xp_cmdshell in this way. It is
even more dangerous to run as a domain admin. If you're a consultant, be
sure you have a battalion of lawyers in your pocket and review your
professional insurance contract, because it would be a simple thing for a
smart lawyer to prove gross negligence on your part.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:642C7D5B-DA48-427D-9535-0BF66E90ADA4@.microsoft.com...
> I run the below on the box that has psExec installed as sa (dbo) in QA.
> The MSSQLSERVER service is set to Local Account.
> Domain\Administrator will run c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat
> from a CmdExec job sucessfully. I run same from command prompt
successfully.
> Do I have to set MSSQLSERVER agent to start as admin.?
> Or can I explicitly say exec master.Domain\Administrator.cp_cmdshell or
> similar?
> Even our DBA is stumped!!
> EXEC xp_cmdshell 'c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat' ;
> output
> ---
> NULL
> PsExec v1.60 - Execute processes remotely
> Copyright (C) 2001-2005 Mark Russinovich
> Sysinternals - www.sysinternals.com
> NULL
> Access is denied.
> Connecting to 10.2.27.230...
>
> Couldn't access 10.2.27.230:
> NULL
> It looks like psExec doesn't have permission but it is SQL QA that is the
> sourcer of the problem.|||thx again, what are the alternatives, create an account and lock it down,
maybe? or
"Brian Selzer" wrote:

> Local Account? or Local System? The SQL Server service must execute with
> the credentials needed to execute commands on the other computer. It cann
ot
> execute as Local System. It must execute as a domain user account.
> WARNING!!! It is extremely dangerous to use xp_cmdshell in this way. It i
s
> even more dangerous to run as a domain admin. If you're a consultant, be
> sure you have a battalion of lawyers in your pocket and review your
> professional insurance contract, because it would be a simple thing for a
> smart lawyer to prove gross negligence on your part.
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:642C7D5B-DA48-427D-9535-0BF66E90ADA4@.microsoft.com...
> successfully.
>
>|||Create an external process to do whatever it is you need to do. Then you
don't have to worry about the xp_cmdshell security hole. Another solution
is to use replication, but if you can't, then you can queue up the
information in a table and create a dequeue process to move it. Both of
these solutions is more robust, because you can still collect information if
the other system is down.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:97BD8606-7F5A-4904-B13C-533FE167A77D@.microsoft.com...
> thx again, what are the alternatives, create an account and lock it down,
> maybe? or
> "Brian Selzer" wrote:
>
with
cannot
is
be
a
QA.
cmdmgr_Resume.bat
or
the|||On Wed, 7 Sep 2005 05:46:10 -0700, marcmc wrote:

>I run the below on the box that has psExec installed as sa (dbo) in QA.
>The MSSQLSERVER service is set to Local Account.
>Domain\Administrator will run c:\psexec \\10.2.27.230 -I cmdmgr_Resume.bat
>from a CmdExec job sucessfully. I run same from command prompt successfully
.
>Do I have to set MSSQLSERVER agent to start as admin.?
>Or can I explicitly say exec master.Domain\Administrator.cp_cmdshell or
>similar?
>Even our DBA is stumped!!
Hi marcmc,
(S)he should check out what BOL says about services accounts.
If you really want to do it like this, than the least unsafe way is to:
a) Have your network administrator set up a domain account for use by
the SQL Server service. Make sure to change the password policy to not
automatically age the password.
b) Give this account all privileges required for SQL Server operation,
plus all privileges required to run your batch file. Don't give any
other privileges to this account. If your version of windows allows it,
disable the posibility to log on using this account as a normal user
(i.e. make it an account that can only be used by services)
c) Change the startup parameters for your SQL Server instance to log in
using this account instead of logging in as Local System.
d) Keep your fingers crossed :-)
But I agree with Brian that there are other ways to do this, and that
they are probably better!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

OLE DB error trace Non-interface error.

Hi ,
When I try running the below sql on MSDE i am getting this error, Can some
one pls help.
It works fine on other systems where SQL Server 2000 is installed.
Select * into Brand FROM OPENROWSET('MSDASQL.1','test1';'sa';'pat','select
* from [BRAND]')
This is the detail error msg when when i set TRACEON (7300, 3604).
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDASQL.1'.
OLE DB error trace [Non-interface error: OLE DB provider MSDASQL.1 returned
an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
OLE DB error trace [OLE/DB Provider 'MSDASQL.1' IRowset::GetNextRows
returned 0x80040155].
MSDE version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.2 (Build 3790: )
SQl Server Driver Version : 2000.85.1022.00
Thanks & Regards,
Ashish.Ashish P K wrote:
> Hi ,
> When I try running the below sql on MSDE i am getting this error,
> Can some one pls help.
> It works fine on other systems where SQL Server 2000 is installed.
>
> Select * into Brand FROM
> OPENROWSET('MSDASQL.1','test1';'sa';'pat','select * from [BRAND]')
Why not use the native SQL Server provider? SQLOLEDB instead of MSDASQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi,
When I tried with SQLOLEDB it worked, but I need to use DSN connection
with the OPENROWSET, So I think , need to use MSDASQL only.
Sql Server (MSDE) is on windows 2003 machine..whether this can cause any
problem?
Regards,
Ashish.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:%23Bq1SfRDFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Ashish P K wrote:
> Why not use the native SQL Server provider? SQLOLEDB instead of MSDASQL.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Ashish P K wrote:
> Hi,
> When I tried with SQLOLEDB it worked, but I need to use DSN
> connection with the OPENROWSET,
Why?

> So I think , need to use MSDASQL only.
> Sql Server (MSDE) is on windows 2003 machine..whether this can cause
> any problem?
MSDASQL has be deprecated for at least 3 versions of MDAC. New functionality
that has been added to SQLOLEDB in those three revisions may not be provided
by MSDASQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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,PropertySta
te,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmou
nt,
ApplicationCommitmentFeeAmount,DiscountP
ointsAmount,
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,PropertySta
te,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmou
nt,
ApplicationCommitmentFeeAmount,DiscountP
ointsAmount,
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.

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,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.

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.