Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Error while importing ...
Microsoft Dynamics AX (Archived)

Error while importing the .dat file with userinfo & SecurityUserRole tables

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi ,

I have a requirement to retain the same roles to the users in our TEST environment even after the data refresh from PROD to TEST, why because there are some differences in assigned roles for the same user in PROD & Test.

So before going for data refresh in TEST i have created a definition group with a name "User Roles" under System Administration > Common > Data export/import > Definition groups.

And then i have added the below tables to the above definition group 

"UserInfo" and “SecurityUserRole”

After this, i have exported them to a comma file type file , finally i have ended up with a .dat & .def files.

Now, after the data refresh is over from PROD to TEST,

I have exported the above .dat file back to TEST as follows.

1. System Administration > Common > Data export/import > Import
2. Selected the .dat file 
3. IN the ‘Advanced’ tab below options were set to yes:
       a. Include shared tables: Yes
          b. Include system tables: Yes
            c. Update existing record: Yes
4. Confirmed dialog for import into related tables.
5.Clicked Ok in the dialog to select which tables you want to delete before import
6.Did not select any tables

After importing progress bar will shown up and i have ended up with below errors.

and there is no roles have been updated to the users.

Below is the error description:

Import database.

Cannot execute the required database operation.

The SQL database has issued an error.

An error occurred during data import.

Cannot execute the required database operation.

The record already exists.

UPDATE "DBO"."SECURITYUSERROLE" SET "PARTITION" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."PARTITION", "RECVERSION" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."RECVERSION", "VALIDTOTZID" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."VALIDTOTZID", "VALIDTO" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."VALIDTO", "VALIDFROMTZID" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."VALIDFROMTZID", "VALIDFROM" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."VALIDFROM", "ASSIGNMENTMODE" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."ASSIGNMENTMODE", "ASSIGNMENTSTATUS" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."ASSIGNMENTSTATUS", "USER_" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."USER_", "SECURITYROLE" = "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798"."SECURITYROLE" FROM "DBO"."SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798" WHERE "SYSIMPEXPRES7AC145DB738C4357AB11EA3764D19798".NEWRECID = "SECURITYUSERROLE".RECID

Cannot execute the required database operation.

The record already exists.

