bizarre query problem

Below is part of a stored proc.
This code returns an error ONLY if Field.OrderId is included in the query.
It runs ok if the OrderID is not included.
The error recieved is:
Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
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.
Running the code without field.OrderID returns..
(0 row(s) affected)
(1 row(s) affected) << ALSO: What does this represent ?
(297 row(s) affected)
CREATE TABLE [OrderRequest_Equipment] (
[OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NULL ,
[Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveEarliest] [datetime] NULL ,
[MoveLatest] [datetime] NULL ,
[UnLoadEarliest] [datetime] NULL ,
[UnLoadLatest] [datetime] NULL ,
[EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveType] [int] NULL ,
[OffRentID] [int] NULL ,
[Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
DEFAULT (0),
[FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
DEFAULT (0),
[RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
(0),
[RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
(0),
[RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
(0),
[Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trucking] [smallmoney] NULL ,
[fuel] [smallmoney] NULL ,
[JobNumber] [int] NULL ,
[UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinishedAsOf] [smalldatetime] NULL ,
CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
(
[OR_EQ_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
----
--
CREATE TABLE [DCCPendingMoves] (
[jobNumber] [int] NOT NULL
) ON [PRIMARY]
GO
THIS IS THE QUERY CAUSING THE PROBLEM...
delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
OrderRequest_Equipment Table
set IDENTITY_INSERT OrderRequest_Equipment on
insert into OrderRequest_Equipment
(OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf)
select
OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf
from bobmlt.dcc.dbo.DCCPendingMoves_ore
set IDENTITY_INSERT OrderRequest_Equipment off
Thanks in advance for any help on this...
Bob McClellan.> (1 row(s) affected) << ALSO: What does this represent ?
This is a clue. You probably have an INSERT trigger on the table that is
not written correctly to handle multi-row inserts.
Hope this helps.
Dan Guzman
SQL Server MVP
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> 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.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>|||Seems that you have a trigger defined on the table whch isn=B4t able to
process multiple rows affected.
Triggers are fired on a statement basis NOT on a row basis...
HTH, jens Suessmeyer.|||Awesome...
Thanks guys...
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> 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.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>