Here is my query:
Select AQ.hwt_user, AQ.Cust_Div_Def_Definit,
AQ.hwt_action, AQ.hwt_entity_id,
AQ.Order_H_PO, AQ.FirstName + ' ' + AQ.LastName as UserName,
AQ.hwt_part_number, AQ.hwt_location_id_wasp,
AQ.hwt_asset_tag, AQ.hwt_serial_number,
AQ.hwt_quantity, AQ.hwt_create_date,
AQ.hwt_create_time, AQ.hwt_entry_date,
AQ.hwt_entry_time, AQ.hwt_processed,
AQ.Prod_Description, location.code as location_code
From
OPENQUERY(CommPlete,
'Select hh_warehouse_trans.hwt_user, Customer_Division_De.Cust_Div_Def_Definit,
hh_warehouse_trans.hwt_action, hh_warehouse_trans.hwt_entity_id,
Order_Header.Order_H_PO,
Rtrim(User_Code_Table.User_First_Name) as FirstName, Rtrim(User_Code_Table.User_Last_Name) as LastName,
hh_warehouse_trans.hwt_part_number, hh_warehouse_trans.hwt_location_id_wasp,
hh_warehouse_trans.hwt_asset_tag, hh_warehouse_trans.hwt_serial_number,
hh_warehouse_trans.hwt_quantity, hh_warehouse_trans.hwt_create_date,
hh_warehouse_trans.hwt_create_time, hh_warehouse_trans.hwt_entry_date,
hh_warehouse_trans.hwt_entry_time, hh_warehouse_trans.hwt_processed,
Product_Catalog.Prod_Description
From hh_warehouse_trans
Inner Join Customer_Division_De
On Customer_Division_De.Cust_Div_Def_Custome = ''2009010101''
And Customer_Division_De.Cust_Div_Def_Divisio = hh_warehouse_trans.hwt_warehouse
Inner Join Product_Catalog
On Product_Catalog.Prod_Customer_No = ''2009010101''
And Product_Catalog.Prod_Customer_Div = hh_warehouse_trans.hwt_warehouse
And Product_Catalog.Prod_Mfr_Part_No = hh_warehouse_trans.hwt_part_number
Left Outer Join Order_Header
On Order_Header.Order_H_Order_No = hh_warehouse_trans.hwt_entity_id
Inner Join User_Code_Table
On User_Code_Table.User_ID = hh_warehouse_trans.hwt_user
Where hh_warehouse_trans.hwt_asset_tag = ''AM00163779''
And hh_warehouse_trans.hwt_action <> ''Move''
And hh_warehouse_trans.hwt_processed = 1
') As AQ Left Outer Join
location On location.location_id = AQ.hwt_location_id_wasp
Order By AQ.hwt_entry_date Desc,
AQ.hwt_entry_time Desc
This works on an older 2003 sp2 server running MS SQL 2005 but fails with a catastrophic failure on a 2012 R2 server running MS SQL 2012.
The error message is: The OLE DB provider "MSDASQL" for linked server "CommPlete" reported an error. The provider reported an unexpected catastrophic failure.
I meant to include that in the original post and then forgot.
The interesting thing is it returns the header row just no data.