Home All Groups Group Topic Archive Search About

bizarre query problem

microsoft.public.sqlserver.programming
Author
22 Dec 2005 5:02 PM
John 3:16
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.

Author
22 Dec 2005 5:09 PM
Dan Guzman
> (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

Show quote
"John 3:16" <bob***@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.
>
Author
22 Dec 2005 5:15 PM
Jens
Seems that you have a trigger defined on the table whch isn´t able to
process multiple rows affected.

Triggers are fired on a statement basis NOT on a row basis...

HTH, jens Suessmeyer.
Author
22 Dec 2005 6:12 PM
John 3:16
Awesome...
Thanks guys...



Show quote
"John 3:16" <bob***@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.
>

AddThis Social Bookmark Button

Post Other interesting topics
Why query optimizer does not select the index?
Popuate field with value from last row
Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation