SET QUOTED_IDENTIFIER Error in job and sp_MSForEachTable

Question

I have this sql server job running every 3 seconds in infinite loop. The code goes through all the tables in a certain schema, takes the values and inserts them in another database every 3 seconds:

USE simulation_db

While 1 = 1
BEGIN
    EXEC sp_MSForEachTable 
        @precommand = 'use [simulation_db];',
        @command1 = 
        '
            if ((select top(1) sy from ? ORDER BY cap desc) = 1)
            BEGIN
                EXEC sp_change_table_sync ''?'', 0;
            END
        ',
        @command2 = 
        '
            DECLARE @var NVARCHAR(MAX) = (select top(1) vari from ? where sy = 0)
            if ((@var BETWEEN 226 AND 239) OR (@var BETWEEN 241 AND 252) OR (@var BETWEEN 256 AND 258))
            BEGIN
                insert into demo.? (
                    [cap] ,
                    [val] ,
                    [qual] ,
                    [we] ,
                    [fa] ,
                    [vari] ,
                    [fi] ,
                    [sy]
                ) select top(1)
                    GETDATE(),
                    val,
                    qual,
                    we,
                    fa,
                    vari,
                    fi,
                    1
                from ? where sy = 0
            END
        ',
        @command3=
        '
            DECLARE @var NVARCHAR(MAX) = (select top(1) vari from ? where sy = 0)
            if ((@var BETWEEN 226 AND 239) OR (@var BETWEEN 241 AND 252) OR (@var BETWEEN 256 AND 258))
            BEGIN
                update ? set sy = 1 from ? tb
                join (select top(1) * from ? where sy = 0) tp 
                    ON tb.cap = tp.cap AND tb.we = tp.we
                WHERE tb.cap= tp.cap AND tb.we = tp.we
            END
        ',
        @whereand = 
        '
            and upper(schema_name(schema_id)) = ''inbox''
        '
    WAITFOR DELAY '00:00:03.000';
END

In a local environment, the code works fine, but when run on a server, it generates the SET QUOTED_IDENTIFIER error. I have tried adding the SET QUOTED_IDENTIFIER ON at the start of the first command of the sp_MSForEachTable, but it still throws the error, could anyone help me?

EDIT:

I have managed to solve the problem by adding the SET property just before the insert. Thx.

Answer 1

You want to set that at the start of your batch - i.e. before your USE statement.

Note also, from Docs:

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.