SQL Server Error, Cannot insert explicit value for identify column when Identity insert is turned off

The exact error I'm getting is:
Msg 544, Level 16, State 1, Procedure aConvertspProdPayments, Line 28
Cannot insert explicit value for identity column in table 'tblTmpBuildProd_Payments_wLB_01' when IDENTITY_INSERT is set to OFF.

Open in new window


Within an SP I am running an insert into a table in the SQL DB.  The table does not have an Identity column defined so I don't understand the error.

This insert was in my MS Access program and ran fine.  I only get the error when I move it to the SP

Any ideas?

Here's the SP:
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[aConvertspProdPayments]    Script Date: 1/26/2018 2:02:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 
-- =============================================
Alter PROCEDURE [dbo].[aConvertspProdPayments]
   
AS
BEGIN
    SET NOCOUNT ON;

	declare     @WkDate DATE
	declare     @wkUser  nvarChar(10)
	declare     @TrueVar    bit
	declare     @FalseVar   bit

	set @wkUser='Convert'
	set @wkdate = GETDATE()
	set @TrueVar = 1
	set @Falsevar  = 0
--
-- Clear the COmment Table
--
  Execute dbo.spDeleteAllRecsFromSQLTable 'tblTmpBuildProd_Payments_wLB_01'
--
--  Now load it
--
INSERT INTO tblTmpBuildProd_Payments_wLB_01 ( TaxYearRecID, TaxAuthorityRecID, PropertyID, LotBlock, TieBreaker, SQMTID, MuniCode, ControlNumber, SequenceNumber, TaxType, PaidFlag, FullOrPartPayFromTildeRec, Paid_To_County, Initials, VoucherNum, VoucherAlpha, SoldFlag, PayYearNum, PayDate, PayReceipt, PayFaceAmt, PayPenaltyAmt, PayInterestAmt, PayCostAmt, PayCommisionAmt, PayReceiptSequence )
SELECT tblTaxYears.TaxYearRecID, tblTaxYears.TaxAuthorityRecID, tblTaxYears.PropertyID, tblTaxYears.LotBlock, tblTaxYears.TieBreaker, SQMT_Comment_Payments_Work.ID AS SQMTID, SQMT_Comment_Payments_Work.MuniCode, SQMT_Comment_Payments_Work.ControlNumber, SQMT_Comment_Payments_Work.SequenceNumber, SQMT_Comment_Payments_Work.TaxType, SQMT_Comment_Payments_Work.PaidFlag, SQMT_Comment_Payments_Work.FullOrPartPayFromTildeRec, SQMT_Comment_Payments_Work.Paid_To_County, SQMT_Comment_Payments_Work.Initials, SQMT_Comment_Payments_Work.VoucherNum, SQMT_Comment_Payments_Work.VoucherAlpha, SQMT_Comment_Payments_Work.SoldFlag, SQMT_Comment_Payments_Work.PayYearNum, SQMT_Comment_Payments_Work.PayDate, SQMT_Comment_Payments_Work.PayReceipt, SQMT_Comment_Payments_Work.PayFaceAmt, SQMT_Comment_Payments_Work.PayPenaltyAmt, SQMT_Comment_Payments_Work.PayInterestAmt, SQMT_Comment_Payments_Work.PayCostAmt, SQMT_Comment_Payments_Work.PayCommisionAmt, SQMT_Comment_Payments_Work.PayReceiptSequence
FROM SQMT_Comment_Payments_Work INNER JOIN tblTaxYears ON (SQMT_Comment_Payments_Work.PayYearNum = tblTaxYears.TaxYear) AND (SQMT_Comment_Payments_Work.TaxType = tblTaxYears.TaxTypeID) AND (SQMT_Comment_Payments_Work.MuniCode = tblTaxYears.MuniCode) AND (SQMT_Comment_Payments_Work.ControlNumber = tblTaxYears.OldControlNumber)
WHERE (((SQMT_Comment_Payments_Work.PayYearNum) Is Not Null));


END

Open in new window




