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
647 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 the fundamental information of how to create a table.

737 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