Bulk-inactivating lists

Erin Milette

If some one in your organization has you have access to SQL Server Management Studio SSMS, one could likely change the inactive column to equal 'Y' in order to do bulk inactivation.  I'm not aware of user interface way to do this in Tessitura V15.

I have access to SSMS but I'm not SQL-fluent enough to write a procedure for this myself - if you have one you'd be willing to share, I'd greatly appreciate it! 

I use something pretty basic since we have naming conventions that begin with the FY making it easy. If you have a similar set up this would be an easy way to do it:

UPDATE T_LIST SET inactive = 'Y'
WHERE list_desc like 'xxxx'

Other things I update like this: 

list categories (replace X with the category number from TR_LIST_CATEGORY) category = 'X'

tms = 'N'

tstats (analytics) = 'N' 

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 

That report sounds wonderful Katie Lachance-Duffy  - could I bug you for a copy?  It seems to be missing from https://www.tessituranetwork.com/Support/Resources/Shared-Reports 

Riffing off Madeline Dummerth (Past Member)

You could run this and seeing if it works for you.  Change the '2022-01-01' to the date of the last useful generation that works for you.

use impresario
Select * from T_LIST 
where last_used_dt < '2022-01-01'
and tms = 'N'
and tstats  = 'N' 

and it that looks good

BEGIN  TRANSACTION

UPDATE  T_LIST 
SET inactive = 'Y'
WHERE last_used_dt < '2022-01-01'
and tms = 'N'
and tstats  = 'N' 
and inactive = 'N'

SELECT * from T_list 
WHERE  last_used_dt < '2022-01-01'
and tms = 'N'
and tstats  = 'N' 

ROLLBACK TRANSACTION

-- change rollback to commit if happy and run again


-- change rollback to commit if happy and run again

Thanks Heath, I'll give this a try and double back with questions!

Hi Heath,

quick question, I keep seeing you use TRANSACTION in your forum posts. what is it and why do you use it? The SQL can work without it. 

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