This is the table Def
CREATE TABLE [dbo].[tblTmpBuildProd_Payments_wLB_01](
	[TaxYearRecID] [int] NOT NULL,
	[PropertyID] [int] NOT NULL,
	[SQMTID] [int] IDENTITY(1,1) NOT NULL,
	[TaxAuthorityRecID] [int] NOT NULL,
	[LotBlock] [nvarchar](30) NULL,
	[TieBreaker] [nvarchar](2) NULL,
	[MuniCode] [smallint] NULL,
	[ControlNumber] [int] NULL,
	[SequenceNumber] [int] NULL,
	[TaxType] [int] NULL,
	[PaidFlag] [int] NULL,
	[FullOrPartPayFromTildeRec] [nchar](1) NULL,
	[Paid_To_County] [int] NULL,
	[Initials] [nvarchar](3) NULL,
	[VoucherNum] [nvarchar](6) NULL,
	[VoucherAlpha] [nvarchar](1) NULL,
	[SoldFlag] [nvarchar](8) NULL,
	[PayYearNum] [int] NULL,
	[PayDate] [date] NULL,
	[PayReceipt] [nvarchar](50) NULL,
	[PayFaceAmt] [float] NULL,
	[PayPenaltyAmt] [float] NULL,
	[PayInterestAmt] [float] NULL,
	[PayCostAmt] [float] NULL,
	[PayCommisionAmt] [float] NULL,
	[PayReceiptSequence] [int] NULL,
	[SSMA_TimeStamp] [timestamp] NOT NULL,
 CONSTRAINT [tblTmpBuildProd_Payments_wLB_01$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[TaxYearRecID] ASC,
	[PropertyID] ASC,
	[SQMTID] ASC,
	[TaxAuthorityRecID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?
 
Snarf0001Connect With a Mentor Commented:
The table does not have an Identity column defined

According to your table script it does, 3rd line:

[SQMTID] [int] IDENTITY(1,1) NOT NULL,

Open in new window


Which is included in your insert statement, and is throwing the error.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check if the table doesn't have a trigger that inserts data in another table.
Or if this table is part of a transactional replication.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Btw, the main SP is calling another SP:
Execute dbo.spDeleteAllRecsFromSQLTable 'tblTmpBuildProd_Payments_wLB_01'

Open in new window

It will be good to see what this SP is doing also.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
mlcktmguyAuthor Commented:
There is no trigger on the table.  The executed SP is removing all of the records from the table that I want to insert records into.  I am executing that SP for the same purpose in dozens of other places without issue.
Here it is:

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spDeleteAllRecsFromSQLTable]    Script Date: 1/26/2018 3:42:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		MJO
-- Create date: 
-- Description:	Clear Passed SQL Table
-- =============================================
ALTER PROCEDURE [dbo].[spDeleteAllRecsFromSQLTable] 
	-- Add the parameters for the stored procedure here
	@tablename nvarchar(100) 
	
AS

 SET NOCOUNT ON;

 DECLARE @sql varchar(8000);

 --Trying TRUNCATE first, since it's more efficient when it works.
 SET @sql = 'TRUNCATE TABLE [' + PARSENAME(@tablename, 1) + ']';
 BEGIN TRY
 EXEC(@sql)
 END TRY
 --If TRUNCATE fails, fall back on normal DELETE.
 BEGIN CATCH
 SET @sql = 'DELETE FROM [' + PARSENAME(@tablename, 1) + ']';
 EXEC(@sql)
 END CATCH

Open in new window

0
 
mlcktmguyAuthor Commented:
On a whim I added an Identity column to the table and now the insert works.
0
 
Snarf0001Connect With a Mentor Commented:
Incidentally... when you added the new identity column, if you did that through the table designer and not script, it would have probably "automatically" disabled identity property on the existing one.
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
In your stored procedure you can do
SET IDENTITY_INSERT  tblTmpBuildProd_Payments_wLB_01 ON;

Open in new window

And reset after the load...
Or given the Tmp prefix maybe leave it / disable it / redefine your table.

@snarf, *laughing*, was going to post the same having read the question and poured over table spec - but thought better of it :)
0
 
Snarf0001Commented:
Lol, you know what they say about great minds ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.