Date Error with View from Linked Server

[Llazwas]

I have a linked server pulling data from a ProvideX database. I've created a view to combine data from two tables.

SELECT        OrderDate, InvoiceDate, InvoiceNo, HeaderSeqNo, CustomerNo, CustomerPONo, ShipToName, ItemCode, QuantityShipped, WarehouseCode, ExtensionAmt
FROM            OPENQUERY(mas_xyz, 
                         'Select a.OrderDate, a.InvoiceDate, a.InvoiceNo, a.HeaderSeqNo, a.CustomerNo, a.CustomerPONo, a.ShipToName, b.ItemCode, b.QuantityShipped, b.WarehouseCode, b.ExtensionAmt from ar_invoiceHistoryHeader a, ar_invoicehistorydetail b 
where a.invoiceno=b.invoiceno and a.headerseqno=b.headerseqno')
                          AS vInvoiceHistory

When I execute this I'm getting the following error:

-------------------------
SQL Execution Error.

Executed SQL statement: SELECT OrderDate.ToString() AS OrderDate, InvoiceDate.ToString() AS InvoiceDate, InvoiceNo, HeaderSeqNo, CustomerNo, CustomerPONo, ShipToName, ItemCode, QuantityShipped, WarehouseCode, ExtensionAmt FROM OPENQUERY (mas_xyz , 'Select a.OrderDate, a.Inv...
Error Source: .Net SqlClient Data Provider
Error Message: Cannot find either column "OrderDate" or the user-defined function or aggregate "OrderDate.ToString", or the name is ambiguous.
-------------------------

If I query the one of the tables directly:

SELECT InvoiceNo, InvoiceDate
FROM OPENQUERY(mas_xyz, 'SELECT InvoiceNo, InvoiceDate FROM AR_InvoiceHistoryHeader')

I get the following data:

InvoiceNo | InvoiceDate
10012 | 1/15/2003
10013 | 3/12/2003
10014 | 3/12/2003

If I alias the date fields I get no error, the columns are just excluded from the view.

I assume the error is related to a problem with my date formats but I cannot figure out how to format them properly. Any help is GREATLY appreciated!!

 

[SQLBill]

The query isn't having an issue with InvoiceDate. It is having the issue with OrderDate. You need to find out what that column is like on the source. Does it exist? Is it a computed column (in other words, it really doesn't exist, but is created whenever the table is queried)?

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Llazwas]

Thanks SQLBILL.

The order date is a valid column in my source db, but currently has NULL values for everything. I swapped it out in my view for the invoice date, which has the values shown in the previous post and get the same error. In fact, if I try to pull any date field in to the view I get that error. Queries with the same date columns work ok.

I think the order date is just the first one the view is encountering so it's displaying that in the error first.

 

[SQLBill]

The issue then appears to be the date format and that SQL Server doesn't 'understand' it. I suggest creating a 'staging' table in SQL Server with the columns you need all as VARCHAR(x), pull the data into the staging table, then query the data using SQL Server and convert the VARCHAR date columns to DATETIME values.

That is how we usually deal with dates coming from Oracle.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875