LP_VALIDATE_CUST_MERGE

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

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

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. 

David Vivino (Past Member) said:

Do we have a list of everything that breaks when we move from SQL 2008R2 to SQL 2016?

Other than the "raiserror" stuff above, not aware of anything in the standard code breaking due to move from 08R2 to later versions.

Hi David - back when we upgraded from 2008 R2 to 2012, we ran into the "raiseerror" issue described in this thread. We upgraded to SQL Server 2016 SP1 from 2012 in our 14.1.2 test environment, and so far have had no issues. We aren't live yet - but our experience so far mirrors what Chris said.

As Chris said (Chris Jensen), the main change for 2016 was the raiserror syntax.  Chris' fix above should help you with that.  I tried to keep a running tab of changes here:

https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_technical-9/19096/polling-v14-sql-server-2014-upgrade-experiences/58154

We have scores and scores of customized reports/tables/jobs/procedures and the two things I found there (one was RAMP specific) were the only things I tripped.

We use Throw.

DECLARE
@ERR_MSG AS NVARCHAR(MAX),
@ERR_STA AS SMALLINT

SELECT @ERR_STA = ERROR_STATE()

--------- char(13) is a line feed to make the message easier to read

SET @ERR_MSG= 'Merge not possible: Both records have renewal_value.'
+ CHAR(13) + 'please adjust per business rules for Membership (LP_VALIDATE_CUST_MERGE)';

THROW 50000, @ERR_MSG, @ERR_STA;

RETURN

We have been testing a change 2008R2 to 2016 as part of a move to RAMP.

Another thing that we found is that some SSRS reports were working OK on onsite even if you don't have IMP USERS on the stored procedure.  When we got to RAMP on 2016 these reports stopped working.  This may not be part of the 2016 change.

Thanks, Troy NelsonI see you completely bypass the Raiserror command.  Let me give this a try...  I was not able to get this to work in the context of LP_VALIDATE_CUST_MERGE.  I think that it has do do with the calling procedure expecting a 0 (No Problem) and a 1 (We found a problem) returned in this context.

So this is the code set that worked on me.  Hope that this helps someone else.  Others may have cleaner and tidier ways to get this done.  Please comment.

ALTER  PROCEDURE [dbo].[LP_VALIDATE_CUST_MERGE]

( @kept_id int,

@delete_id int

)

AS

Set NoCount On  -- added auto CWR 8/9/2001

declare @kept_id_count int

declare @delete_id_count int

declare @err_msg varchar(MAX)

-- Check to see if we have a membership for delete_id

select @delete_id_count = count(*) from vxs_cust_membership

where customer_no = @delete_id and cur_record = 'Y'

-- Check to see if we have a membership for kept_id

select @kept_id_count = count(*) from vxs_cust_membership

where customer_no = @kept_id and cur_record = 'Y'

-- Do our test

If (@kept_id_count >= 1 and @delete_id_count >= 1)

  Begin

-- Oops both accounts have memberships.

SET @err_msg = 'Merge not possible: Delete and Kept IDs are both current Members! You must Deactivate one of the memberships.'

raiserror(@err_msg, 11, 2)

-- Get ready to Tell calling procedure we found a problem.

select 1

-- Return to calling procedure

return

  End

-- We made it to the bottom!

-- Get ready to tell calling procedure All OK 0 = False we don't have a problem

Select 0

-- Return to calling procedure

RETURN

Oh, I'd check LP_CONST_MERGE as well, as I see that it also had the old RAISERROR syntax in it.

Yep.  I'm seeing the same thing.  Now I'm trying to figure out how to test this.

The code here in LP_CONST_MERGE looks really old and never updated.  It is making reference to a lt_custom_example table which is empty.

I'm wondering if this is going to break the actual merges.  Not just the setup phase of declaring who should be merged with whom.  But the actual merges themselves.

And yes, this looks like it is broken as well.  Thanks, Gawain Lavers next time I'm in the office I'll take a look at this one as well.  

Yep.  I'm seeing the same thing.  Now I'm trying to figure out how to test this.

The code here in LP_CONST_MERGE looks really old and never updated.  It is making reference to a lt_custom_example table which is empty.

I'm wondering if this is going to break the actual merges.  Not just the setup phase of declaring who should be merged with whom.  But the actual merges themselves.

And yes, this looks like it is broken as well.  Thanks, Gawain Lavers next time I'm in the office I'll take a look at this one as well.  

So, LP_VALIDATE_CUST_MERGE is run whenever you try to schedule two customers to merge, and should raise an error if there are any conditions where you don't want someone to be able to schedule a merge (that's the slot for your custom code, apparently you don't let a merge go through if both customers are members).  The text returned by RAISERROR should be descriptive, since it will appear in the Merge Status Report.

LP_CONST_MERGE has a before and after section:  before is custom code you want to run just before the merge happens (which might include re-checking the same issues you wanted to flag in LP_VALIDATE_CUST_MERGE), and the after section can include custom code to run immediately after the merge.  If either raises an error then the merge is cancelled and any work is undone (i.e. a transaction is rolled back).  Typically this is used to update custom tables where you reference a customer number.

If you're not using this, all you should need to do is update the RAISERROR call, e.g.

--RAISERROR 50000 'Error Occured in Localized Procedure'
RAISERROR(@err_table, 11, 2)

Oh, I'll actually go a step further and recommend that you re-run your validation in the "before" block of LP_CONST_MERGE, since I think if anything changes with your keep/delete customer accounts between scheduling and merging, I don't think LP_VALIDATE_CUST_MERGE is run again.  I actually broke out our validation code into a function, and just run that from each procedure.