In order to perform AdHoc Querying, there is a configuration option that needs to be set. This code checks that setting, changes it if necessary, and restores the change when finished, returning the code to the original environment.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DECLARE @Show BIT, @AdHoc BIT; SELECT @Show = value_in_use FROM sys.configurations WHERE name = 'show advanced options'; SELECT @AdHoc = value_in_use FROM sys.configurations WHERE name = 'Ad Hoc Distributed Queries'; IF @AdHoc = 0 BEGIN IF @Show = 0 BEGIN EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; END; EXECUTE sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; END; -- do your work -- you might want to use TRY/CATCH to ensure that -- the following cleanup code runs. IF @AdHoc = 0 BEGIN EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE; END; IF @Show = 0 BEGIN EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE; END; |
