The problem is that the @Active_Indicator IS NULL.

SET @Active_Indicator =

(SELECT @Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver

WHERE Active_Indicator = 1 AND TableName = @TableName)

The above code does not work.

The variable @Active_Indicator IS...

I added the table Name to the code so that it could be used to archive more that 1 table.

The logic is based on a variation of Lynn's code.

It does...

Post removed.

Ed Wagner (8/12/2015)

---

Eirikur Eiriksson (8/12/2015)

---

Welsh Corgi (8/12/2015)

---

Brandie Tarvin (8/12/2015)

---

Welsh Corgi (8/12/2015)

---

Brandie Tarvin (8/11/2015)

---

On behalf of everyone who is wondering...

Why is it...

Brandie Tarvin (8/12/2015)

---

Welsh Corgi (8/12/2015)

---

Brandie Tarvin (8/11/2015)

---

On behalf of everyone who is wondering...

Why is it unfortunate that you got someone else's...

Scott Coleman (8/10/2015)

---

One criticism would be using "WHERE DATEPART(yyyy,Call_Date)" everywhere. It is much better to set @ArchiveDate to the oldest instant you want to keep (say midnight Jan 1...

Alvin Ramard (8/11/2015)

---

Welsh Corgi (8/11/2015)

---

I striped the script down and it works.

It lacks Transaction, try, catch etc.

It also lacks the WHILE Loop and the commit size.

DECLARE @ArchiveDate DateTime

SET @ArchiveDate...

Brandie Tarvin (8/11/2015)

---

On behalf of everyone who is wondering...

Why is it unfortunate that you got someone else's code to work? Usually...

Lynn Pettis (8/11/2015)

---

Personally, I'd start with something like this and modify it as needed:

declare @BatchSize int = 5000, -- change as desired

@ArchiveDate...

I striped the script down and it works.

It lacks Transaction, try, catch etc.

It also lacks the WHILE Loop and the commit size.

DECLARE @ArchiveDate DateTime

SET @ArchiveDate = '2002-01-01' --...

Does anyone know of an Archive Delete Utility that I could pay for?

Ed Wagner (8/11/2015)

---

It sounds like this is a maintenance process that should be run off-hours.

You have calls in your table from the year 1899? :ermm:

Did you try to update the...

Great code but it is not doing what I would expect.

Before I run the script for the first time I have the following in the table.

CallDateYearRecordDate

NULL46

189924

19981295

19997967

20006600

200150483

200267137

200370068

200467311

200568502

200668595

200784438

2008104377

2009151171

2010200165

2011232679

2012242464

2013285255

2014355890

2015236886

After I run it there...

GilaMonster (8/10/2015)

---

If you want it in a transaction so you can roll it back, it is pointless to do it...

I had a Loop to limit the batch size. What is the alternative?

I added the TRY CATCH.

There are only 25 records and it is taking for ever.

The only DML that...