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

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. 

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