Enable Activity Feeds on Entities/Tables via Sql4Cds

[SiPurdy]

So I'm trying to use Sql4Cds.Engine as a platform for doing pre/post install actions for a MS package, foundations are in which is pretty good, but I've it an issue with trying to enable Activity feeds for entities.

I was hoping the script below would be good:

update msdyn_postconfig set
    statecode=0,
    statuscode=1
where
    statecode=1 and
    msdyn_entityname in ('cr815_newtable')
OPTION (MAXDOP 1, USE HINT('BATCH_SIZE_1'))

I have had previous where it would initially error due to updates in batches, but with MAXDOP and BATCH_SIZE_1, the error has transformed to:

You cannot start a transaction with a different isolation level than is already set on the current transaction
System.ArgumentException: You cannot start a transaction with a different isolation level than is already set on the current transaction

Checking the front end, it does let you activate the Post Config record, but it does so via a SetStateRequest, rather than the UpdateRequest (and the state/statuscode match). I haven't managed individually to get execute SetState working either :(.

Any thoughts/ideas? Thanks

[MarkMpn]

I've seen this before when a plugin is trying to make metadata changes automatically based on the data changes.

If you can show me a simple C# app that executes the SDK messages that you need to get this to work, I can try to figure out how to express those in SQL 4 CDS.

[SiPurdy]

This is what we currently have, but the main bit is the Execute of the SetStateRequest.

            extension.PackageLog.Log("Retrieving Activity Feeds to Activate");
            QueryExpression inactiveEntityQuery = new QueryExpression("msdyn_postconfig");
            inactiveEntityQuery.ColumnSet = new ColumnSet("msdyn_entityname");
            inactiveEntityQuery.Criteria = new FilterExpression(LogicalOperator.And);
            inactiveEntityQuery.Criteria.AddCondition("statecode", ConditionOperator.Equal, 1); // Inactive
            inactiveEntityQuery.Criteria.AddCondition("msdyn_entityname", ConditionOperator.In, entities.Cast<object>().ToArray());
            EntityCollection inactiveEntities = extension.CrmSvc.RetrieveMultiple(inactiveEntityQuery);
            extension.PackageLog.Log($"{inactiveEntities.Entities.Count} Entities to Enable");

            // Activate the Inactive Ones
            foreach (var currentEntity in inactiveEntities.Entities)
            {
                extension.PackageLog.Log($"Activating {currentEntity.GetAttributeValue<string>("msdyn_entityname")}");
                extension.CreateProgressItem($"Activating Activity Feed for {currentEntity.GetAttributeValue<string>("msdyn_entityname")}", PDStage.Unknown);

                extension.CrmSvc.Execute(new SetStateRequest()
                {
                    EntityMoniker = currentEntity.ToEntityReference(),
                    State = new OptionSetValue(0),
                    Status = new OptionSetValue(-1)
                });

                extension.RaiseUpdateEvent($"Activating Activity Feed for {currentEntity.GetAttributeValue<string>("msdyn_entityname")}", ProgressPanelItemStatus.Complete, PDStage.Unknown);

            }

[SiPurdy]

Thanks.
Just wondering how you'd make it work with columns other than statecode/statuscode in the same update? If hint set issue update on the other columns followed by the SetStateRequest???

[MarkMpn]

Yes, it would need to split this up into multiple requests in this case. Ideally those requests would be combined with an ExecuteTransactionRequest though that might then trigger the same error for you again

[SiPurdy]

I suspect it would.
Shame really as the c# equivalent of...

select
    msdyn_name
from msdyn_postruleconfig
where
    msdyn_name = '5EF5871D-FE40-4EB9-9952-39B0AC611895'

-- Enable Activity Feeds on Required Tables
update msdyn_postconfig set
    statecode=0,
    statuscode=0
where
    statecode=1 and
    msdyn_entityname in ('cr815_newtable')
OPTION (MAXDOP 1, USE HINT('BATCH_SIZE_1'))

-- Publish Customisations if changes have been made    
if @@ROWCOUNT > 0 begin
    exec PublishAllXml
end

...is quite a bit more verbose and difficult for non-coders to handle.

[MarkMpn]

I'm working on including this in the next version. It should also be possible to execute SetState using the stored procedure syntax but there's a bug passing in the OptionSetValue parameters which I'll try to fix at the same time. The new syntax will be:

select
    msdyn_name
from msdyn_postruleconfig
where
    msdyn_name = '5EF5871D-FE40-4EB9-9952-39B0AC611895'

-- Enable Activity Feeds on Required Tables
update msdyn_postconfig set
    statecode=0,
    statuscode=0
where
    statecode=1 and
    msdyn_entityname in ('cr815_newtable')
OPTION (MAXDOP 1, USE HINT('BATCH_SIZE_1', 'USE_LEGACY_UPDATE_MESSAGES'))

-- Publish Customisations if changes have been made    
if @@ROWCOUNT > 0 begin
    exec PublishAllXml
end

Because only these two fields are being set, the SetStateRequest will be issued directly. If you were to update other fields at the same time, these would be done in an UpdateRequest and the two requests would be combined in an ExecuteTransactionRequest - so long as you only update those fields you should then be fine to execute this as SQL.