SQL Server数据库迁移后数据录入出现42000 SQLSTATE错误

问题 #

将SQL Server数据库从Windows Server 2012 R2 STD上的SQL Server 2012迁移至Windows Server 2016 STD上的SQL Server 2017，数据库保持110兼容模式，未修改SQL Server相关配置。应用已通过ODBC重新指向目标库，但用户执行数据录入/修改时触发错误。

错误信息 #

执行<sp_name>存储过程 SQLSTATS=42000 Microsoft SQL Server Native Client 10.0 必须声明标量变量

相关背景 #

• SSMS中表定义显示id列设为NOT NULL，DDL无问题
• 运行sp_blitz后，目标库被标记为“使用危险SET选项创建的对象”，指向QUOTED IDENTIFIERS设置问题，但不确定是否关联当前错误
• 引发错误的存储过程代码如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[xxxxxxxx] 
    @sTable varchar(40), 
    @lnew_id integer OUTPUT
AS  
BEGIN   
    SET @lnew_id = -1;      
    SET @sTable = UPPER(@sTable);   

    SELECT @lnew_id = id_lastused 
    FROM idnext WITH (UPDLOCK, ROWLOCK) 
    WHERE id_table = @sTable;   

    IF @@error = 0      
    BEGIN
        IF (@lnew_id IS NULL)       
        BEGIN
            SET @lnew_id = -1;          
        END

        IF (@lnew_id >= 0)      
        BEGIN
            BEGIN TRANSACTION tu    

            SET @lnew_id = @lnew_id + 1

            UPDATE idnext 
            SET id_lastused = @lnew_id 
            WHERE id_table = @sTable;

            IF (@@error = 0)                
            BEGIN
                COMMIT TRANSACTION tu;      
            END
            ELSE
            BEGIN
                ROLLBACK TRANSACTION tu;
                SET @lnew_id = -999;
            END
        END
        ELSE
        BEGIN
            SET @lnew_id = 1;       

            BEGIN TRANSACTION ti            
                INSERT INTO idnext (id_table, id_lastused) 
                VALUES (@sTable, @lnew_id);                 

                IF (@@error = 0)
                BEGIN
                    COMMIT TRANSACTION ti;      
                END
                ELSE
                BEGIN
                    ROLLBACK TRANSACTION ti;    
                    SET @lnew_id = -998;
                END
            END 
    END
    ELSE
    BEGIN
        SET @lnew_id = -997;
    END 
    RETURN (@lnew_id); 
END

排查建议 #

• 检查ODBC连接的SET选项一致性
  目标库的QUOTED_IDENTIFIER设置与源库可能存在差异。虽然存储过程创建时设为OFF，但ODBC连接可能强制启用该选项，导致存储过程执行时解析逻辑异常。可在应用端或ODBC数据源配置中，将QUOTED_IDENTIFIER设置为OFF，与存储过程创建时的选项匹配。

• 验证参数传递是否完整
  错误提示“必须声明标量变量”，大概率是应用调用存储过程时，未正确传递输出参数@lnew_id。检查应用代码中调用存储过程的逻辑，确认输出参数已正确声明并绑定，尤其是ODBC调用时的参数方向设置（需明确标记为OUTPUT）。

• 重新编译存储过程
  迁移后数据库的执行计划可能存在兼容性问题。执行以下语句重新编译目标存储过程，强制生成适配SQL Server 2017的执行计划：

  EXEC sp_recompile N'dbo.xxxxxxxx';
  

• 检查数据库级SET选项配置
  对比源库和目标库的数据库级SET选项，特别是ANSI_NULLS、QUOTED_IDENTIFIER、ANSI_WARNINGS等。执行以下语句查看当前库设置：

  SELECT name, is_ansi_nulls_on, is_quoted_identifier_on FROM sys.databases WHERE name = N'YourDatabaseName';
  

  确保目标库的关键SET选项与源库完全一致。

• 测试存储过程的直接调用
  在SSMS中直接调用存储过程，传递合法参数并查看输出，验证存储过程本身是否正常工作：

  DECLARE @output_id INT;
  EXEC dbo.xxxxxxxx @sTable = N'TestTable', @lnew_id = @output_id OUTPUT;
  SELECT @output_id;
  

  如果直接调用无错误，则问题出在应用端的参数传递或ODBC配置上；若直接调用也报错，需检查存储过程依赖的idnext表是否存在、权限是否正确。

• 检查SQL Server Native Client版本兼容性
  应用使用的是SQL Server Native Client 10.0，该版本适配SQL Server 2008/2012，与SQL Server 2017存在兼容性风险。尝试升级客户端驱动至SQL Server Native Client 11.0或最新的ODBC Driver for SQL Server，验证是否解决问题。

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