Distributed query issue

[Claire]

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=W:\MyExcel.xls', 'select * from [Sheet1$]')
which works on my local server with Microsoft office XP Professional installed

But keep on failing on the QA server which has MDAC installed only, no Microsoft Office installed

The error msg is 7399. I thought it's because of permission issue.

But problem still resides after I move the Excel file into that QA NT server and execute the statement with a service account which has sysadmin permission on both NT Server and SQL Server


What might be te reason?Why it's working perfectly in my Local server.


--------------------------------------
This is the error message:

Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

[MAK]

refer KB article 814398
http://support.microsoft.com/?kbid=814398

http://dbforums.com/arch/72/2002/8/475316

http://www.sqlmag.com/forums/message...&threadid=5554

[Claire]

All these 3 links are talking about service account doesnt have full access in those folder.

The SQL Server Service account used to run the select statement is the sysadmin for both NT server and SQL server.

And Excel file is stored in the NT server which is also where SQL Server installed.

So....any other suggestion?

[rmiao]

May need install excel on qa server.

[Claire]

It's confirmed to be permission issue.Now the stored proc will work only if
(1) I logon my NT station and I have local SQL server

(2)I logon to NT Server where QA SQL Server installed and execute SP from there.


If I log on into the NT Server where QA SQL Server installed.I could execute the SP without problems.

But if I logon from my machine(only client) and connect to QA SQL Server, then try to execute the SP again.I ran into permission issue.(Error 7399)

So I tried to logon into another server (only client), again I ran into permission issue.(Error 7399)

Since in the SP I use openrowset to access the excel file.That's where the permission issue comes up.But when I logon into the NT Server, I checked the SQL Service account is added in Administrator group in the NT server.So no matter where I connected to SQL Server.The SQL service account will be all the same.Why only if I logon into the NT server where SQL Server installed, I can execute the sp.

Is it because if openrowset is queried in my stored proc.I have to execute from server, not allow in client?