2

when I run the script below all at once I get this error message:

Msg 15281, Level 16, State 1, Line 58 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.

But when I run this first and then run the full script then it is all fine.

exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

The impression that I got is that sql-server will look at the configured values in memory, and does not seem to realise that they have changed.

How can I alter this behaviour?

here is the full script:

    SET NOCOUNT ON;

    declare @prevAdvancedOptions int
    declare @prevXpCmdshell int
    declare @adhocdistque  int

-------------------------------------------------------------------------------------------         

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





select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
select @adhocdistque = cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'

PRINT @prevAdvancedOptions
PRINT @prevXpCmdshell
print @adhocdistque 


if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 1
    reconfigure
end

if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
end

if (@adhocdistque  = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
end

/* -----------------------------------------------------------------  do work - begin */


SELECT  * 
      FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
      , 'set fmtonly off exec msdb.dbo.sp_help_job')
      --where name in
      --        ( 'WebFeed UKProductOffer Offers'
            --   ,'WebFeed USProductOffer Offers'
            --   ,'WebFeed DEProductOffer Offers'
            --   ,'WebFeed ATProductOffer Offers'
            --   ,'WebFeed FRProductOffer Offers'
            --   ,'WebFeed EUProductOffer Offers'
            --   ,'WebFeed AUProductOffer Offers')


/* -----------------------------------------------------------------  do work - end */

---------------------------------------------------
-- restore the settings as they were previously
---------------------------------------------------

if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 0
    reconfigure
end

if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 0
    reconfigure
end

if (@adhocdistque = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 0
    reconfigure
end
2

I'm not sure if what you are building is a good idea or not but the code below should get it working.

You basically need to include the GO statement to separate the script into individual batches. Using a temporary table to store the original settings values allows access to the values by each of the batches.

You can't include the GO statement in a stored procedure so if you are looking to automate this process you will need to separate the script out another way (Agent job steps, SSIS, PowerShell, etc)

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;

CREATE TABLE #Settings
(
    Setting VARCHAR(100),
    Val INT
)
INSERT #Settings (Setting, Val)
SELECT 'show advanced options', cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
UNION
SELECT 'xp_cmdshell', cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
UNION
SELECT 'Ad Hoc Distributed Queries', cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'

SELECT * FROM #Settings;


if ((SELECT val FROM #Settings where Setting = 'show advanced options') = 0)
begin
    exec sp_configure 'show advanced options', 1
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'xp_cmdshell') = 0)
begin
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'Ad Hoc Distributed Queries')  = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
end
go
/* -----------------------------------------------------------------  do work - begin */

    SELECT  * 
          FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
          , 'set fmtonly off exec msdb.dbo.sp_help_job')
          --where name in
          --        ( 'WebFeed UKProductOffer Offers'
                --   ,'WebFeed USProductOffer Offers'
                --   ,'WebFeed DEProductOffer Offers'
                --   ,'WebFeed ATProductOffer Offers'
                --   ,'WebFeed FRProductOffer Offers'
                --   ,'WebFeed EUProductOffer Offers'
                --   ,'WebFeed AUProductOffer Offers')

/* -----------------------------------------------------------------  do work - end */

---------------------------------------------------
-- restore the settings as they were previously
---------------------------------------------------
go
if ((SELECT val FROM #Settings where Setting = 'xp_cmdshell') = 0)
begin
    exec sp_configure 'xp_cmdshell', 0
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'Ad Hoc Distributed Queries') = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 0
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'show advanced options') = 0)
begin
    exec sp_configure 'show advanced options', 0
    reconfigure
end

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;
1
  • 1
    You could make the configuration changes be dynamic SQL and then execute using sp_executesql. That statement could have GO within it without breaking the stored procedure. Sep 28 '16 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.