Print Page | Close Window

CR 2008, SQL 2005 and Excel 2007

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17774
Printed Date: 20 Apr 2025 at 10:56pm


Topic: CR 2008, SQL 2005 and Excel 2007
Posted By: TonyM70
Subject: CR 2008, SQL 2005 and Excel 2007
Date Posted: 16 Oct 2012 at 4:26am
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)


Replies:
Posted By: hilfy
Date Posted: 16 Oct 2012 at 5:25am
Crystal 2008 will not read data from Excel 2007.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: TonyM70
Date Posted: 16 Oct 2012 at 7:41am
Hilfy - thanks much for the response..... 
 
couple of questions:
 
CR 2008 cannot read Excel 2007 regardless of file format (i.e. saved as 97-2003)?
 
will upgrade of CR resolve this or is this an MS office/windows issue?

Posted By: hilfy
Date Posted: 16 Oct 2012 at 8:28am
It MUST be saved as 97-2003, in which case it's not in Excel 2007 format.  When you say "Excel 2007" it is assumed that that's the format of the file, not necessarily the version of the software.
 
What type of connection are you using to connect to your SQL Server database?  I'm not sure whether the "OPENDATASOURCE" syntax will pass through to the database with an OLEDB connection and I'm pretty sure it won't work correctly with ODBC.  There are issues with both types of connections, but OLEDB usually has more features than ODBC.
 
Can you run the query in SQL Server Management Studio?  I would start there and make sure the query works directly against the database before putting it into Crystal and trying it there.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: TonyM70
Date Posted: 16 Oct 2012 at 8:59am
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.

Posted By: hilfy
Date Posted: 16 Oct 2012 at 10:58am

It is because you are trying to connect through a SQL Server connection, not an Access/Excel DAO connection.  While SMS will allow this, there is something in the OLEDB middle-ware that doesn't understand this type of connection and so your query is causing an error.

Here's what I would do (if Crystal will let you...):

1.  Add the data from SQL Server under one connection and the data from Excel under another.

2.  Left Outer Join from the SQL Server data to the Excel data.

3.  In your selection criteria, do something like the following formula:

 
IsNull({ExcelTable.InvoiceNumber})
 
This should get you all of the data in the table that's not in the Excel file.  If this doesn't work, you'll have to look at uploading the Excel to a table in the database and working from there.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: TonyM70
Date Posted: 17 Oct 2012 at 4:47am
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!


Print Page | Close Window