November 9, 2010 at 5:25 am
Hi all
I am trying to get this sp to work;
USE [dbname]
GO
/****** Object: StoredProcedure [dbo].[sp_update_order_cargo] Script Date: 11/09/2010 10:02:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_update_order_cargo]
(
@XMLCargo XML
)
AS
BEGIN
UPDATE OrderTable SET
CargoReady = T.Item.value('@cargoready', 'DATETIME'),
EstPallets = T.Item.value('@estpallets', 'INT'),
EstWeight = T.Item.value('@estweight', 'INT'),
EstVolume = T.Item.value('@estvolume', 'INT')
FROM @XMLCargo.nodes('/root/row') AS T(Item)
WHERE OrderID = T.Item.value('@orderid', 'INT');
RETURN 0
END
Some sample data:
<root><row orderid='323936' ordernumber='125924' cargoready='01/11/2010 00:00:00' estpallets='1' estweight='2' estvolume='3'/><row orderid='326695' ordernumber='128176' cargoready='01/11/2010 00:00:00' estpallets='1' estweight='2' estvolume='3'/></root>
I just keep getting the error above (Subquery returned more than 1 value...)
The OrderId is the PK so I don't understand where this error is coming from.
Any help much appreciated
November 9, 2010 at 10:16 am
I had no problems with your sample data and update statement in this code:
DECLARE @XMLCargo XML;
SET @XMLCargo = '<root><row orderid=''323936'' ordernumber=''125924'' cargoready=''01/11/2010 00:00:00'' estpallets=''1'' estweight=''2'' estvolume=''3''/><row orderid=''326695'' ordernumber=''128176'' cargoready=''01/21/2010 00:00:00'' estpallets=''4'' estweight=''5'' estvolume=''6''/></root>';
DECLARE @OrderTable TABLE (
OrderID INT PRIMARY KEY CLUSTERED,
CargoReady DATETIME,
EstPallets INT,
EstWeight INT,
EstVolume INT);
INSERT INTO @OrderTable(OrderID)
VALUES (323936),(326695);
SELECT *
FROM @OrderTable;
SELECT CargoReady = T.Item.value('@cargoready', 'DATETIME'),
EstPallets = T.Item.value('@estpallets', 'INT'),
EstWeight = T.Item.value('@estweight', 'INT'),
EstVolume = T.Item.value('@estvolume', 'INT'),
OrderID = T.Item.value('@orderid', 'INT')
FROM @XMLCargo.nodes('/root/row') AS T(Item);
UPDATE @OrderTable
SET CargoReady = T.Item.value('@cargoready', 'DATETIME'),
EstPallets = T.Item.value('@estpallets', 'INT'),
EstWeight = T.Item.value('@estweight', 'INT'),
EstVolume = T.Item.value('@estvolume', 'INT')
FROM @XMLCargo.nodes('/root/row') AS T(Item)
WHERE OrderID = T.Item.value('@orderid', 'INT');
SELECT *
FROM @OrderTable;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2010 at 10:22 am
As it turned out the table being updated has got a couple of triggers on it (it's a legacy database so I am still finding things like this hidden away).
The code does work, but you have to disable the triggers then re-enable them once the update is complete.
Not sure if that's a good idea, but can't think of any other way around it!
But thanks for your help anyway
November 9, 2010 at 11:23 am
pauled2109 (11/9/2010)
As it turned out the table being updated has got a couple of triggers on it (it's a legacy database so I am still finding things like this hidden away).The code does work, but you have to disable the triggers then re-enable them once the update is complete.
Not sure if that's a good idea, but can't think of any other way around it!
But thanks for your help anyway
It sounds like your trigger needs to be re-written to properly handle multiple records. Disabling them only prevents the action that should be accomplished. If you post your trigger code, we can help with it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2010 at 12:08 pm
My code is working - but the table has 2 triggers on it which have to be disabled befoe my script will work with. I therefore modified my script to first disable the triggers, perform the record update(s) then re-enable the triggers.
But thanks
November 9, 2010 at 12:20 pm
It appears that I didn't explain properly... the triggers should not need to be disabled. In fact, they are probably performing something that is expected to be happening. If you post your triggers, we can help identify what they are generating that error, and allow them to be fixed where that can work in conjunction with your batch update. I suspect that the trigger is designed to process just one row of data, and not multiple. If so, it would be a pretty easy change.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 10, 2010 at 2:00 am
Thanks
Attached scripts for the 2 triggers and the tables they affect.
November 10, 2010 at 8:36 am
This code will make the one trigger set-based (trigger2). The other will take a bit longer... it's more complicated, and uses nested cursors. In the meanwhile, can you implement this (in TEST) and see if this fixes the error? (I think the other trigger will be causing this issue also, but I might be wrong.)
CREATE TRIGGER [dbo].[B3OrderTableUpdateTrigger] ON [dbo].[OrderTable] FOR UPDATE
AS
INSERT INTO B3OrdersUpdated (OrderNumber, DirtyType)
SELECT OrderNumber, 1
FROM inserted
WHERE CompanyID IN (5,428)
AND OrderNumber > 320000
AND OfficeIndicator = 'UK auto';
GO
Oh - see how the code is a lot easier to understand when it's done in a proper set-based fashion?
Edit: As I mentioned, the other will take a bit more work. It was designed to update only one record at a time - it's assuming only one record is being updated. It has two nested cursors. It retrieves data from tables one column at a time. It is worse than RBAR (Row-by-Agonizing-Row) - it's Column-By-Agonizing-Column-with-Row-By-Agonizing-Row!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 10, 2010 at 9:01 am
I'd just like to say I inherited this database, I didn't design it. Until I get to the point where I can migrate the data I have to work with what I've got.
November 10, 2010 at 10:40 am
No problem, I understand how that goes.
One thing about triggers - they should always be designed to handle > 1 row at a time.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy