Hi everyone!
I'm hoping someone will have some guidance on how to fix an error created by the Manage Special Activity Utility.
Someone recently utilized the Manage Special Activity utility but unfortunately used an incorrect List of patrons and created incorrect data for the wrong people. The activity is a way for our development staff to track specific committee/board meetings. They were able to update the activity to imply that the 'event' was cancelled, which was a quick fix, but we'd like for the individuals who received this event in error to have that table updated.
How would one go about fixing this?
I'm still learning some SQL, but the only thing I can see is the table TR_SPECIAL_ACTIVITY and I know the ID of the activity we want to take away and I know the List No of the individuals who incorrectly updated. The problem is, I only want to delete the entry that was created for the incorrect list at that moment... not delete the activity from the table altogether. That's where I'm stuck.
When I look at the data object: RP_GLOBAL_ACTIVITY_INSERT_UPDATE, it says the mode must be in "I" or "U" (insert or Update), no "D" (delete/drop) option. :-(
Does anyone have any suggestions how to delete this insert made in error before I head to our support people and bother them? :)
Thanks,
Tiffany Evans
Database Coordinator
Huntington Theatre Company
Boston, MA
I should clarify... by "error" I mean it was created by mistake.
Well to delete from that table it would be:
Delete sa
From T_SPECIAL_ACTIVITY sa
JOIN T_LIST_CONTENTS lc on sa.customer_no = lc.customer_no
Where sp_act IN (<special_act_number>)
AND lc.list_no IN (<list_no>)
First you want to make absolutely certain that you have the correct records.
Go to TR_SPECIAL_ACTIVITY and determine the id of the item that was inserted in error. Let’s say it is “29.”
Go to List Manager and get the number of the list that was used to create the bad records that you want to get rid of. Let’s say it is 12000.
In Microsoft SQL Server Management Studio, run the following query:
select * from t_special_activity
where sp_act = 29
and customer_no in (select customer_no from T_LIST_CONTENTS where list_no = 12000)
If your activity is one that gets used for several activities, you may get too many results to find a sample of what you’re looking for to delete. Try to limit your results by using one of the other fields. For instance, you might include the date of the activity:
and sp_act_dt = '2013-04-28 00:00:00.000'
Check your results to make sure that you have exactly the number of patrons that you expect to have.
Once you are happy that you are only pulling what you want to delete, then update the query to read:
delete t_special_activity
______________________________Lucie SpielerIT Development and Training ManagerFLORIDA GRAND opera
Oh!
That was super easy.
Thank you for your help, Renville!! J
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Renville Williams Sent: Tuesday, October 08, 2013 2:16 PM To: Tiffany Evans Subject: Re: [Tessitura Technical Forum] TR_SPECIAL_ACTIVITY rollback?
From: Tiffany Evans <bounce-tiffanyevans7088@tessituranetwork.com> Sent: 10/8/2013 12:34:55 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Just want to warn readers of this thread that if the special activity id (sp_act) refers to an activity that gets used for more than one specific event, such as, in our case, the sp_act number that is used for “board meeting,” you will delete ALL activities of this type for the patrons on your list, not just the specific date that is incorrect. You’ll need to limit your delete to activities created at a certain time, or occurring on a specific date.
Thank you so much for your help, Lucie.
I also tried everything in Test first, just to make sure. J
Recipient of the 2013 Regional Theatre Tony Award
Office: 281 Huntington Avenue | Boston, MA 02115
617-273-1658
huntingtontheatre.org
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Lucie Spieler Sent: Tuesday, October 08, 2013 4:16 PM To: Tiffany Evans Subject: RE: [Tessitura Technical Forum] TR_SPECIAL_ACTIVITY rollback?
______________________________ Lucie Spieler IT Development and Training Manager FLORIDA GRAND opera