December 8, 2006 at 12:37 pm
Hello,
I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;
-It has one worksheet in it named 'Add Revenue Accts'.
-The name of the workbook is 'Revenue_to_All_Accounts.xls'
-Its location is \\cdnbwfin1\data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts
I have the linked server configured as follows;
-Linked Server; REVENUE_TO_ALL_ACCOUNTS
-Provider; Microsoft Jet 4.0 OLE DB Provider
-Data Source; \\cdnbwfin1\data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts\Revenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\\Cdnbwfin1\Data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts\Revenue_to_All_Accounts.xls;Persist Security Info=False
When I attempt the following query;
SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')
The following message appears, and no results are returned;
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?
Thank you for your help!
CSDunn
December 8, 2006 at 3:34 pm
Never mind on this. I discovered that by setting up a DSN on the local sql server, I can use the MS OLE DB Provider for ODBC Drivers and point to the DSN.
December 11, 2006 at 4:13 am
Hi,
1. When U R running Ur Query that time Excel Sheet should not be opened.
2. Sheet Name should not have space e.g. Ur Excel Sheet Name is [Add Revenue Accts$]. It should be Like [AddRevenueAccts] then it should work.
Try it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply