"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
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 "
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.
