locked
"Ad Hoc Distribution Queries" is not support by this edition of SQL server and cannot be changed using sp_configure RRS feed

  • Question

  • Hello everyone, your advice is greatly appreciated..

    I am trying to automate the cleanup of the WSUS WID database using the query found here:

    http://blog.ctglobalservices.com/configuration-manager-sccm/kea/house-of-cardsthe-configmgr-software-update-point-and-wsus/

    The section with the query is:  The WSUS Server Cleanup Utility

    This particular line:

    SELECT TOP (1000)*INTO#MyTempTable

    FROMOPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=yes;','EXEC susdb.dbo.spGetObsoleteUpdatesToCleanup')

    Causes the error:

    Msg 15392, Level 16, State 1, Procedure sp_configure, Line 166 [Batch Start Line 13]

    The specified option 'Ad Hoc Distributed Queries' is not supported by this edition of SQL Server and cannot be changed using sp_configure.

    Msg 15281, Level 16, State 1, Line 19

    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', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

    So I tried running this but get the same error:

    exec sp_configure'allow updates'
    go

    sp_configure 'show advanced options',1; 
    GO

    RECONFIGURE withoverride;
    GO

    sp_configure 'Ad Hoc Distributed Queries',1; 
    RECONFIGURE withoverride; 
    GO

    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

    Msg 15392, Level 16, State 1, Procedure sp_configure, Line 166 [Batch Start Line 6]

    The specified option 'Ad Hoc Distributed Queries' is not supported by this edition of SQL Server and cannot be changed using sp_configure.

    So am I out of luck? thanks

    Andy

    Environment:
    2016 Server (virtual machine on HyperV) is the WSUS server
    Database is c:\Windows\WID\Data\SUSDB
    SSMS is SQL Server 2016 Management Studio (13.0.16000.28)
    Connection to DB: \\.\pipe\MICROSOFT##WID\tsql\query


     



    Andy

    Wednesday, February 28, 2018 9:07 PM

Answers

  • Hi Andy,

    consider using the official guidance:

    https://blogs.technet.microsoft.com/configurationmgr/2016/01/26/the-complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maintenance/

    Best regards,

    Andrei


    We could change the world, if God would give us the source code.

    • Marked as answer by AndySpecial Wednesday, February 28, 2018 9:55 PM
    Wednesday, February 28, 2018 9:28 PM

All replies

  • Hi Andy,

    consider using the official guidance:

    https://blogs.technet.microsoft.com/configurationmgr/2016/01/26/the-complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maintenance/

    Best regards,

    Andrei


    We could change the world, if God would give us the source code.

    • Marked as answer by AndySpecial Wednesday, February 28, 2018 9:55 PM
    Wednesday, February 28, 2018 9:28 PM
  • Hi Andrei,

    that is a very comprehensive article!

    I will mark your answer as correct and see no problem with implementing the solution suggested there.

     thank you!

    andy


    Andy

    Wednesday, February 28, 2018 9:55 PM
  • Hi Andrei,

    I need your help locating the wsusmaintenance script for sqlcmd for WID please.

    In the article you linked, it has a link to the MS page to get the script...but the script is no longer available.

    NOTE For WID, you may want to run SQL Server Management Studio Express as administrator if you were not the person who installed WSUS.

    TIP Alternatively, you can also use a utility called sqlcmd to run the script if it is installed. See the following TechNet documentation for more information:

    Reindex the WSUS Database

    ...that link takes you to this page:

    https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/dd939795(v=ws.10)

    Yet another link to download the sql script...says it is not available.

    Much appreciated if you know of a different location I can use.

    cheers

    Andy


    Andy

    Thursday, March 1, 2018 8:37 PM
  • Hi Andy,

    I will put in a request to correct that, until then, here is the URL you should have been redirected to:

    https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61

    Best regards,

    Andrei


    We could change the world, if God would give us the source code.

    Thursday, March 1, 2018 8:47 PM
  • sweet! thanks Andrei!

    Andy

    Thursday, March 1, 2018 8:55 PM
  • No problem, happy deployment!

    We could change the world, if God would give us the source code.

    Thursday, March 1, 2018 9:10 PM