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
651 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

630 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