ETL and Data Integration tools - Modern ETL software for everyone

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

George

Following is the procedure that is used in the Export Package -

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'


SQL_NTS) Failed: 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]The column 'ID' was specified multiple times for 'a'.
Warning 13/09/2010 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'),

Error message
SQL_NTS Failed: 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]The column 'ID' was specified multiple times

You have to rewrite your SQL to get rid of duplicates
+ this part is user locale specific '05/31/2010' I would recommend to use for it cast as well

select
InvoiceHeader.BillingCategoryID,
InvoiceHeader.ClubNumber,
InvoiceHeader.CustomerID,
InvoiceHeader.Date,
InvoiceHeader.ID as InvoiceHeaderID,
InvoiceHeader.InactiveFlags,
InvoiceHeader.PaidAmount,
InvoiceHeader.TotalAmount,
InvoiceDetail.Amount,
InvoiceDetail.Description,
InvoiceDetail.ID as InvoiceDetailID,
InvoiceDetail.InvoiceID,
InvoiceDetail.ItemAccountID,
InvoiceDetail.ItemID
from InvoiceHeader, InvoiceDetail
Where InvoiceHeader.ID = InvoiceDetail.InvoiceID and cast (floor(cast(Date as float)) as datetime) =convert(datetime,'05/31/2010' ,101)

PS
We do not do any anything specific to the SQL we just use what ever was given to us by Microsoft,Oracle etc
We pass to the ODBC driver it rejects it.

Mike