locked
ad hoc queries RRS feed

  • Question

  • I'm attempting to use an ad hoc query in SQL Server 2008 R2 - Express. I take it that when I try to run sp_configure to enable this feature and it returns that the configuration does not exist or it is an advanced feature that it isn't available in Express editions.

    Is this correct?

    Friday, October 1, 2010 5:03 PM

Answers

  • It works fine in SQL Server 2005 Express, so I doubt that it has been removed from 2008 R2 Express.  (Although I leave it disabled.)   Did you enable advanced options first?  If not:

    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
    GO
    RECONFIGURE;
    GO

    RLF

     

    • Marked as answer by Wasabi_Sushi Friday, October 1, 2010 7:34 PM
    Friday, October 1, 2010 6:38 PM

All replies

  • can you tell us which option in sp_configure you are trying to modify?
    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Friday, October 1, 2010 5:43 PM
  • It works fine in SQL Server 2005 Express, so I doubt that it has been removed from 2008 R2 Express.  (Although I leave it disabled.)   Did you enable advanced options first?  If not:

    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
    GO
    RECONFIGURE;
    GO

    RLF

     

    • Marked as answer by Wasabi_Sushi Friday, October 1, 2010 7:34 PM
    Friday, October 1, 2010 6:38 PM
  • I missed the part of the sp_configure 'show advanced options', 1. I didn't see that one in any of the documents I read while searching high and low.

    Thanks

    Friday, October 1, 2010 7:36 PM