Need to modify Stored Procedure to create table in another database

Question

We have a SQL database that is a secondary database or should I say a secondary replica in an AlwaysOn Availabiltity Group. As a result, the database is always a read-only database and therefore I can't execute the stored procedure below because the stored procedure attempts to create a new table .. basically I get hte error message:

Failed to update database xxxxxx because the database is read-only.

It was suggested that a workaround would be to modify the procedure to SELECT ... INTO a table on a different database (that's not read-only / not part of an AlwaysOn Availability Group) that lives in the same server. E.g:

INTO SomeOtherDatabase.' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + '

Can someone please take a look at my code and help modify the code.

I tried the following:

INTO tempdb.dbo.@DeltaTable' + QUOTENAME(@Domain) + '.' + QUOTENAME(@DeltaTable) + '

The above didn't work.

The full procedure is as follows:

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;

Any thoughts on how to create the table from the stored procedure to another database

score 5 · Accepted Answer · answered Jan 19 '25 at 14:28

just to get by

To get your code working and well-oiled, I made a few changes.

Use correct data type (sysname) for objects
Add parameters for flexible database and schema names
Add parameters to optionally debug and execute the dynamic SQL
Add N prefixes to string concatenation

This should get you where you need to go. There is alternative syntax where you could use EXECUTE TargetDatabase.sys.sp_executesql but I chose to fully-qualify things in the object references instead.

CREATE OR ALTER PROCEDURE 
    dbo.GenerateDeltaTable
(
    @TargetDatabase sysname, /*Where your writes are going*/
    @OriginalDatabase sysname, /*Where your reads are coming from*/
    @TargetDomain sysname, /*Target write schema*/
    @OriginalDomain sysname, /*Originating read schema*/
    @DeltaTable sysname, /*Table for writes*/
    @OriginalTableName sysname, /*Table for reads*/
    @Debug bit = 'false', /*Print dynamic SQL for debugging*/
    @Execute bit = 'true' /*Skip executing dynamic SQL while debugging*/
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
/*Declare and set to an empty string*/
DECLARE @sql 
    nvarchar(MAX) = N'';


/Construct dynamic SQL for dropping and creating the target table/
    SET @sql = N'
    IF OBJECT_ID(N''' + 
        /Target writeable database/
        QUOTENAME(@TargetDatabase) + 
        N'.' + 
        QUOTENAME(@TargetDomain) + 
        N'.' + 
        QUOTENAME(@DeltaTable) + N''', N''U'') IS NOT NULL
    BEGIN
        DROP TABLE ' + 
            /Target writeable database/
            QUOTENAME(@TargetDatabase) + 
            N'.' + 
            QUOTENAME(@TargetDomain) + 
            N'.' + 
            QUOTENAME(@DeltaTable) + 
            N';
    END;
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(@TargetDatabase) + 
    N'.' + 
    QUOTENAME(@TargetDomain) + 
    N'.' + 
    QUOTENAME(@DeltaTable) + 
    N'
FROM ' + 
    QUOTENAME(@OriginalDatabase) + 
    N'.' + 
    QUOTENAME(@OriginalDomain) + 
    N'.' + 
    QUOTENAME(@OriginalTableName) + 
    N' AS T;';

/*Print to debug syntax issues*/
IF @Debug = 'true'
BEGIN
    PRINT @sql;
END;

/*Execute when not debugging*/
IF @Execute = 'true'
BEGIN
    EXECUTE sys.sp_executesql 
        @sql;
END;

END;
GO 
RETURN;

Locally, I can execute this repeatedly without error:

EXECUTE dbo.GenerateDeltaTable
    @TargetDatabase = 'tempdb', 
    @OriginalDatabase = 'Crap', 
    @TargetDomain = 'dbo', 
    @OriginalDomain = 'dbo', 
    @DeltaTable = 'FirstNamesDelta', 
    @OriginalTableName = 'FirstNames', 
    @Debug = 'true', 
    @Execute = 'true';

Which generates T-SQL that looks like this:

IF OBJECT_ID(N'[tempdb].[dbo].[FirstNamesDelta]', N'U') IS NOT NULL
BEGIN
    DROP TABLE [tempdb].[dbo].[FirstNamesDelta];
END;
SELECT 
    T., 
    LOWER
    (
        CONVERT
        (
            varchar(64), 
            HASHBYTES
            (
                'SHA2_256', 
                (
                    SELECT 
                        T. 
                    FOR 
                        JSON PATH, 
                        WITHOUT_ARRAY_WRAPPER, 
                        INCLUDE_NULL_VALUES
                )
            ),
            2
        )
    ) AS signature 
INTO [tempdb].[dbo].[FirstNamesDelta]
FROM [Crap].[dbo].[FirstNames] AS T;

I do not have an Availability Group set up, so if there are further errors related to that, they would be up to you to troubleshoot, or put into a new question.

Need to modify Stored Procedure to create table in another database

1 Answers1

just to get by