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();
}
}