Combination of Dilithium and fieldFilter throws and must declare scalar @ error

[rabehaja]

Do you want to request a feature or report a bug?

Report a bug

What is the current behavior?
When I use the fieldFilter to exclude some fields from an item during serialization in combination with Dilithium, I get a "must declare scalar variable @" error when syncing. The serialization works fine and it is properly serialized, but the syncing does not work.

If the current behavior is a bug, please provide the steps to reproduce.

• Enable dilithium
• Configure a base configuration as abstract and configure the usage of fieldFilter to exclude your fields in there.
• Extend that base configuration in a two or more configurations
• Serialize your items
• Deploy to a new sitecore instance
• sync your items

What is the expected behavior?
That syncing should work with dilithium and excluded fields via fieldFilter

Please mention your Sitecore version and Unicorn version.
Sitecore: 9.1.1
unicorn: 4.1.3

[cassidydotdk]

Can you include your SQL Server Version details, please?

[rabehaja]

SQL Server 2017 (Sql server 14.0.2037.2)

[cassidydotdk]

I need to see the full configurations as well. Specifically the field filters you have configured. I also need you to switch Unicorn logging to debug level and send me that output as well.

Note to self:

Unicorn/src/Unicorn/Data/Dilithium/Sql/SqlPrecacheStore.cs

Line 301 in 8716fe4

private StringBuilder BuildSqlInStatement(Guid[] parameters, SqlCommand command, string parameterPrefix, StringBuilder debugStatementBuilder)

[rabehaja]

I uploaded the requested files to https://drive.google.com/drive/folders/1ngEqqiG12GfFVtOhJDMw9MrlGJt4kZAv?usp=sharing

The field filters configuration files can be found in Unicorn issue\unicorn serialization configuration\VGZ.Foundation.SC.Unicorn.Configuration.config but for good measure I pasted them in the comment field.

`

    <configuration name="VGZ.FieldExclusion" abstract="true" extends="VGZ.Base">
      <fieldFilter type="Rainbow.Filtering.ConfigurationFieldFilter, Rainbow" singleInstance="true">
         <!--Message Generation--> 
        <exclude fieldID="{1B963507-6176-4336-A14D-D5070C3B0286}" note="'BaseUrl field' on the Manager Root template" key="Base Url"/>
        <exclude fieldID="{943D8E5D-B924-41F8-BA1E-E0B630960E6E}" note="'Preview base URL' on the Manager Root template" key="Preview base URL" />
        <exclude fieldID="{E0614797-9A63-41C9-AA8A-7A67B490DC5D}" note="'Encoding' on the Manager Root template" key="Encoding" />
        <exclude fieldID="{FF64B61E-E68D-4BE1-BFD1-AAE311F4ACA1}" note="'Embed Images' on the Manager Root template" key="Embed Images" />
        <exclude fieldID="{97D7CB7A-1EFD-43C0-B39E-8FC28A40356F}" note="'Track Message Open' on the Manager Root template" key="Track Message Open" />
        <exclude fieldID="{8D8367E8-E45B-4D78-8C3C-86920C40CD4D}" note="'Renderer User' on the Manager Root template" key="Renderer User" />
         <!--Subscription Control--> 
        <exclude fieldID="{4EAFB5C3-FB2C-4AE5-A55D-1C257B44AB9B}" note="'Already Unsubscribed Page' on the Manager Root template" key="Already Unsubscribed Page" />
        <exclude fieldID="{6900C28C-C47B-458B-B9B0-F55C53102CC7}" note="'Email Sent Confirmation Page' on the Manager Root template" key="Email Sent Confirmation Page" />
        <exclude fieldID="{4F0EB8E2-A9D6-4F01-97BC-190CC399698B}" note="'Final Confirmation Page' on the Manager Root template" key="Final Confirmation Page" />
        <exclude fieldID="{B07634DE-C2F0-4005-8C66-433B5FB60B68}" note="'Subscription Page' on the Manager Root template" key="Subscription Page" />
        <exclude fieldID="{52D572AD-9678-423F-96E2-477528418BA1}" note="'Global Opt-out List' on the Manager Root template" key="Global Opt-out List" />
        <exclude fieldID="{54A9031B-154F-4FA6-9350-8979B56F0115}" note="'Undelivered Maximum' on the Manager Root template" key="Undelivered Maximum" />
         <!--Subscription Control--> 
        <exclude fieldID="{1B5ECBD6-FBBE-4FC8-9640-0C7597990F18}" note="'Gather Notifications' on the Manager Root template" key="Gather Notifications" />
        <exclude fieldID="{B20A0B24-0C9D-48A2-996D-B57552750BA6}" note="'Return Address' on the Manager Root template" key="Return Address" />
        <exclude fieldID="{24FCF033-40DB-4B62-A03E-AA22F9EBD8D0}" note="'POP3 Server' on the Manager Root template" key="POP3 Server" />
        <exclude fieldID="{98A7E122-73D1-4C8C-8878-48BC41487B3E}" note="'POP3 SSL' on the Manager Root template" key="POP3 SSL" />
        <exclude fieldID="{721E353A-4F5A-4F11-9136-2B6A6E6F9971}" note="'POP3 Port' on the Manager Root template" key="POP3 Port" />
        <exclude fieldID="{AFB3E366-DAB4-4E10-B61F-BC3776542BB8}" note="'POP3 User Name' on the Manager Root template" key="POP3 User Name" />
        <exclude fieldID="{1EC023FB-DBB4-4A7B-837D-55F9614737B8}" note="'POP3 Password' on the Manager Root template" key="POP3 Password" />
         <!--Default Message Header--> 
        <exclude fieldID="{69AAD409-CA36-4767-B945-ABB77F3611FD}" note="'From Name' on the Manager Root template" key="From Name" />
        <exclude fieldID="{0F383CD8-C031-4A9D-9ED0-D55217F91287}" note="'From Address' on the Manager Root template" key="From Address" />
        <exclude fieldID="{15B8E428-A2D3-4736-8708-3B6CE509AA86}" note="'Reply To' on the Manager Root template" key="Reply To" />
         <!--Send Limits--> 
        <exclude fieldID="{6487A494-C7E1-45E9-AC88-7495621820A3}" note="'Send Limits Enabled' on the Manager Root template" key="Send Limits Enabled" />
        <exclude fieldID="{DE923CF4-7B97-41E8-BCE7-8229C2224FDC}" note="'Daily Send Limit' on the Manager Root template" key="Daily Send Limit" />
        <exclude fieldID="{369EE5A2-7A9B-4594-88E9-7C360EC95C2C}" note="'Weekly Send Limit' on the Manager Root template" key="Weekly Send Limit" />
        <exclude fieldID="{04182FB9-3E6C-400B-8FC5-BA2BF6C9DC45}" note="'Monthly Send Limit' on the Manager Root template" key="Monthly Send Limit" />
         <!--Unsubscribe Handling--> 
        <exclude fieldID="{37648D0C-7A73-42EC-A2C2-875D9A79F36A}" note="'Send unsubscribe confirmations' on the Manager Root template" key="Send unsubscribe confirmations" />
        <exclude fieldID="{DB7A567F-1E47-400E-991F-1394A60BC665}" note="'Add List-Unsubscribe header to emails' on the Manager Root template" key="Add List-Unsubscribe header to emails" />
      </fieldFilter>

`

