Enable “Ad Hoc Distributed queries” by sp_configure in SQL-Server 2005

If you want to use OpenDataSource or OpenRowSet query than you must have to enable “Ad Hoc Distributed queries”. It is disabled by default as a part of security configuration as per Microsoft.

You can set this option by two ways.

1.)    sp_configure command

2.)    Surface area configuration.

Since I am a script bee, I will explain steps for sp_configure.

You can run sp_configure to see current status of “run_value” field. If you don’t able to see “Ad Hoc Distributed queries” in the results set.  You have to enable “Show advanced option” by following command.

sp_configure ‘show advanced options’, 1

reconfigure
GO

You may get message

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

 

Once you are done with that, you can see “Ad Hoc Distributed queries” in list of sp_configure. You have to set “Run_value” to “1”. If it is “0” than run following command.

 

sp_configure ‘Ad Hoc Distributed Queries’, 1

reconfigure
GO

Which will show you following message.

Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Once you will go through the procedure described here, you will be able to run OpenRowSet and OpenDataSource query.

 

Reference: Ritesh Shah

Advertisements

4 Responses to “Enable “Ad Hoc Distributed queries” by sp_configure in SQL-Server 2005”

  1. Mahendra Says:

    OLE DB provider “SQLNCLI” for linked server “MyLinkedServer” returned message “Deferred prepare could not be completed.”

    I am getting the above error when query the linked server database table.
    Msg 8180, Level 16, State 1, Line 1
    Statement(s) could not be prepared.
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘db.myDatebase.Table1’.

    MyQuery:

    select *
    from openquery(MyLinkedServer, ‘select * from db.myDatebase.Table1’

    Any solution?

  2. Nitin Says:

    Hi,

    How i can use

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

    in my Stored Procedure??


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

%d bloggers like this: