Search This Blog

Loading...

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component

"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. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."

By default SQL Server 2005 disables the execution of Ad Hoc Queries, but that was not the case with SQL Server 2000. For example if you runs the following query and you are getting the error like the one I mentioned above, then this article will definitely going to help you.

Insert into OPENROWSET('Microsoft Jet 4.0 OLEDB','Excel8.0;Database=C:\ testsheet.xls','SELECT Number,Type from [Sheet1$]')SELECT count(*) as [Number Of Objects],type_desc as [Type Description]
from sys.objects group by type_desc

Now in order to run the adhoc Queries you should enable this Using the SQL server 2005 "surface Area Configuration" utility.  You can find the "SQL Server Surface Area configuration " utility, under

Start ->Programs ->Microsoft SQL Server 2005 -> Configuration tools

 

image

 

Once you Click this , you can see a window like this. You need to choose the second option, i.e. "Surface area configuration for features "

 

 

 

 

 image

Now, you must be little careful if you have more than one instance of sql server installed on your machine.

You need to choose the appropriate instance of sql server; if the default instance is SQL server 2000, then by default, its options will be shown in the left pane and most of the options  that you are looking for,wont be visible. So be careful, So in my case I have to choose the instance "SQL2005_1" as that is my only one SQL server 2005 instance.

 

image Once you expand the SQL Server 2005 you will able to see a lot of configuration options on the left pane. In order to allow the execution of queries containing "OPENROWSET/OPENQUERY" , you need to choose the "Ad Hoc remote Queries option and Check that option at the right pane"

1 comment:

cdgnfg said...

welcome to the wow power leveling cheap service site, buy wow power leveling cheap wow gold,wow gold,world of warcraft wow power leveling buy wow gold