Unfortunately, I am not able to get anything from the unicorn log even in debug mode. It directly throws an application/server error

`

Server Error in '/' Application.

---

Must declare the scalar variable "@".
Must declare the scalar variable "@".
Must declare the scalar variable "@".

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@".
Must declare the scalar variable "@".
Must declare the scalar variable "@".

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Must declare the scalar variable "@". Must declare the scalar variable "@". Must declare the scalar variable "@".] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +277 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +765 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4239 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +88 System.Data.SqlClient.SqlDataReader.get_MetaData() +103 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +621 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +3382 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +707 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +83 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +302 System.Data.SqlClient.SqlCommand.ExecuteReader() +147 Unicorn.Data.Dilithium.Sql.SqlPrecacheStore.Initialize(Boolean force, IItemData[] specificRoots) +2263 Unicorn.Data.Dilithium.Pipelines.<>c__DisplayClass2_0.<ProcessInternal>b__1() +215 System.Threading.Tasks.Task1.InnerInvoke() +40 System.Threading.Tasks.Task.Execute() +71 [AggregateException: One or more errors occurred.] System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken) +1264 Unicorn.Data.Dilithium.Pipelines.InitializeDilithium.ProcessInternal(IUnicornOperationStartPipelineArgs args) +816 (Object , Object ) +14 Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args) +490 Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain, Boolean failIfNotExists) +236 Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain) +22 Unicorn.SerializationHelper.SyncConfigurations(IConfiguration[] configurations, IProgressStatus progress, ILogger additionalLogger) +294 Unicorn.ControlPanel.Pipelines.UnicornControlPanelRequest.<>c__DisplayClass3_0.<CreateResponse>b__0(IProgressStatus progress) +175 Unicorn.ControlPanel.Responses.WebConsoleResponse.ProcessInternal(IProgressStatus progress) +511 Kamsar.WebConsole.Html5WebConsole.Render(Action1 processAction) +286 Unicorn.ControlPanel.UnicornControlPanelPipelineProcessor.ProcessRequest(HttpContext context) +716 Unicorn.ControlPanel.UnicornControlPanelPipelineProcessor.Process(HttpRequestArgs args) +325 (Object , Object ) +14 Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args) +490 Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain, Boolean failIfNotExists) +236 Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain) +22 Sitecore.Web.RequestEventsHandler.OnPostAuthenticateRequest(HttpContextBase context) +214 Sitecore.Nexus.Web.HttpModule.​‏‭‬‌‪‫‭‏‭‫​‍​‮‬‭‪‏‍​‏‫‎‮​‫‮(Object , EventArgs ) +307 System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +223 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +220 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +94

`

[cassidydotdk]

Alright, thanks for this :) I will see if I have enough to get the issue reproduced on my test bench :)

[apoetzsch]

We also ran into the same error recently and found a workaround by accident - if you add at least one of the following fields to the exclude list, the error will disappear:

<!-- Exclude fields from the Statistics section on the Standard template as they are getting overwritten after item change -->
<exclude fieldID="{8CDC337E-A112-42FB-BBB4-4143751E123F}" note="'__Revision' field on the Standard template" key="Revision" />
<exclude fieldID="{D9CF14B1-FA16-4BA6-9288-E8A174D4D522}" note="'__Updated' field on the Standard template" key="Updated" />
<exclude fieldID="{BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A}" note="'__Updated by' field on the Standard template" key="UpdatedBy" />

Maybe this helps to find the real cause of the problem, @cassidydotdk