'Is there a way to not return the change with the LSN of the @from_lsn parameter when doing SQL Server change data capture?
tl;dr; Can I get changes from change data tracking where the LSN is after the LSN I pass to the function (i.e., only return new changes since that LSN, not inclusive)?
I'm setting up an Azure Timer Function to track changes to a database. I have a stored procedure that fetches the changes for the table.
This is working well, except that when the max_lsn is the same as the LSN from the last time I called it, I get the last result over and over.
What I'd like is a value I can pass so that I only get new changes based on the LSN of the last change that I looked at.
Code:
private string lastLsn = "00000054000002100007";
[FunctionName("CheckForChanges")]
public void Run([TimerTrigger("0 */1 * * * *")] TimerInfo myTimer, ILogger log)
{
using (SqlConnection sqlConn = new SqlConnection(Environment.GetEnvironmentVariable("connectionString")))
{
sqlConn.Open();
SqlCommand sp = new SqlCommand("[dbo].[get_changed_data]", sqlConn);
sp.CommandType = CommandType.StoredProcedure;
sp.Parameters.Add(new System.Data.SqlClient.SqlParameter("@lastLsn", lastLsn));
sp.Parameters.Add(new System.Data.SqlClient.SqlParameter("@tableName", "table"));
sp.Parameters.Add(new System.Data.SqlClient.SqlParameter("@schema", "schema"));
var changes = sp.ExecuteReader();
if (changes.HasRows)
{
while (changes.Read())
{
log.LogInformation($"{changes["colName"]}"); //name of the column that changed
lastLsn = Convert.ToHexString((byte[])changes["newStart"]); //LSN from sys.fn_cdc_get_max_lsn()
}
}
else
{
log.LogInformation("No changes found.");
}
sqlConn.Close();
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
