I have a valid SQL statement that has gathers records from 2 data sources, SQL tables and 2007 excel spreadsheet.

The intent was to utilize SQL statement in CR Command via existing SQL connection to db that contains the source tables.

However, when pasted in the CR command errors are generated - "Failed to retrieve data from database.... Native Error 7399" and "Failed to retrieve data from database.... Native Error 7303". These errors also reference the Microsoft Jet 4.0 provider.

I have found scads of web posts that discuss issues with file permissions, 64 vs 32 bit support of Jet 4.0 provider and Excel 2007/2010 compatibility. I believe I have ruled out these possible conflicts.

The SQL statement is attached below. I am seeking guidance on how to proceed. Any insight is greatly appreciated.

SELECT *
FROM

(SELECT
          Document,
          Packlist,
          Customer_PO
   FROM Production.dbo.Invoice_Detail) ID
JOIN
(SELECT
          Document,
          Customer,
          Document_Date,
          Open_Invoice_Amt
   FROM Production.dbo.Invoice_Header) IH
ON     ID.Document = IH.Document

WHERE Customer like '12567%' and IH.Open_Invoice_Amt <> 0 and Packlist not in (SELECT [InvoiceNumber] FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=S:\Barb\export1.xls;Extended Properties=EXCEL 8.0')...[export2$] E JOIN Production.dbo.Invoice_Detail ID ON E.InvoiceNumber = ID.Packlist)

Hilfy - thanks much for the response..... 

yes - file is/was saved as 97-2003, sorry for the miscommunication.

yes - the query was developed and debugged in SSMS.

connection attempted was OLEDB (ADO) provider. I can also confirm no success with ODBC.

I'm having some difficulty understanding the following: if I create an Access/Excel (DAO) connection directly to the .xls file it is successful with no errors. This is true even when two connections are made - one to SQL db and the other to .xls.

Thanks for the explanation - it is greatly appreciated.

I had started down the road of dual connections but was not linking correctly and did not consider the need for additional selection criteria. Looks like this was the missing piece!

Thank you for your time and input!