August 27, 2015 at 8:38 am
I am attempting to Insert multiple rows in a table by passing DataTable to a Stored Proc. My code is the same as the examples on the net, however when I execute the code I receive the Subquery returned more than 1 value error. Can someone please advise as to what I'm doing wrong?
I first created up a User-Defined Table Type
My stored proc is as follows:
ALTER PROCEDURE [KCC].[Insert_MaterialJobMtl]
@MtlTblMaterialType READONLY
AS
BEGIN
Set NOCOUNT ON;
Insert Into dbo.JobMtl (Company, JobNum,
MtlSeq,
PartNum,
Description,
QtyPer,
RequiredQty,
IUM,
RelatedOperation,
EstUnitCost,
WarehouseCode,
BuyIt,
BackFlush,
RevisionNum,
Character01,
Character02,
Character03,
Plant,
BaseRequiredQty,
BaseUOM,
SysRowID,
WeightUOM,
ReqRefDes,
EstMtlUnitCost,
PROGRESS_RECID)
Select 'KCC',
JobNum,
SeqNum,
PartNum,
PartDescription,
PartQtyDec,
PartQtyDec,
IUM,
RelatedOperation,
UnitOfCost,
WarehouseCode,
0,
1,
RevisionNumber,
PartAssembly,
Status,
PartType,
'LOU',
PartQtyDec,
IUM,
GuidID,
'LB',
PartQtyInt,
TotalCost,
0
From @MtlTbl
END
C# code:
if (dt.Rows.Count > 0)
{
var connectionString = GlobalKCCConnection.KCCConnectString;
using (var connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("KCC.Insert_MaterialJobMtl", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Connection = connection;
cmd.Parameters.AddWithValue("@MtlTbl", dt);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
}
}
August 27, 2015 at 8:47 am
That code cannot throw the error you've posted.
Is there a trigger on the table? Does the trigger have a subquery in it?
I suspect you're got a badly written trigger on the table, which is throwing the error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2015 at 9:07 am
Unfortunately the table I'm attempting to insert into is part of the our companies ERP systems database and it does have the following trigger:
create trigger [dbo].[_ti_JobMtl] ON [dbo].[JobMtl] for insert as
begin
if ( select PROGRESS_RECID from inserted) is NULL
begin
update t set PROGRESS_RECID = i.IDENTITYCOL
from JobMtl t JOIN INSERTED i ON
t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
select convert (bigint, @@identity)
end
end
Is there any way around this?
August 27, 2015 at 9:21 am
You're going to have to fix that trigger, it can't handle multiple rows being inserted in a single operation. To do that, you need to find out (from the other devs probably) what the intention of that trigger is and what it's supposed to do when more than one row gets inserted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2015 at 9:26 am
out of curiosity...is your ERP "Progress/Openedge" by any chance?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 27, 2015 at 9:30 am
No sir, our ERP is Epicor. They originally used Progress but are phasing it out now.
August 27, 2015 at 9:32 am
Since I can't change the trigger on the ERP System database, would I be able to set the Stored Proc up to use a Cursor and input a single row at a time? Thanks again for you help.
August 27, 2015 at 9:36 am
tthoman77 (8/27/2015)
No sir, our ERP is Epicor. They originally used Progress but are phasing it out now.
what version of Epicor?
as a sidebar.....are you 100% sure that your ERP support licence allows you to write back to the base Epicor tables?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 27, 2015 at 10:09 am
tthoman77 (8/27/2015)
Since I can't change the trigger on the ERP System database, would I be able to set the Stored Proc up to use a Cursor and input a single row at a time? Thanks again for you help.
If you want inefficient, slow code yes. That trigger really needs to be fixed though, it's going to throw that error any time more than one row is inserted from anywhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2015 at 10:20 am
The basic trigger rewrite is easy enough, but trying to return the value is and will be a very severe issue. Returning data directly from a trigger should never be done, and won't be allowed in the future. You'll have to address that part of the trigger yourself, and return the data to the app another way. You could use CONTEXT_INFO as a quick-and-dirty work around.
Create Trigger [dbo].[_ti_JobMtl]
On [dbo].[JobMtl]
After Insert
As
Set Nocount On;
Update t
Set PROGRESS_RECID = i.IDENTITYCOL
From JobMtl t
Inner Join INSERTED i On
t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
Where
i.PROGRESS_RECID Is Null
Select convert (bigint, @@identity) --<<-- BAD, and deprecated, idea to return data from a trigger, MUST BE REWORKED
Go --end of trigger
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 27, 2015 at 11:12 am
Not sure what the current version of Epicor is. I was looking for away to speed things up from using the Business Objects and writing a single Material at a time in looping fashion. I have to write close to 200 lines per order and that presently takes 30 to 45 seconds.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply