SqlPackage 导入文件到本地数据库时发生以下错误

Question

SqlPackage /Action:Import /sf:F:\renesolapower-UATbackup.bacpac /tsn:localhost /tdn:renesolapower-live /tu:sa /tp:dell@123 /Tec:False

正在导入到服务器“localhost”上的数据库“renesolapower-live”。

正在创建部署计划

初始化部署

正在验证部署计划

分析部署计划

将包架构和数据导入数据库

更新数据库

*** 导入数据库时出错: 无法导入包。

Error SQL72014: Framework Microsoft SqlClient Data Provider: 消息 273，级别 16，状态 1，过程 FINTAG_INSTEADOFINSERTTRIGGER，第 41 行不能将显式值插入时间戳列。请对列列表使用 INSERT 来排除时间戳列，或将 DEFAULT 插入时间戳列。

Error SQL72045: 脚本执行错误。执行的脚本:

CREATE TRIGGER [dbo].[FINTAG_INSTEADOFINSERTTRIGGER]

ON [dbo].FinTag

INSTEAD OF INSERT

AS BEGIN

       DECLARE @ErrorLevel AS INT = 0;

       DECLARE @Delimiter AS CHAR;

       DECLARE @IsDelimiterInValues AS BIT;

       DECLARE @ErrorDelimiter AS CHAR = '!';

       SELECT @Delimiter = CASE (SELECT DELIMITER

                                 FROM   FINTAGPARAMETERS

                                 WHERE  PARTITION = (SELECT PARTITION

                                                     FROM   INSERTED)) WHEN 1 THEN '|' WHEN 2 THEN '.' WHEN 3 THEN '_' WHEN 4 THEN '-' WHEN 5 THEN '~' ELSE @ErrorDelimiter END;

       SELECT @IsDelimiterInValues = CASE WHEN CHARINDEX(@Delimiter, concat(TAG01, TAG02, TAG03, TAG04, TAG05, TAG06, TAG07, TAG08, TAG09, TAG10, TAG11, TAG12, TAG13, TAG14, TAG15, TAG16, TAG17, TAG18, TAG19, TAG20)) > 0 THEN 1 ELSE 0 END

       FROM   inserted;

       DECLARE @Hash AS NVARCHAR (64);

       DECLARE @DisplayValue AS N

Answer

Welcome to Microsoft Learn ! First, don't forget to share your issue in English so we can help you :D

From the error message, I can say that :

The Error SQL72014 is relared to the script is trying to insert an explicit value into a timestamp column.
The error SQL72045 is a general script execution error.

The CREATE TRIGGER statement in your script is trying to insert values into a timestamp column directly. SQL Server doesn't allow explicit values to be inserted into timestamp columns. The timestamp (or rowversion) column should be automatically managed by SQL Server.

In this case, you need to modify the trigger so it doesn't explicitly insert values into the timestamp column and ensure that the timestamp column is excluded from the INSERT statement.

Then, update the .bacpac file to remove or alter the offending trigger before importing it into your database.


CREATE TRIGGER [dbo].[FINTAG_INSTEADOFINSERTTRIGGER]

ON [dbo].[FinTag]

INSTEAD OF INSERT

AS 

BEGIN

    DECLARE @ErrorLevel AS INT = 0;

    DECLARE @Delimiter AS CHAR;

    DECLARE @IsDelimiterInValues AS BIT;

    DECLARE @ErrorDelimiter AS CHAR = '!';

    SELECT @Delimiter = CASE (SELECT DELIMITER

                              FROM FINTAGPARAMETERS

                              WHERE PARTITION = (SELECT PARTITION

                                                 FROM INSERTED)) 

                        WHEN 1 THEN '|' 

                        WHEN 2 THEN '.' 

                        WHEN 3 THEN '_' 

                        WHEN 4 THEN '-' 

                        WHEN 5 THEN '~' 

                        ELSE @ErrorDelimiter 

                        END;

    SELECT @IsDelimiterInValues = CASE 

                                  WHEN CHARINDEX(@Delimiter, concat(TAG01, TAG02, TAG03, TAG04, TAG05, TAG06, TAG07, TAG08, TAG09, TAG10, TAG11, TAG12, TAG13, TAG14, TAG15, TAG16, TAG17, TAG18, TAG19, TAG20)) > 0 

                                  THEN 1 

                                  ELSE 0 

                                  END

    FROM inserted;

    DECLARE @Hash AS NVARCHAR (64);

    DECLARE @DisplayValue AS NVARCHAR(MAX);

    -- Your logic here, ensure not to include the timestamp column in INSERT

    -- Example Insert statement without timestamp column

    INSERT INTO [dbo].[FinTag] (/* columns except timestamp */)

    SELECT /* columns except timestamp */

    FROM inserted;

END;

After modifying the trigger and repackaging the .bacpac file, retry the import process:


SqlPackage /Action:Import /sf:F:\modified-renesolapower-UATbackup.bacpac /tsn:localhost /tdn:renesolapower-live /tu:sa /tp:dell@123 /Tec:False

Share via

SqlPackage 导入文件到本地数据库时发生以下错误

1 answer