Solved

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.

Posted on 2007-03-30
4
625 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:DRSLT
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:DRSLT
Comment Utility
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
 
LVL 1

Author Comment

by:DRSLT
Comment Utility
rboyd56:

Thanks for the help...you posted before I posted that I already had it solved so the points are yours!
0
 
LVL 16

Expert Comment

by:rboyd56
Comment Utility
Yor're welcome.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 109
Restore Procedure question 4 28
Report Builder 9 25
SQL 2016 Setup - Connectivity Issues 4 12
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now