LP_VALIDATE_CUST_MERGE

Hi Harpreet

LP_VALIDATE_CUST_MERGE  is called from the Merge Constituents screen,  when you click the Schedule button to attempt to schedule a merge.

If the validation fails, it returns the error message set in the script, and the screen then displays the error message, and backs out of the schedule operation. As far as I know, that's the only place it's used.

Ken

Hi Harpreet

do you think when we click on schedule button some procedure runs and LP_VALIDATE_CUST_MERGE   gets called within that procedure.

Yes. That's what happens.

 is there any quick override option.

No - there's no built-in override option. We added some code into our version of the script so that members of a particular user group can override it. Only consortium administrators have access to that group - if anybody needs to run a non-conforming merge, they ask us, we review the merge and if necessary check with other consortium members, then log on with that user group and schedule it manually.

Ken

Hi Steve

Our script is pretty much as Ryan describes it.

I've parked a copy of the full thing in my files here, but this is the relevant bit:--------------

declare @current_ug varchar(8)
--get the current ug, to enable bypassing of rules by specific user groups if needed
--
set @current_ug = dbo.fs_get_param_from_appname('ug')

--- then we do  the void merge check and any other un-overrideable checks first, and then wrap the whole checking process inside a conditional process.;

if @current_ug <> 'AdMerge'
begin --1
-- the merge validation rules

end

---------------------------------

The "AdMerge" user group is a special group used for only this purpose. If we want to override the merge validation, we have to re-login using this group, and it has no permissions to do anything else, so we have to get out again to continue working.  That's to make sure that every use of this power is deliberate - we don't want admins accidentally putting a non-conforming merge into the schedule.

Ken

We have had a customized LP_Validate_Cust_Merge for quite some time now.

However, with V12.1 we have started to get an error message every time the RaiseError is called.

On the client side it looks like this.

Invalid Entry

Merge not permitted by LP_VALIDATE_CUST_MERGE.

Warning: Null value is eliminated by an aggregate of other SET operation.

Anyone else run into this?

Anyone else have a quick solution?

Here is the type of code that is throwing the error.

if ( not (@Ug_contest like 'PlanM%' and @Ug_contest <> 'PlanMgr') and @delete_id_count > 0)

  Begin

     RAISERROR  ('Merge not possible: Only Membership can merge  %s', 16,1,@msg1)

     RETURN

  End

Hi Tom,

Yeah, we ran into that as well. in our case, I think the issue for us was due to the migration to SQL Server 2012 rather than v12.1 specifically, so there is a chance the issue we experienced is different than yours.

But, here is what I changed our RAISERROR statement to in order to get it to work:

RAISERROR(@ErrorMessage, 11, 2)

(@ErrorMessage is a varchar variable that we use to build the error message, which allows us to display multiple validation errors.)

Thanks,
David 

Unknown said:

Merge not permitted by LP_VALIDATE_CUST_MERGE.

By default LP_VALIDATE_CUST_MERGE and LP_CONST_MERGE have old raiseerror syntax that we found that SQL 2012 doesn't like.  In both sprocs I had to look for syntax like, e.g.

Begin
     RAISERROR 50000 'Merge not possible: Delete ID is an Opera Subscriber and Keep ID is not!
      Try reversing the selection.')
     RETURN
  End

And replace it with something like

Begin

     RAISERROR (N'Merge not possible: Delete ID is an Opera Subscriber and Keep ID is not!

     Try reversing the selection.', -1, -1)

     RETURN

End

An update: I just discovered today that our RAISERROR in lp_validate_cust_merge is no longer showing up in the client - we're using Tessitura 12.1.2. The procedure prevents the merge from being scheduled, but the error message isn't displaying. I can run the procedure in SSMS and the error shows up properly there. I'm opening a TASK ticket on that. I'd be curious to know if anyone else is experiencing that.

Thanks,
David 

Ugh, you can disregard that. I discovered it wasn't an issue with RAISERROR. That is working properly. Sorry for the false alarm!

We did experience that here, until we modified the LP_ stored procedure raiserror code as per my post above.

Does anyone have a working LP_VALIDATE_CUST_MERGE that works with SQL Server 2016 at Tessitura 12.5.1 that they would be willing to share?

I don't have a solution to this, but now I'm worried.  Do we have a list of everything that breaks when we move from SQL 2008R2 to SQL 2016?

We have a proc that works in SQL2014. SQL2016 is no different since compatibility mode needs to be set at 2014 levels.