Forum Discussion
Copy Job Triggered Twice When Executed from Pipeline (Foreach)
Hi D4zk,
Thanks for the detailed explanation.From a Copy Job perspective, the execution details that are exposed are currently limited to the activity-level logs (as you’ve already observed). These logs typically show the overall success/failure status and high-level error messages, but they do not always provide full visibility into intermediate states such as LSN evaluation or CDC window handling.
Because of this, scenarios like:
- One execution succeeding
- A subsequent execution failing
may not always have enough diagnostic detail available directly within the Copy Job monitoring experience.
You might consider validating the source query independently (in SQL Server) to better understand how it behaves across executions. If deeper visibility or control is required, implementing a custom pipeline can help with explicit state tracking and logging.
I hope this information helps. Please do let us know if you have any further queries.
Thank you!
Community SupportHi D4zk,
Thanks for the update.
From the error in your previous message:
“An insufficient number of arguments were supplied for the procedure cdc.fn_cdc_get_net_changes…”,
this usually indicates that the CDC function is not receiving the required parameters.
This function expects values like start LSN and end LSN, and if they are missing, null, or not passed correctly, it can result in this error.
You might try checking the following:
1. How the CDC function is being called in your source query or Copy Job
2. Whether the required parameters (LSN values) are being passed correctly
3. If these values are dynamic, ensure they are not empty at runtime
4. Try running the same query directly in SQL Server to validate it
If the issue still persists, as a quick troubleshooting step, you could try recreating the setup from scratch.
Since one run succeeds and another fails, it may also help to check if different parameter values are being passed in each execution.
For more details, please refer to the official documentation:
cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL) - SQL Server | Microsoft Learn
Please give it a try and let me know how it goes.
Thank you!
Hi v-abhinavmu
I understand the root cause of the issue itself. What I would like to clarify is why the job was executed twice — one run succeeded and the second run failed.
From my analysis, the failure happened because there were no new CDC changes, but the logic still attempted to proceed since from_lsn (derived value) became greater than fn_cdc_get_max_lsn(). This resulted in the error shown. However, this still raises concerns for me.
My main worry is whether the Copy Job is correctly tracking and picking up the last processed LSN. The double execution (success followed by failure) makes me unsure about the reliability and determinism of the Copy Job’s CDC state handling.
Because of these concerns—and also because the monitoring and execution history of Copy Job does not provide sufficient or meaningful diagnostic details—I am considering building and using my own custom pipeline for CDC tables instead of the built‑in Copy Job. At the moment, there seem to be too many edge cases, bugs, or opaque behaviors - I still had another opening issue post - that are difficult to validate or troubleshoot.
I’d appreciate your thoughts or confirmation on this behavior.
- v-abhinavmu
Hi D4zk,
Thanks for the detailed explanation.From a Copy Job perspective, the execution details that are exposed are currently limited to the activity-level logs (as you’ve already observed). These logs typically show the overall success/failure status and high-level error messages, but they do not always provide full visibility into intermediate states such as LSN evaluation or CDC window handling.
Because of this, scenarios like:
- One execution succeeding
- A subsequent execution failing
may not always have enough diagnostic detail available directly within the Copy Job monitoring experience.
You might consider validating the source query independently (in SQL Server) to better understand how it behaves across executions. If deeper visibility or control is required, implementing a custom pipeline can help with explicit state tracking and logging.
I hope this information helps. Please do let us know if you have any further queries.
Thank you!