Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JLatiolait
Starting Member
6 Posts |
 Posted - 2005-02-20 : 14:05:11
|
| Hello,I'm new to this forum but REALLY hoping for some help... I find no entry in this forum for this error. I have 'no' problem executing a specific query on a Linked Server / Excel spreadheet through Query Analyzer (Administrator) or through an ASP page connecting through Login setup with: Server Role - Systems Administrator. However any other lesser Login / Server Role yields:ERROR -Microsoft OLE DB Provider for SQL Server (0x80040E14)Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'NOTES:* Link Server Properties / Security is - DEFAULT* Permissions on Excel spreadsheet & directory are - Everyone* SQL Server 7.0 SP4I believe what I'm looking for are the What and How on setting the permissons on Login executing this query against the linked server / Excel Spreadsheet?Can someone please assist.Thanks, j |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-02-20 : 17:26:43
|
| SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\my.xls;Extended Properties=Excel 8.0')...[Sheet1$]How it goes (in QA) for a non-admin user? |
 |
|
|
JLatiolait
Starting Member
6 Posts |
Posted - 2005-02-20 : 21:33:15
|
| hi Stoad,thanks for the reply.SELECT * FROM CDT_EXCEL...[Sheet1$] - this is the syntax for the linked server as i understand it. it works in boht QA and ASP as long as i Login has Admin. priv......however, the adhoc syntax you give NOW works, after i've setup a Linked Server-? i had tried your syntax before, as an adhoc query, but the "error" i received told me to setup a linked server...all i can think of is that when i setup the Link Server it changed a registry setting that then allowed the adhoc query wihtout error???any ideas? |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-02-21 : 04:28:58
|
| Strangely! My ad hoc query must work without setting up any linked servers. Not sure what to say right now. |
|
|
|
JLatiolait
Starting Member
6 Posts |
Posted - 2005-02-25 : 14:31:10
|
| ANSWER:Setting up Linked Server provides options under the provider pane. Option to "disallow ad-hoc.." if you check and then uncheck this config. it write the approrpriate key to the registry. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-02-26 : 05:04:35
|
| merci beaucoup for the info...have you resolved your trouble? if so, what it was? |
|
|
|
JLatiolait
Starting Member
6 Posts |
Posted - 2005-02-26 : 11:31:22
|
| i'm running my query as ad-hoc (what you note above)... never was able to run the Links Server syntax. this is fine for my app. i setup the LS just to change the registr setting to enable ad-hoc access... (the key appears to be missing by default) alternatively, i could have added/edited the registry mannually.:) |
|
|
|
|
|
|
Linked Server: Excel Spreadsheet - Error.