How to enable OPENROWSET in SQL SERVER?

OPENROWSET is a function used to connect to remote OLEDB Server.
Generally This is used when importing data from Excel or someother OLE DB Server to SQL SERVER.

Following error messages will be displayed when the OPENROWSET is not enabled in the Server.

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered

Solution to correct these errors would be to enable OPENROWSET in SQL SERVER.

OPENROWSET Function can be enabled by running the following queries in SQL Server.

    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE
    GO

This entry was posted in , . Bookmark the permalink.

Leave a reply