INSERT INTO "DBO"."USERINFO" ("RECID", "PARTITION", "RECVERSION", "GLOBALEXCELEXPORTFILEPATH", "DEFAULTPARTITION", "CLIENTACCESSLOGLEVEL", "GLOBALEXCELEXPORTLOCATION", "GLOBALEXCELEXPORTMODE", "GLOBALLISTPAGELINKMODE", "CREDENTIALRECID", "ISSUERRECID", "ACCOUNTTYPE", "SHOWMODELNAMEINAOT", "DEL_DEFAULTMODELID", "GLOBALFORMOPENMODE", "FILTERBYGRIDONBYDEFAULT", "NOTIFYTIMEZONEMISMATCH", "HOMEPAGEREFRESHDURATION", "PREFERREDCALENDAR", "PREFERREDTIMEZONE", "HELPLANGUAGE", "LANGUAGE", "EXTERNALUSER", "ENABLEDONCE", "NETWORKALIAS", "NETWORKDOMAIN", "SID", "COMPILERWARNINGLEVEL", "REPORTRIGHTMARGIN", "REPORTLEFTMARGIN", "REPORTBOTTOMMARGIN", "REPORTTOPMARGIN", "TRACEINFO", "QUERYTIMELIMIT", "AUTOLOGOFF", "COMPANY", "INFOLOGLEVEL", "PROPERTYFONTSIZE", "PROPERTYFONTNAME", "FORMFONTSIZE", "FORMFONTNAME", "REPORTFONTSIZE", "REPORTFONTNAME", "CONFIRMUPDATE", "CONFIRMDELETE", "STARTUPPROJECT", "DEL_OSACCOUNTNAME", "DEL_PASSWORD", "SHOWAOTLAYER", "DEBUGGERPOPUP", "SHOWTOOLBAR", "SHOWSTATUSLINE", "GENERALINFO", "MESSAGELIMIT", "HISTORYLIMIT", "GARBAGECOLLECTLIMIT", "AUTOUPDATE", "AUTOINFO", "DEBUGINFO", "TOOLBARINFO", "STATUSLINEINFO", "DEL_STARTUPMENU", "ENABLE", "NAME", "ID") SELECT "NEWRECID", "PARTITION", "RECVERSION", "GLOBALEXCELEXPORTFILEPATH", "DEFAULTPARTITION", "CLIENTACCESSLOGLEVEL", "GLOBALEXCELEXPORTLOCATION", "GLOBALEXCELEXPORTMODE", "GLOBALLISTPAGELINKMODE", "CREDENTIALRECID", "ISSUERRECID", "ACCOUNTTYPE", "SHOWMODELNAMEINAOT", "DEL_DEFAULTMODELID", "GLOBALFORMOPENMODE", "FILTERBYGRIDONBYDEFAULT", "NOTIFYTIMEZONEMISMATCH", "HOMEPAGEREFRESHDURATION", "PREFERREDCALENDAR", "PREFERREDTIMEZONE", "HELPLANGUAGE", "LANGUAGE", "EXTERNALUSER", "ENABLEDONCE", "NETWORKALIAS", "NETWORKDOMAIN", "SID", "COMPILERWARNINGLEVEL", "REPORTRIGHTMARGIN", "REPORTLEFTMARGIN", "REPORTBOTTOMMARGIN", "REPORTTOPMARGIN", "TRACEINFO", "QUERYTIMELIMIT", "AUTOLOGOFF", "COMPANY", "INFOLOGLEVEL", "PROPERTYFONTSIZE", "PROPERTYFONTNAME", "FORMFONTSIZE", "FORMFONTNAME", "REPORTFONTSIZE", "REPORTFONTNAME", "CONFIRMUPDATE", "CONFIRMDELETE", "STARTUPPROJECT", "DEL_OSACCOUNTNAME", "DEL_PASSWORD", "SHOWAOTLAYER", "DEBUGGERPOPUP", "SHOWTOOLBAR", "SHOWSTATUSLINE", "GENERALINFO", "MESSAGELIMIT", "HISTORYLIMIT", "GARBAGECOLLECTLIMIT", "AUTOUPDATE", "AUTOINFO", "DEBUGINFO", "TOOLBARINFO", "STATUSLINEINFO", "DEL_STARTUPMENU", "ENABLE", "NAME", "ID" FROM "T408425A86B6544BFBB6EBE6C328DCCE5" WHERE "T408425A86B6544BFBB6EBE6C328DCCE5"."CONFLICTS" = 0

The import took 13 seconds, for 1824 records.

What could  be the reason for above failure.

*This post is locked for comments

  • 5400 Profile Picture
    5400 7,160 on at
    RE: Error while importing the .dat file with userinfo & SecurityUserRole tables

    I will recommended you please use DIXF for same. User entity is OOB, you have to create SecurityUserRole custom entity for same.

  • Martin Dráb Profile Picture
    Martin Dráb 230,445 Most Valuable Professional on at
    RE: Error while importing the .dat file with userinfo & SecurityUserRole tables

    Also note that Microsoft has a tool for such data refreshes: Test Data Transfer Tool. It can be configured not to overwrite some tables (such as users and roles).

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Error while importing the .dat file with userinfo & SecurityUserRole tables

    Hi Andre,

    Thanks for your reply.

    Users are same in both test and prod environments.

    No, I have not created any new entity using the DIXF.

    Regards,

    Aravind Reddy

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at
    RE: Error while importing the .dat file with userinfo & SecurityUserRole tables

    Hi Aravind,

    The object numbers for security roles can be different per environment. So probably it is caused by this. Please compare the object numbers of the roles between your test and prod environment. Also I don't know if the users are the same in both environments.

    Have you also tried to create a new entity using the Data Import/Export Framework (DIXF)?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

photo of Muhammad Affan

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

thumbnail image of a pie chart section

Top 10 leaders for November!

Congratulations to our November super stars!

thumbnail image of the word Tip

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans