Any idea why OpenRowSet to open Excel file doesn''t work well in SQL 2005?

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')

I got error

OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

|||

Any idea?

this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,

Any help?

|||

any suggestion?

help please

|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||

Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.

I am using the buildin/administrators to connect to SQL server

|||

I get following error while trying to query ACCESS data, any clues?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

|||

Ashish,

I'm getting the same error you did. Did you ever resolve the problem?

In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.

But if I log in with my domain account from another PC or server, I get this error in the report server:

An error has occurred during report processing.

Query execution failed for data set 'FinanceLinkedServer'.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".

My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.

I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.

Thanks!

|||

I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:

My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.

|||

Thanks, davery921. I tried that, but it didn't resolve the error.

Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?

|||First, to answer your question, the answer is yes, I've gotten it to work.

Next.... I need help too...

I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:

DECLARE @.RC int

DECLARE @.STARTDATE datetime

DECLARE @.ENDDATE datetime

-- TODO: Set parameter values here.

EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]

'07/01/2007','07/31/2007'

On my machine through SSMS, I get this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".

If I run this on my test server through Remote desktop, to the test database, it runs just fine!

Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.

The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.

Any ideas?