May 21, 2008 at 3:51 pm
I want to import excel file data to sql server 2000 using sql programming
I used following command and tried to execute it in sql analyzer,
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=..\file.xls', [Sheet1$])
and Received the following error Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
Can any one please help me with this .... 🙁
May 22, 2008 at 5:24 am
You can see if these tips help:
1. Enable ad hoc distributed queries, by running:
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
2. Allow ad-hoc access to the Jet provider, by using the registry
(using Management Studio will not work, because it deletes the registry
key instead of setting it to zero, and a missing registry key is
interpreted as "do not allow ad-hoc access"). In the
Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0" key, add a DWORD
value named "DisallowAdHocAccess" with the value 0.
(Reg key roughly = HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers)
3. If you previously got the error, before trying again make sure that
you restart the server or run a DBCC FREEPROCCACHE, because it seems
that SQL Server won't check the key again if you run the same query
twice.
"Got no time for the jibba jabba!"
-B.A. Baracus
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply