Hello
The database we are working with is using Windows SQL 2000 (SP4)
We have been setting up a trail for evaluating the Advanced ETL Processor Enterprise product -
To date it was all quite straightforward, however we have struck a problem with a particular SQL procedure that is showing up as an error when we run the Export Package from the Repository. In the attached document (Package run time log file) you will see the errors that occur for the Export in the third Export for the procedure dealing with a join of 2 tables InvoiceHeader and InvoiceDetail
I can run the procedure in the SQL200 Query Analyser without see any errors and it returns the expected number of rows - 69,114
I suspect it is something to do with the SQL procedure and the way we have constructed the Join -
Would you please review and advise if there is an alternate method to achieve an error free run as at present the errors will cause the Package to fail and in the full package we have a number of procedures that require a table Join to extract the required data for export.
Let me know if you require any further information –
Thank you for your assistance –
Regards,
John
Package execution log
Information 13/02/2011 00:13:38 Preparing to execute: Repository
Information 13/02/2011 00:13:38 Package Started
Information 13/02/2011 00:13:38 Executing Export: Export My_Customer Change data LUDate
Information 13/02/2011 00:13:39 Executing:Export My_Customer Change data LUDate
Information 13/02/2011 00:13:39 Source Select Statement: select
Customer.CardExpires,
Customer.CategoryID,
Customer.ClubNumber,
Customer.ContractExp,
Customer.ContractStart,
Customer.GroupID,
Customer.ID,
Customer.LUDate,
Customer.MemberSince,
Customer.SalespersonID
from
Customer
where
cast (floor(cast(LUDate as float)) as datetime) = '05/31/2010'
Information 13/02/2011 00:13:39 Data Source has: 817 Record(s)
Information 13/02/2011 00:13:39 Using Bulk Fetch
Information 13/02/2011 00:13:39 Export File : C:\Temp\MY_CustomerChangeData.csv
Information 13/02/2011 00:13:39 Processed: 817 Record(s)
Information 13/02/2011 00:13:39 Records per second: 1136.3
Information 13/02/2011 00:13:39 Time Taken: 00:00:00
Information 13/02/2011 00:13:39 Done!
Information 13/02/2011 00:13:39 Executing Export: Export MY_Contract ChangeData LUDate
Information 13/02/2011 00:13:39 Executing:Export MY_Contract ChangeData LUDate
Information 13/02/2011 00:13:39 Source Select Statement: select
Contract.ApprovalStatus,
Contract.Category,
Contract.CustomerID,
Contract.EffectiveDate,
Contract.EmployeeID,
Contract.ExpireDate,
Contract.ID,
Contract.LUDate,
Contract.PlanID,
Contract.SaleDate,
Contract.VoidDate
from
Contract
where
cast (floor(cast(LUDate as float)) as datetime) = '05/31/2010'
Information 13/02/2011 00:13:40 Data Source has: 87 Record(s)
Information 13/02/2011 00:13:40 Using Bulk Fetch
Information 13/02/2011 00:13:40 Export File : C:\Temp\MY_ContractChangeData.csv
Information 13/02/2011 00:13:40 Processed: 87 Record(s)
Information 13/02/2011 00:13:40 Records per second: 129.46
Information 13/02/2011 00:13:40 Time Taken: 00:00:00
Information 13/02/2011 00:13:40 Done!
Information 13/02/2011 00:13:40 Executing Export: Export MY_InvHeader_Detail ChangeData Date
Information 13/02/2011 00:13:40 Executing:Export MY_InvHeader_Detail ChangeData Date
Information 13/02/2011 00:13:40 Source Select Statement: select
InvoiceHeader.BillingCategoryID,
InvoiceHeader.ClubNumber,
InvoiceHeader.CustomerID,
InvoiceHeader.Date,
InvoiceHeader.ID,
InvoiceHeader.InactiveFlags,
InvoiceHeader.PaidAmount,
InvoiceHeader.TotalAmount,
InvoiceDetail.Amount,
InvoiceDetail.Description,
InvoiceDetail.ID,
InvoiceDetail.InvoiceID,
InvoiceDetail.ItemAccountID,
InvoiceDetail.ItemID
from InvoiceHeader, InvoiceDetail
Where InvoiceHeader.ID = InvoiceDetail.InvoiceID and cast (floor(cast(Date as float)) as datetime) = '05/31/2010'
Error 13/02/2011 00:13:40 SQLExecDirect(FHSMT,PChar ('select count(*) as rec_count from (select
InvoiceHeader.BillingCategoryID,
InvoiceHeader.ClubNumber,
InvoiceHeader.CustomerID,
InvoiceHeader.Date,
InvoiceHeader.ID,
InvoiceHeader.InactiveFlags,
InvoiceHeader.PaidAmount,
InvoiceHeader.TotalAmount,
InvoiceDetail.Amount,
InvoiceDetail.Description,
InvoiceDetail.ID,
InvoiceDetail.InvoiceID,
InvoiceDetail.ItemAccountID,
InvoiceDetail.ItemID
from InvoiceHeader, InvoiceDetail
Where InvoiceHeader.ID = InvoiceDetail.InvoiceID and cast (floor(cast(Date as float)) as datetime) = '05/31/2010'
) a'), SQL_NTS) Failed: 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]The column 'ID' was specified multiple times for 'a'.
Warning 13/02/2011 00:13:40 Unable to determine number of records in the Data Source: SQLExecDirect(FHSMT,PChar ('select count(*) as rec_count from (select
InvoiceHeader.BillingCategoryID,
InvoiceHeader.ClubNumber,
InvoiceHeader.CustomerID,
InvoiceHeader.Date,
InvoiceHeader.ID,
InvoiceHeader.InactiveFlags,
InvoiceHeader.PaidAmount,
InvoiceHeader.TotalAmount,
InvoiceDetail.Amount,
InvoiceDetail.Description,
InvoiceDetail.ID,
InvoiceDetail.InvoiceID,
InvoiceDetail.ItemAccountID,
InvoiceDetail.ItemID
from InvoiceHeader, InvoiceDetail
Where InvoiceHeader.ID = InvoiceDetail.InvoiceID and cast (floor(cast(Date as float)) as datetime) = '05/31/2010'
) a'), SQL_NTS) Failed: 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]The column 'ID' was specified multiple times for 'a'.
Information 13/02/2011 00:13:54 Using Bulk Fetch
Information 13/02/2011 00:13:55 Export File : C:\Temp\MY_InvHeaderChangeData.csv
Information 13/02/2011 00:14:17 Processed: 69114 Record(s)
Information 13/02/2011 00:14:17 Records per second: 1878.25
Information 13/02/2011 00:14:17 Time Taken: 00:00:36
Information 13/02/2010 00:14:17 Done!
Information 13/02/2011 00:14:17 Executing Export: Export MY_InvoiceCharge ChangeData DueDate
Information 13/02/2011 00:14:17 Executing:Export MY_InvoiceCharge ChangeData DueDate
Information 13/02/2011 00:14:17 Source Select Statement: select
InvoiceCharge.Amount,
InvoiceCharge.BilledCustomerID,
InvoiceCharge.ClubNumber,
InvoiceCharge.DueDate,
InvoiceCharge.InactiveFlags,
InvoiceCharge.PaidAmount
from
InvoiceCharge
where
cast (floor(cast(DueDate as float)) as datetime) = '05/31/2010'
Information 13/02/2011 00:14:20 Data Source has: 433 Record(s)
Information 13/02/2011 00:14:24 Using Bulk Fetch
Information 13/02/2011 00:14:27 Export File : C:\temp\MY_InvoiceCharge ChangeData.csv
Information 13/02/2011 00:14:27 Processed: 433 Record(s)
Information 13/02/2011 00:14:27 Records per second: 42.37
Information 13/02/2011 00:14:27 Time Taken: 00:00:10
Information 13/02/2011 00:14:27 Done!
Information 13/02/2011 00:14:27 Package Finished
