Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Error in restoring 10....
Finance forum
Suggested answer

Error in restoring 10.0.39 UAT bacpac file on Development Env

Posted on by 1,794
Hi, I'm getting below error while trying to restore 10.0.39 UAT.bacpac on development environment (10.0.39)
 
Procedure FINTAG_INSTEADOFINSERTTRIGGER, Line 41 Cannot insert an  explicit value into a timestamp column
 
Anyone faced this same issue and found a resolution from MS Support?
 
Importing to database 'UATDBApr' on server 'localhost'.
Creating deployment plan
Initializing deployment
Verifying deployment plan
Analyzing deployment plan
Importing package schema and data into database
Updating database
.//SqlPackage.exe : *** Error importing database:Could not import package.
At line:1 char:1
+ .//SqlPackage.exe /a:import /sf:C://Users//localadmin//Downloads//21Apr24. ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (*** Error impor...import package.:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 273, Level 16, State 1, Procedure FINTAG_INSTEADOFINSERTTRIGGER, Line 41 Cannot insert an 
explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
Error SQL72045: Script execution error.  The executed script:
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
Time elapsed 0:14:17.66
 
  • Suggested answer
    Mohammad Raziq Ali Profile Picture
    Mohammad Raziq Ali 2,428 on at
    Error in restoring 10.0.39 UAT bacpac file on Development Env
    Here is the fix provided by MS.
     
     
  • Mohammad Raziq Ali Profile Picture
    Mohammad Raziq Ali 2,428 on at
    Error in restoring 10.0.39 UAT bacpac file on Development Env
    Hi,
     
    Here are the steps:
     
    1. Make 2 copies of bacpac (in 2 different locations)
    2. Convert your bacpac file to .zip (just edit the file extension and change. bacpac to .zip)
    3. Copy the model.xml from the zip folder to some location.
    4. Delete the Zipped bacpac file.
    5. Now edit the model.xml file and find the SqlPermissionStatement"Grant.KillDatabaseConnection" element and remove the whole <Element>...</Element> from the xml file and save it.
    6. While using the sqlpackage.exe import action, add /mfp:"YourCopiedModel.xmlPath" to the commands, this will allow you to use the edited model.xml instead of the original one in the bacpac.

     


     

  • Suggested answer
    fsilva-jr Profile Picture
    fsilva-jr 20 on at
    Error in restoring 10.0.39 UAT bacpac file on Development Env
    When i got this error i could not find anything about it... So i connected to the Tier2 database, created a backup of this procedure, deleted it and just after to delete it exported the .bacpac file.
    After to import it to the target environment i created the procedure again.
    Didn´t found issues until today and the VM is still working!
  • Umesh Pandit Profile Picture
    Umesh Pandit 9,102 User Group Leader on at
    Error in restoring 10.0.39 UAT bacpac file on Development Env
     I have made a video that might help: https://youtu.be/DBahKcpectg?si=211mGUkje7T8JlfG
  • Suggested answer
    Navneeth Nagrajan Profile Picture
    Navneeth Nagrajan 1,339 on at
    Error in restoring 10.0.39 UAT bacpac file on Development Env
    Hi MyGz,
     
    A few questions:
    Apply the binary update 10.0.1860.38 (Version 10.0.39) on your development instance and from the environment where you extracted the DB. The issue will be resolved. 
     
    Reason for Error: (SQL Row based version tracking)
    This is due to new changes to allow row version tracking for synapse for the Fintag table. This table uses an insteadofinsert trigger which may be incompatible between the versions you are transferring data to.
     
     
     
    Happy to answer questions, if any.
     
  • Error in restoring 10.0.39 UAT bacpac file on Development Env
    I too am getting the same error.  I am using the latest SQLPackage Version 162.2.111.2.
  • fsilva-jr Profile Picture
    fsilva-jr 20 on at
    Error in restoring 10.0.39 UAT bacpac file on Development Env
    Hello!
    Can you tell me the version of the SQLPackage you are using to restore it?
  • DK-30041054-0 Profile Picture
    DK-30041054-0 2 on at
    Error in restoring 10.0.39 UAT bacpac file on Development Env
    Just to confirm - I'm getting identical error while restoring bacpac created from 10.0.39 UAT enviroment.
  • MYGz Profile Picture
    MYGz 1,794 on at
    Error in restoring 10.0.39 UAT bacpac file on Development Env
    @Layan Jwei
     
    The DB backup that we download from LCS is a .bacpac format which we import to sql using SQLPackage. What's shown in the blog is a .bak format.
     
    In the blog:
     
     
    From LCS:
     
     
     
    Although I tried importing it from SSMS following this blog:
     
    But it failed.
  • Layan Jwei Profile Picture
    Layan Jwei 4,723 Super User on at
    Error in restoring 10.0.39 UAT bacpac file on Development Env
    Hi Community member,

    I'm not sure what the issue is, but here's an article I wrote before on how to apply DB backup. Maybe you can check it out if you missed anything
    https://www.ljdynamicswritings.com/2024/03/how-to-apply-database-backup-from-lcs-to-your-d365fo-devbox-environment.html

    Thanks,
    Layan Jweihan
    Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans