An explicit value for the identity column in table 'BNP.dbo.tblWorkingBOM' can only be specified when a column list is used and IDENTITY_INSERT is ON.

This only started to happen recently.  The Stored Procedure and Table have not been changed in any way.  The administrator says that no changes/updates have been made to the SQL Server.

Server: Msg 8101, Level 16, State 1, Procedure usp_GetIndentedBOM4BnP4WorkingFile, Line 87
An explicit value for the identity column in table 'BNP.dbo.tblWorkingBOM' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Stored Procedure: Datawarehouse.dbo.usp_GetIndentedBOM4BnP4WorkingFile
 
I have attempted to insert the following in the Stored Procedure immediately before it writes to the BNP.dbo.tblWorkingBOM table but the result is the same
-->  SET IDENTITY_INSERT BNP.dbo.tblWorkingBOM ON

Here is the Stored Procedure...

CREATE PROCEDURE usp_GetIndentedBOM4BnP4WorkingFile
      @AssyNo VARCHAR(20),
      @AssyRev VARCHAR(4) = NULL,
      @AssyQty DECIMAL(18, 3) = 1.000,
      @NumLevels INT = 1,
      @BidFile VARCHAR(15)
AS
DECLARE
      @errNO INT,
      @uspName VARCHAR(30),
      @Level INT

SET @uspName= 'usp_GetIndentedBOM4BnP4WorkingFile'
SET LOCK_TIMEOUT 5000
SET NOCOUNT ON

-- If no Revision code is passed in use the Revision on the PRODUCT table
IF @AssyRev IS NULL
      SET @AssyRev = (SELECT ISNULL(PARTREV, '') FROM PRODUCT WHERE PARTNO = @AssyNo)

-- If number of levels is zero return all levels
IF @NumLevels = 0
      SET @NumLevels = 99

SET @AssyNo = UPPER(@AssyNo)

SET @Level = 1

CREATE TABLE #stack (
      BMFID VARCHAR(50),
      Parent VARCHAR(20),
      Rev VARCHAR(4),
      Comp VARCHAR(20),
      CompRev VARCHAR(4),
      Qty DECIMAL(18, 3),
      Seq VARCHAR(3000),
      Lvl INT,
      CageNo VARCHAR(5),
      NumCageNo INT,
      BF VARCHAR(15),
      EndItem VARCHAR(20))

INSERT INTO #stack (BMFID, Parent, Rev, Comp, CompRev, Qty, Seq, Lvl, NumCageno,  BF, EndItem)
SELECT B.BMFID, B.PARENT, ISNULL(B.REV, ''), B.COMP, ISNULL(P.PARTREV, ''),
      @AssyQty * B.QTY, RIGHT('0000' + ISNULL(LEFT(B.SEQ, 4), '0000'), 4) + '|' + B.COMP, @Level, 0, @BidFile, @AssyNo
FROM BMF B INNER JOIN
      PRODUCT P ON B.COMP = P.PARTNO
WHERE B.PARENT = @AssyNo AND ISNULL(B.REV, '') = @AssyRev

WHILE @Level < @NumLevels BEGIN
      INSERT INTO #stack (BMFID, Parent, Rev, Comp, CompRev, Qty, Seq, Lvl, NumCageno, BF, EndItem)
      SELECT B.BMFID, B.PARENT, ISNULL(B.REV, ''), B.COMP, ISNULL(P1.PARTREV, ''),
            S.Qty * B.QTY, S.Seq + '!' + RIGHT('0000' + ISNULL(LEFT(B.SEQ, 4), '0000'), 4) + '|' + B.COMP,
            @Level + 1, 0, @BidFile, @AssyNo
      FROM #stack S INNER JOIN
            BMF B ON S.Comp = B.PARENT AND S.CompRev = ISNULL(B.REV, '') INNER JOIN
            PRODUCT P1 ON B.COMP = P1.PARTNO INNER JOIN
            PRODUCT P2 ON S.Comp = P2.PARTNO
      WHERE S.Lvl = @Level AND P2.MRPTYP = 'M'

      IF @@ROWCOUNT = 0
            SET @Level = 99
      ELSE
            SET @Level = @Level + 1
END

-- Cage number returned is 'first' cage number in CAGEMANF
UPDATE #stack
SET CageNo = C2.CAGENO
FROM #stack S INNER JOIN
      (SELECT DISTINCT C1.PARTNO, C1.CAGENO
      FROM CAGEMANF C1
      WHERE CAGENO = (SELECT TOP 1 CAGENO
                  FROM CAGEMANF
                  WHERE PARTNO = C1.PARTNO)) AS C2 ON C2.PARTNO = S.Comp

-- Set count of cage numbers that a component has
UPDATE #stack
SET NumCageNo = C.NumCageNo
FROM #stack S INNER JOIN
      (SELECT PARTNO, NumCageNo = COUNT(CAGENO)
      FROM CAGEMANF
      GROUP BY PARTNO) AS C ON S.Comp = C.PARTNO

