Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL openrowset function

Posted on 2015-02-03
5
Medium Priority
?
144 Views
Last Modified: 2015-02-07
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
Comment
Question by:Omer-Pitou
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 1000 total points
ID: 40587265
try this:
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Open in new window

0
 

Author Comment

by:Omer-Pitou
ID: 40587362
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 40587490
Seems like the sql server machine is missing "Microsoft Access Database Engine 2010 Redistributable”
0
 

Author Comment

by:Omer-Pitou
ID: 40588940
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40589017
>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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question