Bulk-inactivating lists

Hi Erin,

There was a similar discussion on the forums a year+ ago: https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_technical-9/24883/deleting-lists/75426#75426

I'm happy to share the utility I wrote to clean up old lists, extractions, and output sets. Feel free to email me and I'll send it over. 

Madeline Dummerth (Past Member)'s suggestion is also good if you have a specific set of lists in mind! :)

Katie, thanks for pointing me to that previous discussion - I'd love to get your utility if I can! 

Madeline, appreciate the SQL! There doesn't seem to be a consistent naming convention - I'm trying to find a way to inactivate lists based on 'last generated' date instead, thinking that a very old 'last generated' date would be a good indicator if the list is still being used 

I wouldn't worry for a select but for changing data (update, delete, insert etc) I got into the habit of wrapping queries in Begin and Rollback until I'm happy with the result. I really don't want to slip and execute anything before I get the where clause right - that's a scary way of overwriting a whole table's worth of data. 

Heath Wilder is dead on.  TRANSACTION is one of those things that SQL writers can use to protect themselves from their own mistakes.  Cocky little buggers though we be (and myself very much in that category), we like to think that we can write beautiful code that does only what we intend and no worries about such amateur little trifles as mistakes.  TRANSACTION helps to keep us from mistakes that we might miss in our over-zealousness to get something done quickly.

like that one time (thankfully in TEST) I changed all the performance descriptions in our database to the same show...

Or the time (in Live) where I removed every constituent's Interests except the ones I was adding because I hadn't properly thought through my MERGE statement.

Or the time (in LIVE) when I deleted all the seats from a performance that was actively on sale and was to take place in 3 days because I thought I would get clever and try to bulk release comps from the back end.  Was able to fix fortunately, but never did anything that stupid again.

I did that with Short Name in Live before...  oops.  

I feel like this could be a support meeting at the conference, lol-  "DBAs and their Oops, I'll Never Make THAT Mistake Again" open space discussion.

That's around the time you learn about TA_AUDIT_TRAIL and pray that there is a record

Especially if it includes,  How I fixed it along with how to avoid it. 

Absolutely!  I'll try to remember to convene this discussion in August-- I think a lot of people could get something out of it.  

Also, since we went ahead and started this discussion on TRANSACTIONS, I thought I would share this.  Just because I like to fully document "standard procedures" for myself, I made this handy little file which I call "Transaction Standard Logic" as it has everything I might ever want in a TRANSACTION.

USE impresario

--Sets all errors as flaws
SET	XACT_ABORT ON

--Group for cleanliness of error handling
BEGIN

	--Open the TRY
	BEGIN TRY
	
		--Use Transaction functionality to avoid issues with whatever you are doing during this process
		BEGIN TRANSACTION trans_to_commit

			--Stuff to do here
			DECLARE	@test_variable INT
			
		--End and process everything
		COMMIT TRANSACTION trans_to_commit

	--Close the try
	END TRY

	--Open the catch
	BEGIN CATCH

		SELECT	ERROR_NUMBER() AS err_number,
				ERROR_SEVERITY() AS err_severity,
				ERROR_STATE() AS err_state,
				ERROR_LINE() AS err_line,
				ERROR_PROCEDURE() AS err_procedure,
				ERROR_MESSAGE() AS err_message

		--Test if the transaction is uncommittable
		IF	(XACT_STATE()) = -1
			BEGIN
				PRINT N'The transaction is in an uncommittable state.' + 'Rolling back transaction.'
				ROLLBACK TRANSACTION;
			END

		--Test if the transaction is still committable; theoretically impossible since XACT_ABORT is set to ON
		IF	(XACT_STATE()) = 1
			BEGIN
				PRINT N'The transaction is committable.' + 'Committing transaction.'
				COMMIT TRANSACTION
			END

	--Close the catch
	END CATCH

--Close the overall try/transaction grouping
END

Q: why is this included: "and tstats = 'N'" ? There's an Analytics property in List Critera, but no Tstats property that I can find, so do I need to replace that line with Analytics instead? 

Sorry, if it wasn't clear, that was about Heath's procedure, not John's

What used to be the T-Stats indicator - before Analytics replaced T-Stats - is now the Analytics indicator in List Manager in the application. The database column name did not change, only the on-screen text. Put another way, tstats = 'N' in Heath's SQL statement reflects the Analytics indicator unchecked in List Manager.

Excellent, thanks for the explanation, A!

Here is a visual from SSMS, but Madeline was the prompt.

Here is a visual from SSMS, but Madeline was the prompt.

Ah, I totally missed that Madeline had it in parentheses in her comment! Thanks all!