search

d365collaborative / d365fo.tools

Tools used for Dynamics 365 Finance and Operations
MIT License
250 stars 102 forks source link

:sparkles: Drop all views with `_SHARING` in their name before creating .bacpac on T1 environment #731

Open FH-Inway opened 1 year ago

FH-Inway commented 1 year ago

According to https://www.yammer.com/dynamicsaxfeedbackprograms/threads/2228926546395136, when a database sync is executed on a sandbox (T2+) environment (probably as part of a deployable package deployment), a field SYSSHARINGDATAAREAID is added to multiple tables and views are created with that field (e.g. VENDLEDGER_SHARINGVIEW).

When the sandbox database is then exported and imported to a T1 environment, the fields are removed from the tables, but the views with the now invalid field remain.

This causes an error when a .bacpac is exported from the T1 environment.

A workaround described in the Yammer thread is to drop the views before exporting the .bacpac. The views get recreated when the .bacpac is restored on a T2+ environment.

This could be accomplished with the -CustomSqlFile parameter of New-D365Bacpac. To make this more user friendly, we should consider adding the dropping of views with _SHARING in their name to Clear-SqlBacpacDatabase.sql, which gets called by New-D365Bacpac via the internal function Invoke-ClearSqlSpecificObjects

Splaxi commented 1 year ago

Is this still an issue?

As we have extended the Clear-D365BacpacObject cmdlet, to be able to delete views directly inside the bacpac file.

We might need a cmdlet to list all Views, based on a wildcard pattern - as the current cmdlets isn't supporting that - and most likely shouldn't

Splaxi commented 1 year ago

A cmdlet like: Get-D365BacpacTable, but only for objects.

Should be possible, to have it extract ALL objects of a specific type - and then filter on the name afterwards, to output the ones that matches the wildcard pattern.

Just an idea

FH-Inway commented 1 year ago

I think a Get-D365BacpacObject cmdlet in general would be useful.

In this case, I was more thinking of an option that would resolve the issue "automagically" in the background, without the user ever having to bother with it. Running Get/Clear-D365BackpacObject probably requires a similar power user knowledge as the -CustomSqlFile parameter of New-D365Bacpac.

I haven't heard here or in the Yammer thread of any further issues with this. It might have been a one time thing with a specific version of D365FO. Unless there is more interest, I consider it low priority.