SQL Server 2014内存优化表跨库归档存储过程报错咨询

首先得明确你遇到问题的核心原因：原生编译的存储过程（带NATIVE_COMPILATION）必须启用SCHEMABINDING，而架构绑定的规则严格限制了只能引用当前数据库内的对象，且必须使用两部分名称（[schema].[object]）。你尝试在原生编译过程里直接引用其他库的TestReportArchive.dbo.Report，这违反了架构绑定的规则，所以触发了Msg4512错误。

下面针对你的场景（源为内存优化表，目标可为内存/非内存表），给出两种可行的解决方案：

方案1：拆分存储过程（适用于目标为非内存优化表） #

把归档逻辑拆成两个步骤：用原生编译过程处理源库内存优化表的数据提取，用常规存储过程处理跨库插入和源数据删除。

步骤1：源库创建原生编译的"数据提取"过程 #

这个过程只操作当前库的内存优化对象，符合架构绑定要求：

USE [TestReport] 
GO

-- 先创建内存优化的中间临时表（用于暂存要归档的数据）
CREATE TABLE [dbo].[ArchiveStaging] (
 [ReportID] [nvarchar](50) COLLATE Latin1_General_100_BIN2 NOT NULL, 
 [Year] [int] NOT NULL, 
 [DayOfYear] [int] NOT NULL, 
 [ProductType] [nvarchar](50) COLLATE Latin1_General_100_BIN2 NOT NULL, 
 [ApplicationID] [nvarchar](50) COLLATE Latin1_General_100_BIN2 NOT NULL, 
 [TotalSize] [bigint] NOT NULL DEFAULT ((0)), 
 [TotalCount] [bigint] NOT NULL DEFAULT ((0)), 
 [LastReportTimeSpan] [nvarchar](50) COLLATE Latin1_General_100_BIN2 NULL, 
 CONSTRAINT [pk_staging] PRIMARY KEY NONCLUSTERED HASH ( [ReportID], [Year], [DayOfYear], [ProductType], [ApplicationID] )
 WITH ( BUCKET_COUNT = 131072) 
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ) 
GO

-- 原生编译的提取过程
CREATE PROCEDURE [dbo].[ExtractArchiveData]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS 
BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' ) 
    DECLARE @currentdate DATETIME2 = GETDATE();
    DECLARE @maintainDay INT = 5;

    -- 清空中间表旧数据
    DELETE FROM [dbo].[ArchiveStaging];

    -- 把要归档的数据写入中间表
    INSERT INTO [dbo].[ArchiveStaging]
    SELECT [ReportID], [Year], [DayOfYear], [ProductType], [ApplicationID], [TotalSize], [TotalCount], [LastReportTimeSpan]
    FROM [dbo].[Report] 
    WHERE DATEADD(day, [DayOfYear] + @maintainDay, DATEADD(YEAR, [Year] - 1900, 0)) > @currentdate;
END;

步骤2：源库创建常规的"完成归档"过程 #

这个过程不使用原生编译，所以可以跨库操作：

USE [TestReport] 
GO

CREATE PROCEDURE [dbo].[FinishArchive]
AS 
BEGIN
    SET NOCOUNT ON;

    -- 跨库插入到目标归档表
    INSERT INTO TestReportArchive.dbo.Report
    SELECT * FROM [dbo].[ArchiveStaging];

    -- 删除源库内存优化表中已归档的数据
    DELETE FROM [dbo].[Report]
    WHERE EXISTS (
        SELECT 1 FROM [dbo].[ArchiveStaging] s
        WHERE s.ReportID = Report.ReportID
          AND s.Year = Report.Year
          AND s.DayOfYear = Report.DayOfYear
          AND s.ProductType = Report.ProductType
          AND s.ApplicationID = Report.ApplicationID
    );

    -- 清空中间表
    DELETE FROM [dbo].[ArchiveStaging];
END;

执行归档 #

-- 先提取数据到中间表
EXEC [dbo].[ExtractArchiveData];
-- 再完成跨库插入和源数据删除
EXEC [dbo].[FinishArchive];

方案2：跨库调用存储过程（适用于目标为内存优化表） #

如果目标库的归档表也是内存优化表，可以在目标库创建原生编译的插入过程，然后用源库的常规过程传递数据。

步骤1：目标库创建相同结构的表类型和插入过程 #

确保目标库的表类型和源库完全一致：

USE [TestReportArchive] 
GO

-- 创建和源库一致的内存优化表类型
CREATE TYPE [dbo].[MemoryType] AS TABLE( 
 [ReportID] [nvarchar](50) COLLATE Latin1_General_100_BIN2 NOT NULL, 
 [Year] [int] NOT NULL, 
 [DayOfYear] [int] NOT NULL, 
 [ProductType] [nvarchar](50) COLLATE Latin1_General_100_BIN2 NOT NULL, 
 [ApplicationID] [nvarchar](50) COLLATE Latin1_General_100_BIN2 NOT NULL, 
 [TotalSize] [bigint] NOT NULL, 
 [TotalCount] [bigint] NOT NULL, 
 [LastReportTimeSpan] [nvarchar](50) COLLATE Latin1_General_100_BIN2 NULL, 
 INDEX [idx] NONCLUSTERED HASH ( [ReportID], [DayOfYear] )WITH ( BUCKET_COUNT = 131072) 
 ) WITH ( MEMORY_OPTIMIZED = ON ) 
GO

-- 原生编译的插入过程
CREATE PROCEDURE [dbo].[InsertArchiveData]
    @ArchiveData [dbo].[MemoryType] READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS 
BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' ) 
    INSERT INTO [dbo].[Report]
    SELECT * FROM @ArchiveData;
END;

步骤2：源库创建常规的归档执行过程 #

USE [TestReport] 
GO

CREATE PROCEDURE [dbo].[RunArchive]
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @currentdate DATETIME2 = GETDATE();
    DECLARE @maintainDay INT = 5;
    DECLARE @ArchiveData [dbo].[MemoryType];

    -- 从源内存优化表读取要归档的数据
    INSERT INTO @ArchiveData
    SELECT [ReportID], [Year], [DayOfYear], [ProductType], [ApplicationID], [TotalSize], [TotalCount], [LastReportTimeSpan]
    FROM [dbo].[Report] 
    WHERE DATEADD(day, [DayOfYear] + @maintainDay, DATEADD(YEAR, [Year] - 1900, 0)) > @currentdate;

    -- 调用目标库的插入过程
    EXEC TestReportArchive.dbo.InsertArchiveData @ArchiveData;

    -- 删除源库已归档的数据
    DELETE FROM [dbo].[Report]
    WHERE EXISTS (
        SELECT 1 FROM @ArchiveData s
        WHERE s.ReportID = Report.ReportID
          AND s.Year = Report.Year
          AND s.DayOfYear = Report.DayOfYear
          AND s.ProductType = Report.ProductType
          AND s.ApplicationID = Report.ApplicationID
    );
END;

执行归档 #

EXEC [dbo].[RunArchive];

关键注意事项 #

1. 事务一致性：如果需要确保"插入归档表"和"删除源表数据"的原子性，可以在常规存储过程中开启分布式事务（需配置MSDTC），或者根据业务场景接受最终一致性。
2. 权限配置：确保执行存储过程的账户拥有目标库的插入权限、源库的删除和读取权限。
3. 性能优化：内存优化的中间表/表变量能大幅减少IO开销，适合处理大规模归档数据。

内容的提问来源于stack exchange，提问作者afshar