HI,
We are looking into customizing this procedure to avoid merging of records are not duplicates. Can somebody shed some light on where this procedure is placed (called within another procedure or something) within Tessitura. This will give us understanding of effects of this procedure run. Thanks for your time and help.
Cheers
Harpreet
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
RAISERROR (N'Merge not possible: Delete ID is an Opera Subscriber and Keep ID is not!
Try reversing the selection.', -1, -1)
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;
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)
-- 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
-- We made it to the bottom!
-- Get ready to tell calling procedure All OK 0 = False we don't have a problem
Select 0
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.
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.
Hi Chris (and Gawain Lavers and )
This fix was very helpful. We thought we'd broken something else in 14.1.
What does the N do in "RAISERROR (N'Merge not possible..." ?
Michael Longfield said:What does the N do in "RAISERROR (N'Merge not possible..." ?
"You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT."
From:
stackoverflow.com/.../what-is-the-meaning-of-the-prefix-n-in-t-sql-statements
It casts the string as an NVARCHAR.
Don't know if I ever sent you this:
https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_technical-9/19096/polling-v14-sql-server-2014-upgrade-experiences
My experience was that there wasn't a lot. The primary issue I had revolved around changes to our RAMP environment, not SQL Server itself.