1

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();
        }
    }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Randy Slavey
  • 544
  • 4
  • 19

0 Answers0