Distributed Query Issue

[ClaireHCM]

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.

 

[JamesLean]

I would have thought you would need to install Excel for it to be able to read an xls. May be wrong though - have you tried it?

--James

 

[ClaireHCM]

Now another issue comes up.

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.