Hello All,
I'm a newbie to Tessitura but familiar with databases.
I'm looking at running this stored procedure, UP_REBUILD_SALUTATIONS, on a daily or weekly basis to refresh the "Salutation 1" and "Dear" fields on the constituent General tab screen.
It takes a list number as an input parameter. My question is, if the list is defined as a dynamic list, will this procedure trigger a dynamic refresh of the list?
Is there a way of running this stored procedure from Tessitura directly?
Thanks
Hi Ian,
You could create a report with the parameter settings you wanted the end user to have, and then write a stored procedure that takes those inputs and executes UP_REBUILD_SALUTATIONS. I would expect that the dynamic list would regenerate, but not 100% sure about this if you’re executing straight from SQL (I would probably test it out in a test environment to be sure). If it doesn't, then you could execute RP_GENERATE_LIST in you SProc before you run UP_REBUILD_SALUTATIONS – it will regenerate any list you give it, not just dynamic lists.
One last thought….depending on what your users’ needs are, you could also set UP_REBUILD_SALUTATIONS to run on a schedule using SQL Server Agent. That’s what we do and we haven’t had the need for front end users to execute it thus far.
Hope that helps,
Natasha
There is something worth noting about UP_REBUILD_SALUTATION. If you do not pass @salutation_type as a parameter it will update ONLY the default salutation type (id 0 in TR_SIGNOR). Also because @salutation_type is an integer value, you will have to run it once for each salutation type you want to update.
I tripped over this earlier. It's not necessarily a bug in the procedure, but it wasn't the behavior I expected.
- Levi
Unknown said: There is something worth noting about UP_REBUILD_SALUTATION. If you do not pass @salutation_type as a parameter it will update ONLY the default salutation type (id 0 in TR_SIGNOR). [...] It's not necessarily a bug in the procedure, but it wasn't the behavior I expected.
There is something worth noting about UP_REBUILD_SALUTATION. If you do not pass @salutation_type as a parameter it will update ONLY the default salutation type (id 0 in TR_SIGNOR). [...] It's not necessarily a bug in the procedure, but it wasn't the behavior I expected.
As I recall if you run UP_REBUILD_SALUTATION with no parameters at all, it rebuilds every salutation in TX_CUST_SAL.
Not behavior I expected either! :-)
I actually went back and looked because I was pretty sure I stated it incorrectly. The problem I ran into is that it is impossible to run the procedure for ONLY the default salutation type (TR_SIGNOR.id = 0)
If you pass in @salutation_type = 0 (meaning I only want the default type rebuilt) it runs into this in the WHERE clause when selecting the salutations:
AND (ISNULL(@salutation_type, 0) = 0 or signor = @salutation_type)
The ISNULL statement here, when evaluated for @salutation_type = 0 returns all of the salutations regardless of type.
So I ran a job to rebuild the general tab salutations each night. But instead I rebuilt every salutation in the database, overwriting a lot of careful and painstaking work by development departments who had updated control grouped salutations for VIPs like board members and major donors.
I took 3 weeks or so to identify all of the affected salutations and notify the departments involved.
I put in an enhancement request to change that one line of SQL to:
ISNULL(@salutation_type, -1) = -1
This would eliminate the problem.
Thank you all for your replies!
We'll probably build a list on a daily basis, excluding those special Development and VIP salutations, and feed that into the UP_REBUILD_SALUTATIONS which we'll schedule through SQL Server.
Problem solved.
Many thanks