News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics AX (Archived) Force Table Sync to cr...

Microsoft Dynamics AX (Archived)

Force Table Sync to create default constraints

(0) Share

Report

Report

Posted on by Jan M.

3,221

Hi

After Datamigration from R2 to R3 we encountered that some default constraints are missing. It looks like fields on AX with deactivated config key, but existing on SQL, raise "cannot insert null into column x" on processes. If the constraint is created, all works fine. The AX table sync does not create constraints for existing fields. After dropping those on SQL DB, sync will create all correct.

Creating constraints manually or dropping fields regarding the amount of tables will consume a lot of time. Is there a way to force AX sync to recreate default constraints? Or may another workaround?

Thanks for help

Jan

*This post is locked for comments

All responses Img

All responses (1)

Answers Img

Answers (0)

Suggested answer

JohnLBevan 5 on at

Like (0)

Report
RE: Force Table Sync to create default constraints

FYI: We encountered a similar issue with a refresh script, where we'd expected dbsync to correct the schema after we synced over the data.

Our solution was to sync the constraints over from another database which had those constraints; this will warn if there's issues applying the constraints (i.e. if source table/column doesn't exist in the target), but will sync all it can.

This uses the dbatools powershell module

$options = New-DbaScriptingOption $options.PrimaryObject = $false $options.DriChecks = $true $options.DriDefaults = $true $sourceTables = Get-DbaDbTable -SqlInstance 'SourceInstance' -Database 'SourceCatalog' foreach ($table in $sourceTables) { Write-Verbose "Updating constraints on [$($table.Name)]" $scripts = $table | Export-DbaScript -ScriptingOptionsObject $options -Passthru foreach ($script in $scripts) { Invoke-DbaQuery -SqlInstance 'DestinationInstance' -Database 'DestinationCatalog' -Query $script } }

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2

Martin Dráb 231,307 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans