Import excel data into sql

  • 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 .... 🙁

  • 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