Unable to execute stored procedure while Database is Synchronizing

1

We have a secondary SQL database that is constantly synchronizing with the primary SQL database. I have a stored procedure that I need to execute on the secondary database. The stored procedure creates a table as part of its process. However, because the database is constantly synchronizing I get the error message:

Failed to update database "xxxxxxx" because the database is read-only.

I have no problem executing the stored procedure on the primary database, but I really need to execute the stored procedure on the secondary database. Can someone let me know how to achieve this?

It was mentioned that I should modify the procedure so that it doesn't create a table, but instead use a temp table. Can someone help modify the my procedure to change my tables to temp tables?

CREATE PROCEDURE dbo.GenerateDeltaTable
    @Domain VARCHAR(100),
    @TableName VARCHAR(100),
    @DeltaTable VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    -- Construct dynamic SQL for dropping and creating the target table
    SET @sql = '
    IF OBJECT_ID(''' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + ''', ''U'') IS NOT NULL
        DROP TABLE ' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + ';

    SELECT T.*, 
           LOWER(CONVERT(VARCHAR(64), HASHBYTES(''SHA2_256'', 
           (SELECT T.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)), 2)) AS signature 
    INTO ' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + '
    FROM ' + QUOTENAME(@Domain) + '.' + QUOTENAME(@TableName) + ' AS T;';

-- Execute the constructed SQL
EXEC sp_executesql @sql;

END;

2

You have to modify the procedure so that it doesn't create a table in the database, it cannot make any writes or modifications on the secondary replica.

If you need to create a table, use a temp table, prefixed with #:

CREATE TABLE #myTable(col1 int .... etc.)

And have your procedure insert into that instead.