NCOA Failed to Process

[MCDaws]

Please go through all the tasks below

• Check this box only after you have successfully completed both the above tasks

Please provide a brief description of the problem. Please do not forget to attach the relevant screenshots from your side.

Our NCOA was supposed to run on 7/12/23. I received an error report, and on 7/16/23 went to System Settings>Spark Data Settings>NCOA to run it manually. I received a very similar error report. The regular job that runs several times a day currently returns 0 information (the page is literally blank with 0 entries) and the quarterly run will not process.

Expected Behavior

Expected Behavior: NCOA runs and returns a set of addresses that we need to manually check

Actual Behavior

Actual Behavior: Error report says:
Error:

Quartz.SchedulerException: Job threw an unhandled exception. ---> System.AggregateException: NCOA job failed. ---> Rock.Jobs.NoRetryAggregateException: Failed to process NCOA export. ---> System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.ArgumentException: Parameter value '10342.29' is out of range. at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam) at 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) at 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) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary2 identifierValues, List1 generatedValues) at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() --- End of inner exception stack trace --- at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) at System.Data.Entity.Internal.InternalContext.SaveChanges() --- End of inner exception stack trace --- at System.Data.Entity.Internal.InternalContext.SaveChanges() at Rock.Data.DbContext.SaveChanges(SaveChangesArgs args) in C:\GitHub\Rock14\Rock\Data\DbContext.cs:line 330 at Rock.Data.DbContext.SaveChanges(Boolean disablePrePostProcessing) in C:\GitHub\Rock14\Rock\Data\DbContext.cs:line 261 at Rock.Utility.Ncoa.PendingExport(SparkDataConfig sparkDataConfig) in C:\GitHub\Rock14\Rock\Utility\Ncoa.cs:line 333 --- End of inner exception stack trace --- at Rock.Utility.Ncoa.PendingExport(SparkDataConfig sparkDataConfig) in C:\GitHub\Rock14\Rock\Utility\Ncoa.cs:line 352 at Rock.Jobs.GetNcoa.Execute(IJobExecutionContext context) in C:\GitHub\Rock14\Rock\Jobs\GetNcoa.cs:line 100 --- End of inner exception stack trace --- at Rock.Jobs.GetNcoa.Execute(IJobExecutionContext context) in C:\GitHub\Rock14\Rock\Jobs\GetNcoa.cs:line 154 at Quartz.Core.JobRunShell.Run() --- End of inner exception stack trace --- [See nested exception: System.AggregateException: NCOA job failed. ---> Rock.Jobs.NoRetryAggregateException: Failed to process NCOA export. ---> System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.ArgumentException: Parameter value '10342.29' is out of range. at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam) at 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) at 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) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary2 identifierValues, List1 generatedValues) at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() --- End of inner exception stack trace --- at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) at System.Data.Entity.Internal.InternalContext.SaveChanges() --- End of inner exception stack trace --- at System.Data.Entity.Internal.InternalContext.SaveChanges() at Rock.Data.DbContext.SaveChanges(SaveChangesArgs args) in C:\GitHub\Rock14\Rock\Data\DbContext.cs:line 330 at Rock.Data.DbContext.SaveChanges(Boolean disablePrePostProcessing) in C:\GitHub\Rock14\Rock\Data\DbContext.cs:line 261 at Rock.Utility.Ncoa.PendingExport(SparkDataConfig sparkDataConfig) in C:\GitHub\Rock14\Rock\Utility\Ncoa.cs:line 333 --- End of inner exception stack trace --- at Rock.Utility.Ncoa.PendingExport(SparkDataConfig sparkDataConfig) in C:\GitHub\Rock14\Rock\Utility\Ncoa.cs:line 352 at Rock.Jobs.GetNcoa.Execute(IJobExecutionContext context) in C:\GitHub\Rock14\Rock\Jobs\GetNcoa.cs:line 100 --- End of inner exception stack trace --- at Rock.Jobs.GetNcoa.Execute(IJobExecutionContext context) in C:\GitHub\Rock14\Rock\Jobs\GetNcoa.cs:line 154 at Quartz.Core.JobRunShell.Run() ---> (Inner Exception #0) Rock.Jobs.NoRetryAggregateException: Failed to process NCOA export. ---> System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.ArgumentException: Parameter value '10342.29' is out of range. at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam) at 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) at 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) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary2 identifierValues, List1 generatedValues) at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() --- End of inner exception stack trace --- at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) at System.Data.Entity.Internal.InternalContext.SaveChanges() --- End of inner exception stack trace --- at System.Data.Entity.Internal.InternalContext.SaveChanges() at Rock.Data.DbContext.SaveChanges(SaveChangesArgs args) in C:\GitHub\Rock14\Rock\Data\DbContext.cs:line 330 at Rock.Data.DbContext.SaveChanges(Boolean disablePrePostProcessing) in C:\GitHub\Rock14\Rock\Data\DbContext.cs:line 261 at Rock.Utility.Ncoa.PendingExport(SparkDataConfig sparkDataConfig) in C:\GitHub\Rock14\Rock\Utility\Ncoa.cs:line 333 --- End of inner exception stack trace --- at Rock.Utility.Ncoa.PendingExport(SparkDataConfig sparkDataConfig) in C:\GitHub\Rock14\Rock\Utility\Ncoa.cs:line 352 at Rock.Jobs.GetNcoa.Execute(IJobExecutionContext context) in C:\GitHub\Rock14\Rock\Jobs\GetNcoa.cs:line 100 ---> (Inner Exception #0) System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.ArgumentException: Parameter value '10342.29' is out of range. at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam) at 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) at 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) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary2 identifierValues, List1 generatedValues) at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() --- End of inner exception stack trace --- at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) at System.Data.Entity.Internal.InternalContext.SaveChanges() --- End of inner exception stack trace --- at System.Data.Entity.Internal.InternalContext.SaveChanges() at Rock.Data.DbContext.SaveChanges(SaveChangesArgs args) in C:\GitHub\Rock14\Rock\Data\DbContext.cs:line 330 at Rock.Data.DbContext.SaveChanges(Boolean disablePrePostProcessing) in C:\GitHub\Rock14\Rock\Data\DbContext.cs:line 261 at Rock.Utility.Ncoa.PendingExport(SparkDataConfig sparkDataConfig) in C:\GitHub\Rock14\Rock\Utility\Ncoa.cs:line 333<--- <--- ]

Steps to Reproduce

• Go to Admin Tools > System Settings > Spark Data Settings > NCOA
• Click Run Manually

Rock Version

Rock McKinley 14.2 (1.14.2.1)

Client Culture Setting

en-US

[nairdo]

Greetings @MCDaws Based on the details above, it looks like there is likely bad data inside the data that Rock is downloading from the NCOA system. If this problem is still occurring, please email us at info @ sparkdevnetwork.org and include your church/organization name (and domain) along with this issue number for reference**. That should allow us to work with the NCOA system to identify (and hopefully fix) the offending record.

---

** Also, if you're handy with the SQL Command (under Power Tools in Rock) can you also include the output of this query:
SELECT [DefaultValue] FROM [Attribute] WHERE [key] = 'core_SparkData'

[chead4]

@MCDaws We are closing this issue as we have not heard back from you. If you experience this issue again, comment on this ticket and we can reopen this issue.

** Also, if you're handy with the SQL Command (under Power Tools in Rock) can you also include the output of this query:
SELECT [DefaultValue] FROM [Attribute] WHERE [key] = 'core_SparkData'

[MCDaws]

Last I heard, one of the developers was digging deeper to find a resolution, reaching out to the NCOA provider if needed. Our NCOA process is still not working. When I ran the SQL requested, this was the result: [Redacted info here.... moved it to internal Asana task for privacy - JME]

[chead4]

@MCDaws Thank you for getting back to me and for providing the SQL details. I have reopened this issue.

[MCDaws]

Thank you!

…

On Thu, Nov 9, 2023 at 3:10 PM Colleen Head ***@***.***> wrote: Reopened #5523 <#5523>. — Reply to this email directly, view it on GitHub <#5523 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BBMIAPIHI55L45OPL3GD2PLYDU2D3AVCNFSM6AAAAAA2QOIBCKVHI2DSMVQWIX3LMV45UABCJFZXG5LFIV3GK3TUJZXXI2LGNFRWC5DJN5XDWMJQHEYTONJUG44TGMI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- *Carrie Daws | *Database Manager & Ministry Analyst <https://manna.church/> <https://www.facebook.com/Manna.Church/> <https://www.instagram.com/manna.church/> <https://www.youtube.com/channel/UCUsPaKGKTWWtA8eKec1l52g?view_as=subscriber> <https://twitter.com/mannachurch> *5117 Cliffdale Rd. **Fayetteville, NC 28314* *910-867-9151*

[shauncummings]

@MCDaws Thanks for reporting this issue. The root cause was that the NCOA API service returned a value which is outside of the range specified by the documentation. We have notified the provider of this issue and added a workaround for this in v16.1.