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
642 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
ID: 18823993
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
ID: 18824035
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
ID: 18824061
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
ID: 18826013
Yor're welcome.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

810 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