Cannot Query Excel Linked Server

Dec 8, 2006

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 \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_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; \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\Cdnbwfin1DataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_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!

cdun2

View 1 Replies

Importing Excel Using Linked Server And Passthrough Query

Jan 24, 2008

What: I am trying to import data from spreadsheets to SQL Server.

Where: Windows Vista, SQL Server 2005 Express, Office 2007.

How: Using linked servers following KB306397.

In SQL Management Studio Express, I created a new Linked Server as follows, with everything else at default:

Linked server: XLSX

Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider

Product name: XLSX

Data source: D:XLSX.xlsx

Provider string: Excel 12.0
The linked server was created ok.

I then followed KB321686 and ran this:

select * from OPENQUERY(XLSX, 'Select * From [Sheet1$]')

and got this:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".

But if I ran: select * from OPENQUERY(XLSX, 'Select * From [nonexistent$]')

the error is:

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "Select * From [nonexistent$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" indicates that either the object has no columns or the current user does not have permissions on that object.

It seems that there is an access rights problem if I get the sheet name correct. May I know what I must do to get this to work. I have already given read/write rights to the spreadsheet to NETWORK SERVICE and SQLServer2005MSSQLUser$servername$SQLEXPRESS.

From the same KB article, I also tried:

select * from XLSX...[Sheet1$]

but got this:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".

Help help.

Thanks.

View 5 Replies View Related

SQL 2012 :: Allow Authenticated User To Query Excel File Via Linked Server?

May 25, 2014

I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.

I'm trying to create a query within the application that will directly access an excel file through a linked server.

As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:

select *
from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".

When I login as a Windows auth user, I can successfully run the above query.

I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login

I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error

I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.

Details:
SQL Server Express 2012
Office version: Excel 10
Provider: Microsoft.ACE.OLEDB.12.0

View 9 Replies View Related

Passing A Variable To A Linked Query (OPENROWSET For Excel Syntax)

May 11, 2007

Hello,

I responded to a very old discussion thread & afraid I buried it too deep.

I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)

but I have not gotten all the ''''' + @variable syntax right.

Here is my raw openrowset with what I am aiming at.

Code Snippet

-- I want to use some kind of variable, like this to use in the file:

DECLARE @FIL VARCHAR(65)

SET @FIL = 'C:company foldersDocumentationINVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:company foldersDocumentationINVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game? Many thank-yous, in advance.

Kind Regards,

Claudia.