h1

SQL SERVER: How to enable ‘Ad Hoc Distributed Queries’ SQL SERVER 2005

03/19/2009
Frequently, we need to use OPENROWSET queries to connect to remote database servers. To enable this feature on SQL Server 2005, you should first configure the database to enable Ad Hoc Distributed Queries.

We can Enable this feature by two ways:

1. SQL Server Surface Area Configuration.

2. by sp_configure option.

Lets check with first way, by SQL Server Surface Area Configuration.

Open surface Area configuration, you will get this screen:

131

Click on second option, Surface Areas Configuration for Features. you will get this screen, where you need to check to Enable OPENROWSET and OPENDATASOURCE support.

132

Lets see second option to enable this feature with sp_configure option:

sp_configure

If you run this command, you will lists of SQL configuration settings. There are 14 items in the list in which ‘Ad Hoc Distributed Queries’ is not exist. To see this, we need to enable the ‘show advanced options’ configuration parameter.

You can enable advance options by:

sp_configure ‘show advanced options’,1

When we run this command we will get this message:

“Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.”

so we need to execute reconfigure command as:

reconfigure

so now if we run sp_configure again, we will get result set as follows:

133

Here, we can find that config_value for “Ad Hoc Distributed Queries” is “0”. We need to set it to 1 to enable this feature. so to do that we need to use following:

sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure

so, now if we run sp_configure, we will get result as follows:

134

Here, we can find that now config_value for “Ad Hoc Distributed Queries” is “1”.

That’s it, now you can use OPENROWSET and OPENDATASOURCE to connect with remote database without Linked server.

Let me know if it helps you in any way.

About these ads

8 comments

  1. Sir,

    Surface Area Configuration is deprecated feature in SQL Server 2008.

    Regards,
    Pinal Dave


  2. Hi,

    You are correct. This article is for SQL SERVER 2005. Let me change Article Header.

    Thanks,

    Tejas


  3. Sir,

    Surface area configuration may be deprecated in SQL Server 2005 but the way you have displayed how to enable adhoc queries is same in SQL Server 2008 so in one way you have done good job :)

    sp_configure ’show advanced options’,1
    reconfigure
    sp_configure ‘Ad Hoc Distributed Queries’,1
    reconfigure

    Should work fine.

    Kind Regards,
    Pinal


  4. Perfect!

    Well Done!

    I’ve searched over the internet a good article
    for this problem.

    Your Article is well summarized and efficient!!

    Thank’s a Lot!!


  5. Thanks…. helped a lot!! I wasn’t sure how to go about this configuration but your article is good for ‘dummies’ like me!!

    Thanks


  6. Thanks!


  7. Thanx..:)


  8. it does help, thanks……



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: