SQL Server CONFIG statement cannot be used inside a user transaction

sql, sql-server, t-sql

Solution

Apparently (to me, anyway), if you are told that you can't do what you are trying to do, you need to change your code to avoid doing what you aren't allowed to. In particular, you likely need to move the configuration/reconfiguration code outside the procedure you are calling in the INSERT statement (the `nz_test1` one), to another stored procedure, for instance. Run that code separately, probably before the insert (that may depend on what you are trying to achieve with that reconfiguration, which you aren't revealing).

So, something like this, perhaps:

CREATE PROCEDURE dbo.my_config
AS
    EXEC sp_configure 'show advanced option', 1;
    RECONFIGURE WITH OVERRIDE;
    EXEC sp_configure 'xp_cmdshell', 1;
    EXEC sp_configure 'ad hoc distributed queries', 1;
    RECONFIGURE WITH OVERRIDE;
GO
CREATE PROCEDURE dbo.nz_test1
AS
    SELECT 1 AS Value;
GO
CREATE PROCEDURE dbo.test_nz_tb3
AS
    EXEC dbo.my_config;
    CREATE TABLE #t (a varchar(10));
    INSERT INTO #t
        EXEC dbo.nz_test1;

Make sure you do not call `test_nz_tb3` within a transaction either. Otherwise you'll need to call `my_config` outside `test_nz_tb3`, likely before the transaction where the latter is called.

Problem

Original source