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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mlcktmguyAuthor Commented:
On a whim I added an Identity column to the table and now the insert works.
0
Snarf0001Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Snarf0001Commented:
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 WillsTopic 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.