SQL Server blocks access to component \ 'ad Hoc Distributed Queries \'
An error occurred while querying the Excel file in SQL Server:
SELECT * from openrowset ('Microsoft. JET. OLEDB.4.0 ', 'excel 8.0; IMEX = 1; HDR = YES; DATABASE = D: \ a.xls', [sheet1 $])
Result prompt:
SQL Server blocks access to the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' component because this component has been disabled as part of the Server's security configuration. The system administrator can enable 'ad Hoc Distributed Queries 'by using sp_configure '. For more information about enabling 'ad Hoc Distributed querys', see "peripheral application configurator" in SQL Server books online ".
Query relevant information and find the solution:
Enable Ad Hoc Distributed Queries:
Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad Hoc Distributed Queries ', 1
Reconfigure
After use, disable Ad Hoc Distributed Queries:
Exec sp_configure 'ad Hoc Distributed Queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure
SELECT *
From opendatasource (
'Sqlodb ',
'Data Source = ServerName; User ID = sa; Password = Sa'
). DataBaseName. dbo. Table