1

This is more of a curiosity, but I noticed a strange behavior today.

Note - there are no explicitly defined transactions in this code.

I have a stored procedure that takes in a user defined table type variable and a string containing another table name.

ALTER proc [TLog].[ClearRecords]
(
    @storesAndDates TLog.DeleteList READONLY,
    @table varchar(max)
)
as
begin

set nocount on;

declare @cmd nvarchar(max)

if @table = 'Coupon'
delete t from Tlog.Coupon t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'DepartmentSale'
delete t from Tlog.DepartmentSale t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'Discount'
delete t from Tlog.Discount t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'FullLineItem'
delete t from Tlog.FullLineItem t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'Media'
delete t from Tlog.Media t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'PriceOverride'
delete t from Tlog.PriceOverride t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'Promotion'
delete t from Tlog.Promotion t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'SaleExtension'
delete t from Tlog.SaleExtension t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'Tax'
delete t from Tlog.Tax t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'TicketTotal'
delete t from Tlog.TicketTotal t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'EJInfoAgeID'
delete t from Tlog.EJInfoAgeID t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'Info2PromotionTax'
delete t from Tlog.Info2PromotionTax t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate
end

I then key off the table name to delete from a particular table using values from the table variable.

This has been running fine in production, being called from an SSIS package using a script task. The data is coming from a RecordSet destination object variable.

 public void Main()
    {
        var table = "TicketTotal";
        var variable = "User::" + table + "Cleanup";

        var da = new System.Data.OleDb.OleDbDataAdapter();
        var dt = new DataTable();
        da.Fill(dt, Dts.Variables[variable].Value);

        var connVal = Dts.Variables["ConnectionString"].Value as string;
        var connStr = connVal.Replace("Provider=SQLNCLI11.1;", "");
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            // Open the connection
            if (conn.State != ConnectionState.Open)
                conn.Open();

            // Prepare a stored procedure command to execute
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[TLog].[ClearRecords]";

            // Add the parameters for the command
            cmd.Parameters.AddWithValue("@storesAndDates", dt);
            cmd.Parameters.AddWithValue("@table", table);

            // Execute the stored procedure
            cmd.ExecuteNonQuery();

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }

Today I tried to run it for many days worth of data, so rather than 70 or so records being passed in, almost 900 were passed in. When running from SSMS, the package error'd with Exception has been thrown by the target of an invocation.

When I ran the same package from Visual Studio, I watched the record count and activity from SQL. I saw the delete command running and the records were slowly decreasing. They started at around 1.5 million. At around 500k, the count started going up again until it reached the original amount. At this point Visual Studio just kept spinning thinking it was still running. I let it sit but ended up killing it.

I finally cleared the records by manually calling the stored procedure.

Any ideas as to what happened here? Thanks.

  • The delete is all done in a single statement? How long did the task run? 30 seconds? – Martin Smith May 9 '17 at 18:45
  • 2
    BTW you should add a try ... catch in your script task and in the catch block use Dts.Events.FireError to pass out the actual error message so you get something more useful than Exception has been thrown by the target of an invocation. – Martin Smith May 9 '17 at 18:48
  • @ironicmuffin Can you please add the full script of the procedure and how you call it? I'd like to try it to understand the behavior... – JohnG May 9 '17 at 19:00
  • 1
    You might want to check the SQL Server logs for any errors. It sounds like something forced it to roll back - did you run out of space for your transaction log, maybe? – RDFozz May 9 '17 at 19:31
  • 2
    My guess would be command timeout. – Martin Smith May 9 '17 at 19:34
2

It was the timeout - thanks for the help guys. I should have known better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.