INSERT INTO BNP.dbo.tblWorkingBOM
SELECT S.BMFID, S.Parent, ParentDesc = P1.[DESCRIPTION], ParentRev = S.Rev, ParentDrawNo = P1.DRAW,
      ParentLT = I1.MLT, ParentPT = I1.MPT, ParentRT = I1.MRT,
      S.Comp, CompDesc = P2.[DESCRIPTION], S.CompRev, CompDrawNo = P2.DRAW, CompMRPType = P2.MRPTYP,
      CompLT = CASE WHEN P2.MRPTYP = 'M' THEN I2.MLT ELSE I2.PLT END,
      CompPT = CASE WHEN P2.MRPTYP = 'M' THEN I2.MPT ELSE I2.PPT END,
      CompRT = CASE WHEN P2.MRPTYP = 'M' THEN I2.MRT ELSE I2.PRT END,
      CompABC = I2.ABC, CompManfClass = P2.MANFCLASS,
      S.Lvl, FindNo = B.FINDNO, Seqno = RIGHT('0000' + ISNULL(LEFT(B.SEQ, 4), '0000'), 4),
      CompUM = P2.UM, UnitQty = B.QTY, ExtQty = S.Qty,
      S.CageNo, S.NumCageNo,
      RefDesignators = B.REDDES,
      EndItem = S.EndItem,
      BidFile = S.BF
FROM #stack S INNER JOIN
      BMF B ON S.BMFID = B.BMFID INNER JOIN
      PRODUCT P1 ON S.Parent = P1.PARTNO INNER JOIN
      PRODUCT P2 ON S.Comp = P2.PARTNO LEFT OUTER JOIN
      ITEMORD I1 ON S.Parent = I1.PARTNO LEFT OUTER JOIN
      ITEMORD I2 ON S.Comp = I2.PARTNO
ORDER BY S.Seq

DROP TABLE #stack

RETURN 0

Error_Handler:
RAISERROR ('Unable to retrieve data at %s due to SQL error %d.', 19, 1, @uspName, @errNO) WITH LOG
RETURN 1
GO
LVL 1
DRSLTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rboyd56Commented:
Even with Identity_insert on you have to specify the column lst in the insert statement. This statement does not have the column list to insert the values in:

INSERT INTO BNP.dbo.tblWorkingBOM
iF you add the column list to insert the values into the insert should work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DRSLTAuthor Commented:
I figured it out...had to explicitely name each column in my INSERT statement as follows:

INSERT INTO       BNP.dbo.tblWorkingBOM
            (
                  BMFID,
                  Parent,
                  ParentDesc,
                  ParentRev,
                  ParentDrawNo,
                  ParentLT,
                  ParentPT,
                  ParentRT,
                  Comp,
                  CompDesc,
                  CompRev,
                  CompDrawNo,
                  CompMRPType,
                  CompLT,
                  CompPT,
                  CompRT,
                  CompABC,
                  CompManfClass,
                  Lvl,
                  FindNo,
                  Seqno,
                  CompUM,
                  UnitQty,
                  ExtQty,
                  CageNo,
                  NumCageNo,
                  RefDesignators,
                  EndItem,
                  BidFile
            )
SELECT            S.BMFID,
            S.Parent,
            P1.[DESCRIPTION],
            S.Rev,
            P1.DRAW,
            I1.MLT,
            I1.MPT,
            I1.MRT,
            S.Comp,
            P2.[DESCRIPTION],
            S.CompRev,
            P2.DRAW,
            P2.MRPTYP,
            CASE WHEN P2.MRPTYP = 'M' THEN I2.MLT ELSE I2.PLT END,
            CASE WHEN P2.MRPTYP = 'M' THEN I2.MPT ELSE I2.PPT END,
            CASE WHEN P2.MRPTYP = 'M' THEN I2.MRT ELSE I2.PRT END,
            I2.ABC,
            P2.MANFCLASS,
            S.Lvl,
            B.FINDNO,
            RIGHT('0000' + ISNULL(LEFT(B.SEQ, 4), '0000'), 4),
            P2.UM,
            B.QTY,
            S.Qty,
            S.CageNo,
            S.NumCageNo,
            B.REDDES,
            S.EndItem,
            S.BF
FROM #stack S INNER JOIN
      BMF B ON S.BMFID = B.BMFID INNER JOIN
      PRODUCT P1 ON S.Parent = P1.PARTNO INNER JOIN
      PRODUCT P2 ON S.Comp = P2.PARTNO LEFT OUTER JOIN
      ITEMORD I1 ON S.Parent = I1.PARTNO LEFT OUTER JOIN
      ITEMORD I2 ON S.Comp = I2.PARTNO
ORDER BY S.Seq
0
DRSLTAuthor Commented:
rboyd56:

Thanks for the help...you posted before I posted that I already had it solved so the points are yours!
0
rboyd56Commented:
Yor're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.