Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

MS SQL openrowset function

I tried to run this command, and am getting the error message. How can I solve it
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\autre\testing.xls;',
'SELECT * FROM [Sheet1$]')
SELECT * FROM dbo.contract Where PROJECTID=160

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.
 A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
0
Omer-Pitou
Asked:
Omer-Pitou
  • 2
  • 2
2 Solutions
 
Habib PourfardSoftware DeveloperCommented:
try this:
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Open in new window

0
 
Omer-PitouAuthor Commented:
The configuration works perfectly. But, now i am getting this error message
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Seems like the sql server machine is missing "Microsoft Access Database Engine 2010 Redistributable”
0
 
Omer-PitouAuthor Commented:
Am I right by saying that the number 12  in "Microsoft.ACE.OLEDB.12.0" refers to 2010 version of this engine?
What If I have access 2013 installed on my computer, what you will be the version number of the engine?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>Am I right by saying that the number 12  in "Microsoft.ACE.OLEDB.12.0" refers to 2010 version of this engine?
Yup , you can download it from here https://www.microsoft.com/en-ca/download/details.aspx?id=13255 

>What If I have access 2013 installed on my computer, what you will be the version number of the engine
2013 is 14.0 ;
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now