• Replies 29 replies
  • Subscribers 339 subscribers
  • Views 498 views
  • Users 0 members are here
Related

Bulk-inactivating lists

Erin Murillas
$organization

Is there any way to inactivate multiple lists at once? we have thousands of old lists that haven't been generated in a decade or more, and while I don't think it will cause too many disruptions if I mass delete them, I'm wondering if mass inactivating is an option, without having to go into each list's properties and inactivate them one at a time. 

  • $organization in reply to Kathleen Smith

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

  • $organization in reply to John Trimble

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

  • $organization

    Okay, I've packaged up my code and I'll send it to those of you who have expressed interest. If anyone else wants it, just DM me.

    Some things to keep in mind with deleting lists: if the list has contents (customer #s) or relationship options you can't just delete the list from the backend; you would also need to delete those contents and/or relationship options before deleting the list. And keep in mind that other dependencies exist throughout the system that might make deleting those lists less than ideal, e.g. due to dependent appeals, or a headache/something you want to avoid, e.g. foreign key references from acknowledgements, etc.

    One other thing to keep in mind: static lists might not have what we would consider an accurate last_used_dt if they're being used as a report parameter. Don't quote me on this one since I can no longer prove it because I took steps to remedy this in our instances, but just something else to keep in mind.

    On the surface this seems like a pretty straightforward task but it's slightly complex. Depending on your needs, it might be easiest and cleanest in the long run to delete the old lists  manually in the front end or check out my utility. 

    Happy list cleaning!

  • $organization in reply to Kathleen Smith

    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

  • $organization
    Brian,
     
    Here is a ticketing example that shows the concepts.
     
    Before your update/delete statement, you add “Begin Transaction.” You can also name the transaction.  This will give you an opportunity to rollback your changes. However, if you commit the transaction, you cannot roll it back.
     
    In addition to wrapping the update/delete within a transaction, I also create a Table Variable and an Output clause. The advantage of this method is that it will display your changes before you commit/rollback the transaction.
     
    SSMS should natively work this way, but it does not. So far, I’ve avoided big mistakes using this method.
     
    /*
    Changing performance name within a production season query.
     
    Uses a table variable to view changes before committing transaction.
    nc
    */
    /*
    --Use this to make sure it'll change the correct performances before running update
    declare @Prod_Season_no int = 39385
    ;
    select
    i.description
    , convert(date,p.perf_dt) [Perf_dt]
    from T_INVENTORY i
    join T_PERF p on p.perf_no = i.inv_no
    where p.prod_season_no = @Prod_Season_no
    and i.description like @Perf_Description
    ;
    */
    declare @Prod_Season_no int = 39385
    , @Perf_Description varchar(30) = 'Vendor ZooBucks%'
    ;
     
    declare @Update_Perf_Text table (inv_no int, Old_description varchar(30), New_description varchar(30))
     
    begin transaction A
     
    update T_INVENTORY
    set description = 'Vendor ZooBucks'
    OUTPUT deleted.inv_no, deleted.description, inserted.description
    into @Update_Perf_Text
    --insert the changes into the table variable.
    from T_INVENTORY i
    join T_PERF p on p.perf_no = i.inv_no
    where p.prod_season_no = @Prod_Season_no
    and i.description like @Perf_Description
    ;
    select * from @Update_Perf_Text
    --show me the changes.
    ;
    /*
    If the changes are ok, then:
    COMMIT TRANSACTION A;
     
    If not ok, then:
    ROLLBACK TRANSACTION A;
    */
     
     
  • $organization in reply to Heath Wilder

    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? 

  • $organization in reply to Erin Murillas

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

  • $organization in reply to Neil Cole

    Interesting! Thanks for the insight Neil :) That's super useful and I'll try to add TRANSACTION to some of my code. 

  • $organization in reply to Erin Murillas

    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.

  • $organization in reply to A. Freeman

    Excellent, thanks for the explanation, A!

  • $organization in reply to Erin Murillas

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

  • $organization in reply to John A. Moskal II

    I'm imagining a session at TLCC where we all sit quietly in a circle and when you feel safe get up in front of the group and start your unburdening statement with 

    "That time (in Live) ... "

  • $organization in reply to Heath Wilder

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