May 19, 2014 at 9:44 am
SSQL 2008 R2
1. Can someone explain why this is a happening?
2. How do I get past this error?
If I can't I have to do the changes manually.
Any help is greatly appreciated!
Just got back from vacation today and the CEO needs it to happen today.
Running this:
[Code]
update CUSTOMER
set CREDIT_LIMIT_CTL='O'
where
CREDIT_LIMIT_CTL='N'
and
CREDIT_STATUS <> 'H'
[/CODE]
Getting this error message:
Msg 512, Level 16, State 1, Procedure CustomerUpdate, Line 16
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Error message is proceeded by this repeated thousands of times:
V_AU_CONTACT
V_AU_CONTACT -> update v_contact
V_AU_CUSTOMER1 -> update v_account contact info
V_AU_CUSTOMER1 -> update v_account contact info
V_AU_CUSTOMER1 -> update v_account contact info
V_AU_CUSTOMER1 -> update v_account contact info
V_AU_CUSTOMER1 -> update v_account contact info
May 19, 2014 at 9:46 am
Do you have any triggers on that table? I suspect that is the same problem with all of your views.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 19, 2014 at 10:06 am
Yes.
There are two triggers causing this.
V_AU_CUSTOMER
V_AU_CUSTOMER1
I could disable triggers and the update works.
But I'm not sure of the impact yet.
The trigger names are the sames as the actual tables impacted.
V_AU_CUSTOMER
V_AU_CUSTOMER1
These are listed under tables not views!
These tables are clones of the customer table.
I've never encountered this before.
Why would this exist?
This is a legacy system.
May 19, 2014 at 10:13 am
TC-416047 (5/19/2014)
Yes.There are two triggers causing this.
V_AU_CUSTOMER
V_AU_CUSTOMER1
I could disable triggers and the update works.
But I'm not sure of the impact yet.
The trigger names are the sames as the actual tables impacted.
V_AU_CUSTOMER
V_AU_CUSTOMER1
These are listed under tables not views!
These tables are clones of the customer table.
I've never encountered this before.
Why would this exist?
This is a legacy system.
The triggers are not coded correctly to handle multiple row operations. I am going to guess that you have variables in your trigger.
declare @MyVar int
select @MyVar = MyValue
from inserted
Something along those lines. Even worse is that somewhere in these triggers I bet you have something like this.
insert SomeAuditTable (Columns)
where MyValue = (select SomeValue from inserted)
This second code will produce the exact error you are describing. The only two ways to deal with this is to disable the triggers or fix the code in the triggers. I would suggest that fixing the problem is the right way to go. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 19, 2014 at 11:11 am
Thank you for the explanation/clarification.
This is greatly appreciated.
I can't change the trigger without breaking our support contract.
So I'll have to disable the triggers and do an update.
Again I really appreciate the help.
Thank you.
May 19, 2014 at 12:05 pm
TC-416047 (5/19/2014)
Thank you for the explanation/clarification.This is greatly appreciated.
I can't change the trigger without breaking our support contract.
So I'll have to disable the triggers and do an update.
Again I really appreciate the help.
Thank you.
Then I would go back to the vendor and tell them to fix their code. Triggers like that are unacceptable. There is a story around here where a company they worked for actually went under because their triggers could not handle multiple row operations. It is usually not a big deal to convert the code. I wish these vendors could be held accountable for the numerous amounts of crap they deliver and the time wasted analyzing their code because it is so bad.
You may need to analyze what the triggers do and emulate the logic when you modify your data to ensure that any sort of logging etc isn't lost. Good luck!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2014 at 1:33 pm
Instead of disabling the trigger, which would affect all row updates, of course, you might want to consider using CONTEXT_INFO() to selectively bypass trigger processing just for your updates.
For example:
CREATE TRIGGER ...
ON ...
AFTER ...
AS
SET NOCOUNT ON;
IF SUBSTRING(CONTEXT_INFO, 1, 1) = 0xDD
AND SUBSTRING(CONTEXT_INFO, 2, 1) = 0xEE
RETURN;
...rest of trigger code as it appears now
Then, for your update:
SET CONTEXT_INFO 0xDDEE
UPDATE ...
SET CONTEXT_INFO 0x00
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply